0

I am sure I am not not the first person to come up with this question but I cannot find a good answer and I will admit that database design is not my primary strength.

Lets say I have a Product and this Product can have subtypes, Product A, Product B, Product C and Product D. The supertype Product will have some common attributes, while the subtypes will have attributes specific to those subtypes. Pretty standard supertype/subtype design.

Now I have a Users entity. A Product can have many User and a User can have many Product. So ideally I would like a join table on these with some specific attributes about that join. The problem I have is that I do not want to do a Product-User join table but more like a join with User and the Product subtype, so a ProductA-User, ProductB-User, ProductC-User, ProductD-User. The reason being that join of these two entities has its own attributes specific to that Product and User join.

Does this design make sense? Or is there a better way to handle this type of behavior. I like the standard supertype/subtype for my Products but I am not sure how to approach when I need to join those with other entities.

Tyler Cvetan
  • 3
  • 2

2 Answers2

2

It make sense to join to the supertype in some databases, it make sense to join to the individual subtypes in some databases, and it make sense to join different data to both supertype and to subtypes in some databases.

Is it really the case that a table for (ProductA, User) will have different columns than (ProductB, User)? If so, then there's your answer. Different attributes, different things, different tables. Separate m:n tables, and reference the subtype.

(Later)

I answered another SO question and included code that did exactly this.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • I looked over your example in there, thanks for pointing that one out. It is the case that the subtypes when joined with a user would have different columns. ProductAUser and ProductBUser would have different columns. – Tyler Cvetan Mar 31 '11 at 14:55
0

Well, if you want to capture different information about the ProductA-User relation and the ProductB-User relation, a Product-User table doesn't seem to make sense. Personally, when I've had a Product table, as well as ProductA, ProductB, etc, I've regretted it, either for performance or maintainability reasons. My gut reaction is generally to eliminate the Product table.

joelt
  • 2,672
  • 2
  • 24
  • 32
  • I generally agree with this. A couple of other things to think about are sparse columns on some databases, where a null column takes less space. Also, you could create a view on top of a union of the ProductA, ProductB tables if you need one place to query. – squawknull Mar 29 '11 at 01:27
  • So with this route, you are saying there is really no reason to use a supertype/subtype design and just have a ProductA, ProductB, etc tables and then individual join tables with the User? This might have some redundancy of data but if it will be less of a mess later on it might be the better option. – Tyler Cvetan Mar 29 '11 at 02:23