I am unclear about your Subcat table design. Is the PK of Subcat just the Subcat code, or is it Category code & Subcat code?
I think the design should be one of the following:
1) Category ---< Subcat ----< Product
Here Subcat has PK of (Category code, Subcat code), and Product has just one FK to Subcat.
2) Category ---< CatSubcat >--- Subcat
CatSubcat ---< Product
Here Subcat has PK of just (Subcat code), and there is a new table CatSubcat that holds the valid combinations of Category and Subcat (with a PK of (Category code, Subcat code)).
Product has just one FK to CatSubcat.
Design (2) is preferable if Subcat has attributes that do not depend on the Category. In either case, Product has just one FK that is constrained to valid Category/Subcat combinations, so there is no possibility of recording an improper Subcat value.
As for constraining the Subcat values during data input, that is an application issue not a database issue. A possible solution would be:
- Disable input of Subcat code until Category code has been entered
- Define picklist for Subcat code as:
FROM Subcat -- or FROM CatSubcat
WHERE category_code = <value entered for Category>;
You would need to decide what action to take if the user then goes back and changes the Category field, e.g.
- clear the Subcat field
- revalidate the Subcat field and raise an error if no longer valid
To create the table with valid cat/subcat combos I fgure a query would be the best method, however what about when there is an update to the Category or subcategory tables.... can this be automated ate all ?
Sorry - just learning and I have a heap of books that are sort of answering my questions.
I'd say the table of valid cat/subcat combos would have to be maintained by hand (e.g. via a form). If a new category or subcategory is created, then somebody is going to have to define the valid combos it can appear in. The only way it could be automated would be to assume that ALL combos are valid - if not, how would the database know which were not valid? But if ALL combos are valid, there's no point in having them in a table!
If you wanted all possible combos created as a default, to be subsequently corrected by human intervention, then you could use code like this example (for after insert of a new category):
INSERT INTO valid_combo ( cat_code, subcat_code )
SELECT 'newcatcode', subcat_code