views:

answers:

+1 Q:

Relational modelling question

We have three entities called Product, ProductType, and ProductCategory.

Let's pretend we have three kinds of ProductType: Book, Music, and Video.

We have three different ProductCategory's for Book: Fiction, Novel, Technical.

Three different ProductCategory's for Music: Rock, Jazz, Pop.

And we have three different ProductCategory's for Video: Fiction, Comic, Drama.

A Product has a ProductType and can have many ProductCategory's. But its ProductCategory's should match its ProductType. For example, if its ProductType is Book, it can only take Fiction, Novel, and Technical as ProductCategory's.

Is it possible to model this schema with this restriction (i.e. that ProductCategory's for a Product should match with its ProductType) without using application code or triggers, etc -- Just using tables, foreign keys, etc.

How would you model this?

+2 A:

PRODUCT_TYPE

PRODUCT_TYPE_ID (pk)
PRODUCT_TYPE_DESCRIPTION

PRODUCT_CATEGORY

PRODUCT_CATEGORY_ID (pk)
PRODUCT_TYPE_ID (fk to PRODUCT_TYPE.PRODUCT_TYPE_ID )
PRODUCT_CATEGORY_DESCRIPTION

PRODUCT

PRODUCT_ID (pk)
PRODUCT_TYPE_ID (fk to PRODUCT_TYPE.PRODUCT_TYPE_ID )

PRODUCT_CATEGORY_MAP

PRODUCT_ID (pk, fk to PRODUCT.PRODUCT_ID)
PRODUCT_CATEGORY_ID (pk, fk to PRODUCT_CATEGORY.PRODUCT_CATEGORY_ID)
PRODUCT_TYPE_ID (pk, fk to both PRODUCT.PRODUCT_TYPE_ID and PRODUCT_CATEGORY.PRODUCT_TYPE_ID)

OMG Ponies 2010-10-30 01:17:03

Wow, could you make that font a bit bigger, please? I'm having a hard time reading it :-)

paxdiablo 2010-10-30 01:23:31

Product(1, 1) has ProductType(1, Music). We also have ProductType(2, Video). We have ProductCategory(1, 2, Drama) for Video and ProductCategory(2, 1, Jazz) for Music. Now we can have ProductCategoryMap(1, 1, 2) which is invalid.

Bytecode Ninja 2010-10-30 01:29:29

In `ProductCategoryMap(1, 1, 2)`, `PRODUCT_TYPE_ID` is `2`. We have a row in `PRODUCT_TYPE` with `PRODUCT_ID 2` -- i.e `(2, Video)` -- and we have a row in `PRODUCT_CATEGORY` that its `PRODUCT_TYPE_ID` is also `2` -- e.g. `(1, 2, Drama)`. Or am I missing something?

Bytecode Ninja 2010-10-30 01:45:34

@Bytecode Ninja: I'm missing how that is invalid - the type and category matches between the PRODUCT_CATEGORY_MAP and PRODUCT_CATEGORY; the type matches between PRODUCT and PRODUCT_CATEGORY_MAP.

OMG Ponies 2010-10-30 01:59:02

Only `Video` products can have `Drama`. Now we have a `Music` product that has the category `Drama`. Mmm... where did your other comment go? :D

Bytecode Ninja 2010-10-30 02:09:05

@Bytecode Ninja: I corrected the typo in my answer to point to the PRODUCT table. I wouldn't make fun of those who try to help, when they have difficulty understanding what you're trying to communicate.

OMG Ponies 2010-10-30 02:15:31

This still seems to have the same problem as before.

Bytecode Ninja 2010-10-30 05:31:50

+1 A:

That's easy, it is a simple two-level classifcation issue. in your app, you need two separate drop downs, the ProductCategory is filled after the ProductType has been chosen.

One clarification: Your statement "A Product has a ProductType and can have many ProductCategory's" is contradicted by your description. A Product can have only one Product Category (Fiction, Jazz), which is based on a ProductType(Book, Music).

There is no need for surrogate keys here (there may be in other modelling requirements), they are merely redundant. For simple classifications like this, CHAR(1) or (2) is much better, user and developer friendly (when you are scanning output, you know "B" means "Book", etc), as well as being faster than any numeric key (except of course tinyint).

There is no "trick" here, it is straight Normalisation, which supports the rules you identified.

Product Classification

I do not understand the need for a "map" table.

I have provided a surrogate key for Product, but of course you need other keys, in order to implement reasonable constraints.

Questions ?

Responses to Comments

Ok, so your requirements are not clear, and it appears they are now changing. When you answer my specific questions in the comments, the model required to support your requirement will be easy. In order to assist, I have published two possibilities. Of course it is incomplete, pending your answers:

Two Possible Models

Your admin vs users "tightness" seems to be very loose.

The Product.ProductType is set by the admin. This allows the admin and users to choose Any of the valid ProductCategories for the Product for whatever use they have.
For each Product, the admin chooses the ProductType AND A SUBSET of ProductCategories (from the list of ProductCategories that are valid for the Product.ProductType). The users can then use only the ProductCategories CHOSEN by the admin for the Product, for whatever use they have.

Respond please, and then I will publish the final version.

PerformanceDBA 2010-10-30 04:40:14

There could be a product like "Star Wars" that's a Movie, and has two categories: 'Vintage' and 'Sci-Fi'. Or a movie can have both 'Horror' and 'Sci-Fi' categories.

Bytecode Ninja 2010-10-30 05:34:22

So now you are saying, a Product can have more than one category. if so, who picks which category the product is sold as; and how (what data do they use) do they make that decision ? Are you saying: A product has one ProductType; one ProductCategory; EACH of which is chosen from a set of valid ProductType-ProductCategories ?

PerformanceDBA 2010-10-30 06:31:00

Think of a ProductCategory as a tag or label. But when a Product is Music, it can only be labeled with certain labels that are valid for Music. And these are used when an admin or operator ads new products to the system. So he wants to add 'Star Wars', then he puts it in the Video ProductType, and then the list of valid categories for Video show up and he can associate one or more of those categories with the product. Having said that I think the schema that you have provided solves this problem. It might just need some minor modifications. I'll double check that and let you know.

Bytecode Ninja 2010-10-30 10:43:29

No I think your solution only works when a Product can have at most one ProductCategory.

Bytecode Ninja 2010-10-30 10:46:55

Yes, as identified in my post. That's what you identified in your initial "requirement". Now you are asking for something else. I have asked clarifying questions. If and when you supply answers to those questions, I will be able to provide the change model. So far (unless you change your "requirement" again) ProductType the relation between them and Product is "pending", awaiting your clarification. It is not possible to supply a model to someone who does not know their requirement.

PerformanceDBA 2010-10-30 20:37:34

I do not need descriptions, I know all that; I need specific answers to my specific questions in comment 2 above: (1) WHEN the admin adds the Star Wars movie, OF the several categories available to them for movie, HOW (on what basis) do they choose ONE category. (2) OR, do they assign more than one category to Star Wars, and if so (3) how MANY.

PerformanceDBA 2010-10-30 21:05:56

"That's what you identified in your initial "requirement". Now you are asking for something else." I haven't changed my question and my initial requirement was: "A Product has a ProductType and can have many ProductCategory's".

Bytecode Ninja 2010-10-31 08:21:41

Never mind. What about the remaining specific questions in the Comments ?

PerformanceDBA 2010-10-31 12:57:06

ansaurus

tags: