One-To-Many Relationship with a Twist

Databases
1

I have a basic 1:Many table relationship for products and the categories they are in. The tables are set up as

Products
id
name

Categories
id name

CategoriesXProducts
cat_id
prod_id

This has been in place for years, works great, yadda yadda. Now we have a new requirement where we need to specify a single relationship (inside of CxP) as the “master category”. Currently when we want to know about the product, we select the categories and only use the first returned category. This obviously doesn’t always work well or even consistently since there is nothing to sort CxP on.

Without a complete overhaul of the current structure, what is the best way to accomplish this? I would like to be able to enforce the 1 master per product at the database level, but the only structure I can think of that allows that is a column that is “is_master” where no is NULL and yes is 1… but that’s a nasty hack

2

Rudy, does that smiley mean my schema needs an overhaul or was there something else I am missing?

Guido, yeah, I can handle it in the app logic, I just like to have the database enforce relationship restraints when possible (it gives me more peace of mind). So far Rudy’s recommendation is winning (I like the idea of flexibility for other category markers in the future).

3

You can also handle the master category assignment in your application code: when someone assigns a master category, check if there already is one, and react any way you want: give a message, change the master category, whatever.

4

it means that i see that you are on the right track and i am happy about it

the many-to-many table is a necessity

choosing one of the many to be a “first among equals” requires an additional column in the many-to-many table, and you ~could~ make it a boolean, but you can also get other advantages by making it an integer

i’m not sure guido was actually suggesting this, but at no time should you consider storing the “first among equals” attribute outside the database (for one thing, it would mean at least one row for every product)

5

Can’t you avoid duplicate prod_id-status couples by putting another unique key on that pair of columns?

6

CREATE TABLE CategoriesXProducts
( cat_id INTEGER NOT NULL
, prod_id INTEGER NOT NULL
, PRIMARY KEY ( cat_id , prod_id )
, status INTEGER NOT NULL DEFAULT 100
);

you can link each product to the same category only once

7

use an integer instead of a boolean (especially a boolean that allows NULL!)

that way you can have “master status” of 100, 200, and so on

this would be a lot easier to hack for future requirements, e.g. major category plus minor category

8

I think you mean a many:many relationship?

the only structure I can think of that allows that is a column that is “is_master” where no is NULL and yes is 1… but that’s a nasty hack

A hack? No, it’s a good solution. And nasty? Why?

9

If I put an additional unique index on prod_id, status, then products would be limited to having two categories (status=200 for primary and status=100 for non-primary/whatever). The only way I know of to get around that is default status = null, but then I’m right back to my first post trying to avoid that. :frowning:

10

I was saying that if the OP wants to have only 1 category (at the most) per product that has the value 1 (or whatever value he wants to use for his ‘master category’), and he doesn’t want to put a unique key on the product/status pair (because he doesn’t want to use NULL as a value for all categories that don’t have a status), then he’ll have to enforce that restriction in his application code.

11

Okay, I guess status_code it is. I’ll just have to enforce the restriction of the “master status” in the application. Thanks for the tip about the status_code – should be more useful than a simple boolean field.

12

:slight_smile:

13

Yes, there is currently no limit to the number of categories a product can be in. Currently, we have products that are in more than 10 categories. This is why it is becoming harder for me to pick the master category given our current schema

14

can a product belong to more than one category? it looks like yes :slight_smile:

can a product belong to more than two categories?

15

in my scheme, the master category would be the one with the lowest status number (actually, “priority” might be a better name than “status”)

but there are many ways you can assign primacy (“first among equals”), just choose a design that gives you some flexibility (which a boolean does not)

16

Right, that’s how my current indexing works, but I’m curious if there was any way to enforce there only being 1 “master category” per product as well. Basically if I do a join to show a product’s master category, I want to be able to know it will only return one row per product

17

Sorry, yeah, it’s a many to many. I always forget the first table has a bunch of records :duh:

It’s a hack because it’s an odd handling of NULL. NULL should not mean false – the only reason I’d use it is because NULL values don’t affect unique indices.

Rudy, using that method (a status_code), there’s no way to enforce singularity/uniqueness/whatever the proper term is (outside of additional application logic) for each product. Any thoughts on that?