I have 7 existing tables that are tech related hardware. i.e. PC, server, ip phones, and so forth.
Now they want to store the MAC Address for each device in a MAC address Table. Normally this would not be a problem for me but here's were I get lost.
Out of the 7 different hardware tables some have multiply MAC Address while the rest have only one.
The only way I can think to make this work is to Create the MAC address table like below.
--List of the 7 tables
Create Table TableName (
TableNameID Bigint Primary Key Identity Not Null,
TableName Varchar(30) Not Null,
)
--The table that hold the MAC address
Create Table MacAddress (
MacAddressID Int Primary Key Identity Not Null,
MacAddress Char(25) Unique Not Null,
TableNameID Int Not Null,
HardwareTableID Int Not Null,
CONSTRAINT fk_Telephone_MacAddress FOREIGN KEY (HarewareTableID) REFERENCES Telephone(TelephoneID),
CONSTRAINT fk_Printer_MacAddress FOREIGN KEY (HarewareTableID) REFERENCES Printer(PrinterID),
)
I was thinking of creating the above and making the HardwareTableID a foreign key to all the other tables, and then using the TableName as a way to identify the Table for which it is linked to. I also thought about using a third table that would normal be used for a many to many relationship but I'd still need some way to know which table the record is linked to.
If anyone has a better way doing this i'd love to hear it.
We are using SQL Server 2008 R2 for the database.
Hopefully I explained this well enough. Please let me know if you need clarification as I'm having a hard time wrapping my head around this.
Thanks