I came across pretty simple and probably very basic problem, and I can't decide what solution is the best.

After simplifiyng my scheme to the bone, I have this:

  • table of Suppliers (id, name)
  • table of Parts (id, name)
  • m to n table PartSuppliers(supplier_id, part_id, price)

I need to mark one of the suppliers as primary supplier for given part. I see two options:

1) (currently used) Add primary_supplier_id column to Parts table

2) Add column is_primary to PartSuppliers table

Both solutions have some pros and cons. Solution 1) there are problems with validation and CRUD - info about part suppliers is on "two places". Solution 2 would be easier to use in app, but isn't it breaking normalization?

  • Similar (if not identical) question: How to have a one-to-many relationship with a privileged child? – ypercubeᵀᴹ Aug 2 '16 at 11:36
  • How does it break normalization? – Walter Mitty Aug 2 '16 at 11:56
  • @ypercubeᵀᴹ Yes! Thank your for linking that question. I was really trying to find it but was unable to find right words (my english is not good enough). – Vladiks Aug 2 '16 at 12:22
  • @WalterMitty Maybe it does not exactly violate 3NF? But anyway, it does not seem very nice to add column with sparse values to same table... – Vladiks Aug 2 '16 at 13:06
  • I don't particularly like boolean columns but I don't think it breaks normalization. This is a physical implementation detail, so it depends on the DBMS you plan to use and doesn't really affect the logical model and normalization. – ypercubeᵀᴹ Aug 2 '16 at 13:22

Which solution you chose is largely a matter of personal preference. I have a strong preference for the foreign key approach, and think I can provide good reasons why.

First of all, check that your requirement is strictly to capture a single "primary supplier", and not (potentially multiple) "preferred suppliers". If you can't guarantee that you'll never have more than 1, the foreign key approach will break and you're better off going with the flag approach from day 1.

If, however, a part always has a single primary supplier I would use the foreign key approach. If a part must have a primary supplier, then this is the only approach where the database constraints guarantee that the data is always correct -- make the foreign key column mandatory (not null) and you will have to provide the primary supplier when adding the part. There is simply no way that the data cannot be correct.

With the flag approach, the primary supplier information can be missing from parts -- unless you want to rely on your application's logic. If that's potentially a problem, the mandatory foreign key approach is the easiest way to ensure this can never happen.

Think also about updates. With the flag approach, you need to clear the flag from other "part supplier" records when you change preferred suppliers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.