Dennis Dennis - 1 month ago 4x
MySQL Question

Should "Product Category" have an identifying relationship with "Product"?

I have a table

product(id, name)
, which contains several groups of products that differ by a model number. i.e. {motor10, motor20, motor30, pipe10, pipe20, pipe30, wrench12, wrench20, etc}.

I chose to create a new table called
product category
that contains just the categories, like {motor, pipe, wrench, uncategorized}

enter image description here


For practical purposes in an application (and not for theoretical purposes such as modeling an ER Diagram), should I use identifying or non-identifying relationship?

My Use Case
In my case, I can define it so that a product cannot exist without a category. However, if a product is not yet categorized, it carries
value in category. Category can have entries that do not yet have any products assigned to it.

Category is a made-up concept that I do not really have to use, but it will help me with categorizing the sets of products that I currently do have. I think because it is a made up concept, and I am not sure how I want to use it, is why I am struggling with this issue. aka.. I do not have to have table
at all, but it will certainly help me with various product grouping.

Making this an identifying relationship will require me to revamp and rewrite some code, and I wanted to make sure I do want to make this an identifying relationship before I go off and write lots of code.

... that said, would there be a case where I would NOT make this an identifying relationship?


You can declare product_category as mandatory—that is, NOT NULL—without making it part of an identifying relationship.

An identifying relationship means that the attribute is part of the table's primary key. I don't think that applies in your case. A table can have NOT NULL attributes without making them part of a table's primary key.

Re your comment:

Another way of defining an identifying relationship is that rows in the child table cannot be uniquely identified without referencing the parent.

But in your case, you could easily have a product that exists and can be identified even though it does not belong to a category yet, or if it belongs to more than one category (if you had a many-to-many relationship).

Maybe existence isn't really the right criteria. But can you identify a product using other attributes (i.e. the primary key) without mentioning its category? Or can the category of a product change, without changing the unique identity of the product?

Take for example a US citizen who lives in a state. The state might be mandatory (for the sake of argument, not taking into account citizens living abroad or in US territories). Is there an identifying relationship on that citizen's current state? No; they can move from state to state and they're still the same person. The state is merely an attribute, even if it's mandatory.

Whereas a different example, like line items in a shopping cart, do have an identifying relationship with their parent shopping cart. The line item doesn't exist without that specific shopping cart.

An example that is more gray is a telephone number. I "own" my telephone number currently, and it makes no sense to have a telephone number that has no owner. But if I give up my number, the phone company can reallocate it to another owner. I'm not sure if that constitutes an identifying relationship or not.