Use flag on each row to mark record as primary or use foreign key on parent table 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?
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.