2

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?

Vladiks
  • 23
  • 2
  • Similar (if not identical) question: How to have a one-to-many relationship with a privileged child? – ypercubeᵀᴹ Aug 02 '16 at 11:36
  • How does it break normalization? – Walter Mitty Aug 02 '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 02 '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 02 '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 02 '16 at 13:22
  • The easiest is the is_primary boolean column and a partial unique, assuming you are on a DBMS that has such indexes (eg. PostgreSQL, SQL Server). – ypercubeᵀᴹ Aug 02 '16 at 13:24
  • If later you find out that need some other FK to reference the "primary suppliers", then the extra table is probably the only option. If you are on DBMS that doesn't have partial unique indexes (like MySQL), again the extra table is probably the best option (otherwise, consistency is based on the applications, not the database.) – ypercubeᵀᴹ Aug 02 '16 at 13:26
  • If Solution 1 has the same info in two places, that must be "harmful redundancy", and therefore probably breaks 2NF, 3NF, BCNF, 4NF or 5NF. So maybe both solutions break normalization. – Walter Mitty Aug 03 '16 at 12:15

1 Answers1

1

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.

Colin 't Hart
  • 9,323
  • 15
  • 35
  • 43