locked
Question Creating Tables RRS feed

  • Question

  • I'm working on the tables for a database, but I'm wondering if I need this 2nd table I have, or maybe I haven't normalized this right. 

    Here are my two tables so far:

    tblProduct

    Product_ID(Primary Key, autonumber)

    Product Type

    Brand

    # In Stock

    Location

    tblCategory

    Product_ID(Foreign Key)

    Category

    I have the relationship being one to one since no record will contain more than one category.

    Do I need a separate table for category you think? Each record will only be part of one category. So I figured breaking category into its own table would make searching by category easier. But since it's one to one, could it be placed back in the product table?

    Tuesday, February 21, 2012 3:13 AM

Answers

  • Do it the other way around. Drag Category_ID from Product to Category, the TABLE.

    Tuesday, February 21, 2012 9:44 PM
    Answerer
  • If the query uses an (NONCLUSTERED) INDEX on name, it will find the Id and then go to the TABLE via ID (assuming it is a CLUSTERED INDEX). The purpose of a PK is not for searching. It is a logical concept to keep records unique.

    Tuesday, February 21, 2012 10:19 PM
    Answerer

All replies

  • I think tblCategory is the parent of the tblProduct as one category can have multiple products. BTW, I suggest to drop tbl prefix from the table names.

    So, the Product table (or Products) will have CategoryID foreign key to the Categories table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, February 21, 2012 3:26 AM
    Answerer
  • Hi,

    I think in normal cases there are many porduct in one category, so the tblCategory will be the parent table and tblProduct will be have foreign key for categoryID.

    Tables difinition will be something like this:

    tblProduct

    Product_ID(Primary Key, autonumber)

    CategoryID (Foreign Key)

    Product Type

    Brand

    # In Stock

    Location

    tblCategory

    CategoryID(Primary Key)

    Category

    I hope this is helpful.

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com

    Tuesday, February 21, 2012 5:50 AM
  • If each product has only one category, and each category has only one product, it is a true one-to-one, and it should be in one TABLE. Speeding it up, if actually required, can be acheived via INDEXing or the like and would not require a new TABLE.

    Category would be its own TABLE if there may contain more than one product. In which case, instead of tblCategory having an FK to Product, Product would have the FK to Product.


    Tuesday, February 21, 2012 1:09 PM
    Answerer
  • Category can contain more than one product. Each product or record cannot be in more than one category. So I guess it would be one to many with category being the parent table? So table product would have foreign key to table category?

    Product

    Product_ID (Primary Key, autonumber)

    CategoryID (Foreign Key)

    Product Type

    Brand

    # In Stock

    Location

    Category

    CategoryID (Primary Key)

    Category

    Tuesday, February 21, 2012 8:01 PM
  • Yes, the above schema as what I would apply in this case.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, February 21, 2012 8:10 PM
    Answerer
  • Yes.

    After populating a few records, it should become clear.


    Tuesday, February 21, 2012 8:28 PM
    Answerer
  • It's still not working correctly for me. I'm trying it out in Access for right now.

    For table Category, I have Category_ID as PK and data type is autonumber.

    The other field is category, which is text.

    For Product table, Product_ID is PK and is an autonumber.

    In the relationship diagram, I have it going from Category_ID in table Category to Category_ID in Product table. Relationship is 1 to many and I have referential integrity enfored.

    If I make a form and put in some test data, it lets me put the category in, but it won't allow me to enter any data in the other fields. There will be only 2 categories.

    Tuesday, February 21, 2012 9:38 PM
  • Do it the other way around. Drag Category_ID from Product to Category, the TABLE.

    Tuesday, February 21, 2012 9:44 PM
    Answerer
  • I think that fixed it, I needed to change category_ID in Category table to an autonumber too. Then I drew new realtionship. Thanks all for the help. One more question, I'm not sure I understand completely what the primary key in products does, (the autonumber). I know it uniquely identifies each record, but if I do a query for something and want the criteria to match something specific in the 'Make' field, does it search the database by the unique identifier? 

    Tuesday, February 21, 2012 10:09 PM
  • If the query uses an (NONCLUSTERED) INDEX on name, it will find the Id and then go to the TABLE via ID (assuming it is a CLUSTERED INDEX). The purpose of a PK is not for searching. It is a logical concept to keep records unique.

    Tuesday, February 21, 2012 10:19 PM
    Answerer