CODEWITHSUNDEEP
databasedatabase-designkey
Paul Carter
Paul Carter

Reputation: 127

Database table design; link tables or rarely used foreign key?

My question relates to the best practices design of tables in a database for a specific scenario.

Say we have a Company that sells office equipment, Say Printers. This company also offers Service Contracts to Customers that have bought 1 or more of its Printers.

Given the above information, we can deduce three tables for our database:

So for a given service contract, we specify which Customer the contract is created for and we assign 1 or more printers that comprise the contract agreement.

With regards to the Printers that are part of the service contract agreement, there are 2 ways we could approach the database design.

  1. The first is to create a ServiceContractID column in the Printers table and create a basic Primary/Foreign key relationship between it and the ServiceContracts table. The only problem I see with with approach is that Printers don't have to be a part of a Service Contract and therefore you could have hundreds or even thousands of Printer records in the database, many of which are not part of a contract, so having this Foreign key column not being used for many of the records.

  2. The second option is to create a link table which would contain 2 foreign keys referencing both The ServiceContracts table (It's primary key) and the Printers table (It's Primary Key). Combining both columns in this new table to make a unique composite primary key.

OK, here's my quandary. I don't see either option as being typically a Really bad idea, but I am stuck on knowing which of these 2 design decisions would be the best practise.

All comments welcome.

Upvotes: 3

Views: 1796

Answers (2)

XIVSolutions
XIVSolutions

Reputation: 4502

If I understand your problem domain correctly, the proper way to do this would be option 2. It sounds like a customer can have 0-many service contract, a service contract can have 0-many printers associated with it, and a printer can be associate with 0-1 service contracts (unless contracts expire and renew with a NEW contract, in which case the printer can have many-many. 

Customers:
    PK
    CustomerInfo

Printers:
    PK
    PrinterInfo
    FK on Customer PK

ServiceContracts:
    PK
    FK on Customer

// This creates a composite PK for the Contract_Printers Table:
Contract_Printers:
    FK on Contract PK
    FK on Printer PK

Hope that helps. I will be interested in hearing what others think . . .

Upvotes: 0

Randy
Randy

Reputation: 16673

I think, without knowing your entire issue, that the second option is preferable. (i.e more properly normalized -in general)

The second option would allow some business rule flexibility which you may not have come up with yet (or your business model may change).

for example: dates may become important. The same ServiceContract may for instance be used even if the business decides on some rule, like all printers are covered for one year after purchase by this customer. where the same agreement just covers many purchases.

so option 2 gives you flexibility for adding other attributes on the relationship...

Upvotes: 3

Related Questions