refactoring sql schema

Question

Guys i currently have 2 tables that reference a lookup table. Ive been tasked with modifiying the database to allow the tables to reference more than one value in the lookup table.

My idea is to create a new table that has a new PK and a column that references the lookup table. The PK of the new table is then dumped into the parent tables thus maintaining referential integrity E.G

Current

 Contracts
 =========
 PK Id
 FK lookupId

 Warranties
 ==========
 PK Id
 FK lookupId

New

 Contracts
 =========
 PK Id
 FK LinkingTableId

 Warranties
 ==========
 PK Id
 FK LinkingTableId

 LinkingTable
 ============
 PK Id
 FK LookupId

Is this a good way of handling the multiple references?

Answer 1

The approach you suggest is the standard one. The linking table is known as a "Junction" table. http://en.wikipedia.org/wiki/Junction_table

I would suggest a change to your naming conventions. Also your main data tables don't need the FK any more and the junction table doesn't need its own primary key but rather a compound key on the two foreign keys.

 Contracts
 =========
 PK Id
 Other_Stuff

 Warranties
 ==========
 PK Id
 Other_Stuff

 Contract_Warranties
 ============
 FK ContactId
 FK WarrantyId

Answer 2

Your NEW proposed table structure cannot handle multiple references, because each Contract and Warranty row is linked to just one single LinkingTable row.

I see two ways to do this:

1. Add two distinct linking tables, one for each many-to-many relationship:

   Contracts
   =========
   PK Id
   Other_Contract_Stuff
   
   ContractLinkingTable
   ====================
   PK ContractId, LookupId
   FK ContractId
   FK LookupId
   
   Warranties
   ==========
   PK Id
   Other_Warranty_Stuff
   
   WarrantyLinkingTable
   ====================
   PK WarrantyId, LookupId
   FK WarrantyId
   FK LookupId
   

2. Add a single new linking table, to handle both many-to-many relationships:

   Contracts
   =========
   PK Id
   Other_Contract_Stuff
   
   Warranties
   ==========
   PK Id
   Other_Warranty_Stuff
   
   LinkingTable
   ====================
   PK LinkedType, LinkedId, LookupId
      LinkedType ("C" for Contract, "W" for Warranty)
      LinkedId (either a ContractId or a WarrantyId)
   FK LookupId
   

The first is the correct and preferrable solution, as the engine can enforce the table foreign keys and data integrity is less at risk. I have seen the latter used in some real world projects. It works, but it is not a good solution... and sooner or later you will have problems with it.