1

Let's say I have a table of products (product) and a table of categories (category) and between them there is a many-to-many relationship via a supplementary table (product_category_xref).

Is there a way to make one, and only one category "primary"?

So far I have two ideas:

  1. Create a separate column in the product table, primary_category, and have it contain a key for the primary category. What I don't like about this is that either there will be redundancy in data (same category defined in both primary_category and product_category_xref) or category matching (verify vs. primary_category and vs. product_category_xref.category).

  2. Create is_primary, a boolean column in product_category_xref. What I don't like about this, is that potentially there's a way to make several categories primary.

Also, integrity of a many-to-many relationship is enforced with foreign key, but I have no idea how to enforce only one primary category (or, perhaps, when a category is made primary, make all other categories of the same product non-primary).

What's the best way to do this?

UPDATE: The DBMS is MySQL interfaced via mysqli in PHP.

UPDATE2: Here's what I ended up doing. I added a non-nullable integer column in the product_category_xref table, priority. The primary category is then where primary is maximum. As a useful side effect, I can also sort categories. I also added the same thing the other way around to allow sorting the products within a category. Sure, this makes it harder to update the data, but that doesn't happen so often.

Beowulfenator
  • 2,262
  • 16
  • 26

3 Answers3

3

I don't disagree with ypercube, who is suggesting this design will be effective (regardless of DBMS)

ERD

Note in this design there is an extra table that refers to your many-to-many table, so it has both product and category in it, but it's primary key is just product. This design enforces these two requirements:

  • The primary product category is a valid product category combination.
  • There is at most one primary product category for each product.

This design doesn't help you with the constraint that every product must have a primary product category, which can really only be achieved by adding a mandatory foreign key column on the Product table.

However, I'm not sure that I would go so far as to recommend this solution to your problem. While this design successfully imposes the constraints you're looking for, it won't actually be easy to work with in your code. You're going to have to write code that deals with the fact that changes to product categories could impact your primary product category. The design works well on the persisted state of the data but it does you no favours for data changes.

Since you're going to need some program logic to make changes to the data work, you might also consider a simpler database design (e.g. add the FK column Product.primary_category_id) and write program logic to impose the additional constraints. It's not a fool-proof method, just a different, possibly easier to understand (therefore maintain) compromise.

Which way to go depends on how strongly you feel about pushing as many constraints as possible into your schema.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • I agree that the more the table one has, the more complicated the code to access them will be. And the answer in my link also suggests possible different approaches (like a flag column and a partial index) that work in SQL-Server and Postgres, without the need of the extra table. – ypercubeᵀᴹ Nov 23 '13 at 13:32
  • And of course neither of those enforces that "every product must have a primary product category". This can be done in code (as your suggestion) or with deferable FKs which are an option in Postgres and Oracle. – ypercubeᵀᴹ Nov 23 '13 at 13:33
  • @ypercube - Agreed, depending on the DBMS there may be a more elegant solution. – Joel Brown Nov 23 '13 at 13:39
1

There is a solution to use second approach with some consideration:

Its based on null-able unique constraints.

  • Having an extra null able foreign key from product table inside product_category_xref table, called primary_product_id.
  • is_primary will be null-able numeric column in product_category_xref table with length of 1, and CHECK constraint of possible values of {1}, that 1 value will show the is_primary is true and a null value shows none-primary.
  • A CHECK constraint with value of:
    (primary_product_id is null or ((primary_product_id = product_id) and (is_Primary is not null)))
  • A UNIQUE constraint on (primary_product_id + is_primary).

SQLFIDLE sample (tested on oracle).

Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46
0

An extra table of the form product_primary_cat_xref with a 1->1 relationship with products and m->1 relationship with categories will save you the duplicates and with referential integrity won't be unnecessarily redundant.

Pjottur
  • 632
  • 9
  • 22
  • Extra table, yes. The 2 FKs you suggest, no. All is needed in this table is one FK, referencing `product_category_xref (product_id, category_id)` and a unique constraint on `(product_id)` – ypercubeᵀᴹ Nov 23 '13 at 12:43