Help with database design.

I am working on a server/client application where the client scans a
computer for all available mac addresses and stores the information
in
a database, associating mac addresses to assets. I see problems
occuring in terms of NICs being removed, changes to different
machines
on the network, new NICs being added, etc, etc. I am trying to
figure
out what tables, methods, etc I could use to avoid any possible
situations that could arise as I'm trying to store this information.
Any suggestions would be wonderful.

Re: Help with database design.

For example.
I have an asset table that has an AssetID column and MAC address. The
MAC address stored in this table is the one used to send the
information (via remoting).
I have a MAC table that has a unique MAC for each record, and an asset
ID associated to each MAC.
Any asset ID could have any number of MAC addresses.
I have an information table that holds other information sent via the
remoting.
This table has an ID column and an AssetID column.

I want to find out what issues will arrise when a particular asset
changes, adds, or removes a NIC, therefor altering the MAC information.

Re: Help with database design.

rhaazy (rhaazy@gmail.com) writes:
> For example.
> I have an asset table that has an AssetID column and MAC address. The
> MAC address stored in this table is the one used to send the
> information (via remoting).
> I have a MAC table that has a unique MAC for each record, and an asset
> ID associated to each MAC.
> Any asset ID could have any number of MAC addresses.
> I have an information table that holds other information sent via the
> remoting.
> This table has an ID column and an AssetID column.
>
> I want to find out what issues will arrise when a particular asset
> changes, adds, or removes a NIC, therefor altering the MAC information.

I don't really grasp what you are up to, but it seems to me that you
need an Assets table which describes the assets (which I assume are
computers). And then you need a Networkcards table keyed by the MAC.
This table would have AssetID as a foreign key. Theoretically, this
column should be nullable, since a network card maybe lying on the desk,
not being connected to anything. I suspect, though, that in this case,
it is not of interest to enter the card into the database.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Help with database design.

There are plenty of off-the-shelf applications that will handle this.

One I've used in the past is Centennial Discovery.

Cheers,
Jason Lepack

On Jun 18, 4:46 pm, rhaazy wrote:
> For example.
> I have an asset table that has an AssetID column and MAC address. The
> MAC address stored in this table is the one used to send the
> information (via remoting).
> I have a MAC table that has a unique MAC for each record, and an asset
> ID associated to each MAC.
> Any asset ID could have any number of MAC addresses.
> I have an information table that holds other information sent via the
> remoting.
> This table has an ID column and an AssetID column.
>
> I want to find out what issues will arrise when a particular asset
> changes, adds, or removes a NIC, therefor altering the MAC information.