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?

So you have a lookup table to the lookup table now? It looks ok but it can't be fully interpreted without knowing wht the lookup table does. — Jacob
– Jacob, Commented Aug 3, 2011 at 13:59
@cularis the lookup table is a list of disciplines. nothing more. My company now want the user to be able to select a number of disciplines rather than just one and yes i guess it would be a lookup table to another lookup table. Any thoughts? — Richard Banks
– Richard Banks, Commented Aug 3, 2011 at 14:05
Ok then it's standard. To change a one-to-one relation to a one-to-many you need something like your linking table so your solution is ok. — Jacob
– Jacob, Commented Aug 3, 2011 at 14:07

Frazz · Accepted Answer · 2011-08-03 14:56:26Z

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:

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

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.

answered Aug 3, 2011 at 14:56

Frazz

3,0432 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Banks Over a year ago

yes your right. Looking at back at my solution it is obvious now. Thanks for pointing that out. In your first solution are you saying that i need four columns in the linking table? How do i set the lookupid and the warrantyId to both be primary keys

Frazz Over a year ago

You only need 2 columns, but you can and should define 3 constraints: 1 PK and 2 FK. 'ALTER TABLE ContractLinkingTable ADD CONSTRAINT pk_ContractLinkingTable PRIMARY (ContractId,LookupId)'

Ash Eldritch · Accepted Answer · 2011-08-03 14:56:34Z

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

edited Aug 3, 2011 at 14:56

answered Aug 3, 2011 at 14:27

Ash Eldritch

1,51410 silver badges14 bronze badges

2 Comments

Tom H Over a year ago

The linking or junction table should still have a primary key. Perhaps you meant that it doesn't need a surrogate single-column key?

Ash Eldritch Over a year ago

@Tom H. Thanks. Answer modified accordingly.

Collectives™ on Stack Overflow

refactoring sql schema

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related