Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Australia
    Posts
    2

    Question Database Design Help

    I am just getting started in databases (a newbie in this area of IT)

    I am trying to design a product and service database.

    I would like a each PRODUCT to have CATEGORY AND SUBCAT

    However some subcategories are duplicated in categories (example - Mainboards can have the SubCat Socket478 as can the CPU Category)

    I have it currently in 3 tables

    Product , Category and sub cat

    Product contains Cat and Subcat fields (linked via relationship to Prim Keys in respective tables).

    Is it possible in the datasheet view (product table) subcat field dependent on the selection in the cat field so it only pick subcats from that category, if not can it be done in a form.

    Or is my design all wrong ... any help appreciated.

    Cheers,

    Jester from OZ

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Database Design Help

    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:
    SELECT subcat_code
    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

  3. #3
    Join Date
    Jan 2003
    Location
    Australia
    Posts
    2

    Re: Database Design Help

    Thanks Andrew .

    I'm using access if this helps any

    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.

    Cheers

    Jester from OZ

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Database Design Help

    I don't really know Access.

    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
    FROM subcat;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •