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开发者_如何学Python 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?

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)

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 here. 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.

Link to 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.

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:

Link to Two Possible Models

The "tightness" of your control re administrator vs users seems to be very loose. Please choose one of the following, so that we can progress and close the question:

1. The Product.ProductType is set by the admin. This allows the admin and users to choose Any of the valid ProductCategories for the Product.ProductType for whatever use they have.

2. 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.