Setting up a database, need some help

[rhett7660]

Setting up a database, need some input

Hello all..

I am putting together a database that will log the items that are at a location. I am looking to log the following:

Location
Phase

Now for the rest of these items I can have more then one at each location.

IE

Location: X
Phase: II

ComputerID
Computer Serial Number
Computer IP Address
Computer MAC Address

RouterID
Router Serial Number
Router IP Address
Router MAC Address

And so on. I will log about 4-5 different things at one location and each of those things I can have 2 or more.

I was thinking of setting it up the following way:

MainDataID (pk)
LocationID (pk)
Phase
TotalComputers
TotalRouters

ComputerID
LocationID(fk)
Etc

RouterID
LocationID(fk)
Etc

Would this be a good way of setting it up? Seems like I am missing something.

Thanks

 

[rhett7660]

Instead of repeating the serial numbers, ipaddress and mac address could I setup a table for each one of those?

SerialNumbersID
SerialNumbers

IpAddressID
IpAddress

MACAddressID
MacAddress

Or would you put those in one table

ComputerID (Type of computer)
LocationID (Location of the Computer)
IpAddress
MACAddress
SerialNumber

Or should I stick to doing it the other way?

 

[Banana]

Are all items going to have MAC ID, IP address, Model #, and so forth?

If so, why not:

tblItem
ItemID
ItemTypeID   'FK to a table of ItemType, enumerating Computer, Router, etc
MAC
IP
...

 

[rhett7660]

Not all items will have a Mac Address or IP Address,

They all will have a serial number thou.

Hence the reason i was thinking about having this table:

ComputerID (Type of computer)
LocationID (Location of the Computer)
IpAddress
MACAddress
SerialNumber

 

[rhett7660]

This is what I ended up doing. Let me know what you think. See attached.

 

[Swillsy]

This is what I ended up doing. Let me know what you think. See attached.

Hi rhett7660 This looks what I was about to suggest, although why is there a separate table for a site - would there be many sites to one location?

 

[rhett7660]

I actually have two types, I have site and location. Location being the physical placement, IE basement room 1a, where site is Norwalk Building. So I know I will have a minium of two sites per location. So yes, I will have many sites to one location. IE

Location: Norwalk
Site: Basement
Componet: LCD
Serial # 1234567

Site: Room 1a
Componet: Mac Mini Computer
Serial #.....
etc

Does this make sense? Is there anything I should be looking at?

 

[georgedwilkinson]

You should separate NetworkAddress into it's own table. Many pieces of equipment have more than one MAC address and thus more than one IP addresss.

Additionally, some equipment shares a MAC address and thus IP address with other equipment (I'm thinking blade servers).

That's a standard M:M relationship.

tblMain's name has no meaning. Why not name it something that people understand, descriptive of the data it contains?

 

[rhett7660]

George...

You are correct, how would I go about setting up a many to many relationship in this example?

 

[rhett7660]

George..

Would you put the IP Address and MAC address in the same table or would you split those out into their own table? IE

tblIPAddress
IpAddressID
IpAddress

tblMACAddress
MACAddressID
MACAddress

Or

tblAddress
ComponentID
MACAddress
IPAddress

Thanks again for your help.

 

[Banana]

I'd use an Address table because splitting IP/MAC into their own table isn't really right way for reasons that escapes me now; my gut says it's not normalized this way.

I'm assuming that there is also a one-one relationship between IP and MAC address. If so, then yes, the tblAddress with Component as a foreign key will make sense.

But if you can have stuff that may have unused MACs and do not want to use 0.0.0.0, "", or Null and need more information (e.g. you want to know why this isn't being used) then you will have to have another table for that.

 

[rhett7660]

Banana..

For this project, everything will have a Serial Number, but not everything will have a MAC or IP. IE

ComponentType
LCD Monitor
Serial Number

ComponentType
Mac Mini
IP Address
MAC Address
Serial Number

 

[rhett7660]

I don't mind having Null values, but if this is a no no then what would I need to do for that?

 

[rhett7660]

Something like this:

tblComponent
MainID
ComponentTypeID
SerialNumber

tblJunction
ComponentTypeID
AddressID
AddressComponentID

tblAddress
AddressID
IpAddress
MACAddress

Something like this?

 

[Banana]

I don't mind having Null values, but if this is a no no then what would I need to do for that?

The most important thing is whether you need to know why it's Null. If you want to know stuff like, say, "Broken", "Inactive", "Unused", you definitely should not assign that meaning to Null value. But if you don't particularly care about that, then Null is going to be fine. If you want to get brainfucked, here's a thread with The_Doc_Man, debating the finer points.

I hope that makes sense...

 

[georgedwilkinson]

I don't think I'd call it tblAddress, seems too much like a physical postal address, which your design may need one day. "NetworkAddresses" is potentially more appropriate.

In my mind, MAC and IP Addresses are 1:1 and can thus be in the same table. If they are not, the "main" MAC and "main" associated IP Address can be in the same table with an "aliases" table, if needed. For instance, I have a router at home that can be assigned additional MAC addresses for tricking your ISP into letting you run a network. The same device has an "internal" (192.168.X.X) IP address and an "external" IP address. In your table, one or the other (MAC or IP) can be null but not both.

My genius friend, Banana, has already helped on your other questions.

Are you set?

 

[rhett7660]

Thank you both for the input. After talking with my other co-workers on this project, each item, if called for will have an IPAddress and a MAC Address. If you have an IPAddress you will have a MAC Address, not one or the other.

On the flip side, all items will also have a serial number, but not all items will have an IPAddress or MAC Address. IE an LCD monitor that is setup at the site location will not have a IPAddress or a MAC Address but will have a serial number.

With that in mind, would I need to use the junction table? If yes, aside from the naming, which I will change, will what I laid out work?

Banana..

Thanks for the article/thread. Good read. But man o man... I could see one going crazy with this.

 

[Banana]

Seems easier to me to have IP/MAC as optional (e.g. allow ZLS or Null) fields in same table. You can always query for only components with IP/MAC or without.

 

[rhett7660]

Banana....

Ok that is what I was thinking. Do you still think I would need a junction table to tie them together? Or do you think the orginal way I had it would work out?

 

[Banana]

I'm not sure what you are referring to.

If you meant this:

tblComponent
tblJunction
tblAddress

Then this looks fine to me. Or did you mean "tie them together" as IP to Mac?

This really can only be answered by yourself- do you need to know that information or not? If you don't care or won't need it right now, will you need it tomorrow?