1

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

Question

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 uncategorized 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 product_category 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?

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Dennis
  • 7,907
  • 11
  • 65
  • 115
  • If it helps you, add it. It's that simple. Decide early so you don't create a lot of extra work for yourself. If it were me, I would add it. – Missy Sep 06 '16 at 23:27
  • If you make `product_category_id` part of the PK, you will increase the size of other idexes beacause they will also contain it without any reason. – Paul Spiegel Sep 06 '16 at 23:43

2 Answers2

1

I would

  1. Leave the product_category table as a non-identifying relationship.
  2. Have a default product_category row with category_name = Uncategorized and id = 1.
  3. Have a default value of 1 for the column product_category_id in product table, so that all new product rows that do not have a product_category assigned automatically default to Uncategorized.
Shiva
  • 20,575
  • 14
  • 82
  • 112
1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • thank you, Bill. I am curious - how do you discern that it does not apply? i.e. would you ever argue for the case where it would apply (still considering the same scenario)? – Dennis Sep 07 '16 at 02:17
  • Bill, thanks, so rationale seems to be that a `product` can stand on its own, even if it is not explicitly categorized. I was trying to see if I can make an artificial business rule like "a product cannot *conceptually* exist without a category" and enforce that, which would then (I presume) make it an identifying relationship. That is, I wonder if decision about identifying vs non-identifying relationship is a product of semantics or business rules rather than objectivity. I can't say that I got the idea 100%, but thanks for getting me a step closer. – Dennis Sep 07 '16 at 21:55