Multiply tables need to save to many record in another table

45 views Asked by At

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

0

There are 0 answers