Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Location
    UK
    Posts
    4

    Question Unanswered: Some help with database design please

    Hi All,

    New to the forums I know a little about databases but not an expert by any definition of the word so please provide idiot proof answers I won't be offended

    My questions

    I am making a database to represent definition of certain products. The products can be composed of a single component or multiple components. The multicomponent products are constituted from one or more single components. The single components are products in their own right.

    So my question is what is the logical design approach? I started by defining two basic tables Single Products and Multicomponent Products thinking they would be linked by a many to many relationship. When I look at the two tables and do a normalisation check I find considerable overlap in the fields in the two tables, not surprising perhaps as both are "Products" just different types of products. This makes me think I am not taking the best approach for the design or I have simply got it wrong.

    The end goal will be to list all the products in a single catalogue but if a product is multicomponent then have the ability to view a list of the components and access information about them.

    Bearing this goal in mind intuitively I wonder whether a need a uniqueID (primary key) for multicomponent products distinct from single products.

    Finally I am thinking that at some stage I need to be able to enforce some referential integrity since if a single product is discontinued but that product is a component of a multi-component product then I would want to be made aware of that.

    Quite a lot for a first question but hope somebody can provide some useful advice

    Thanks

    HowardB

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hello Howard, and welcome to the MS Access forum.

    A common approach to this is a single table listing all products. Add the following field: HasOwnPartsList as Boolean.

    You can then have an additional table with all products where HasOwnPartsList is True. You only need three fields, in essence: PartsListNo (the "assembly" level), ProductNo (item level), and ProductQty.

    When you get to making the relationship, you want to relate your product table as one-to-many with the parts list table, and they should be related by the ProductNo field in the product table and the PartsListNo of the parts list table.

    When you finish this step, we can talk about creating data entry forms for both tables.

    Sam

  3. #3
    Join Date
    Mar 2012
    Location
    UK
    Posts
    4
    Hi Sam,

    Thanks for the quick reply, I will give it a go

    So all products single component and multicomponent appear in the same table and if a product is marked as multi-component then the components of the multi-component product appear in a seperate table correct?

    Does this work even though the components of the multi-component tests are products in their own right?

    thanks

    Howard

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    So all products single component and multicomponent appear in the same table and if a product is marked as multi-component then the components of the multi-component product appear in a seperate table correct?

    Does this work even though the components of the multi-component tests are products in their own right?
    'Yes' to the first question, and 'Certainly' to the second one.

    Sam

  5. #5
    Join Date
    Mar 2012
    Location
    UK
    Posts
    4
    OK Sam I now have two tables

    1. tbl_Products

    Primary Key: ProductID
    Product_Name (Text)
    multi_component_product (Yes/No) Boolean

    2. tbl_Product_Components
    Primary Key: ComponentID (Autonumber)
    ProductID (Foreign key)
    Product_Name

    Have created a 1 to Many relationship between Table 1 ProductID and Table2 ComponentID

    So am I right in thinking that tbl_Product_Components will only contain the ProductIDs (derived from tbl_products) of items which are components of products in tbl_products where the multi-component test indicator is set to YES?

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    2. tbl_Product_Components
    Primary Key: ComponentID (Autonumber)
    ProductID (Foreign key)
    Product_Name
    Hi, Howard,

    I'm not up to the question yet; I'm stuck on the structure of the 2nd table. I would get rid of the PK; Your ProductID, which is a FK, will function just fine.

    Add a field called Component (or Item, Sub-assy, etc.) which will also be a ProductID from the first table; in fact, when adding an item to a record in this table with a combobox on a form, you will need to use this ProductID in the combobox.

    If you feel you need a PK, it should be a multi-field key, using the ProductID and Component fields as the unique key.

    Also, what is the Product_Name? Is that the name of the assembly or the component level? If it's the assembly name, why do you need it to be repeated so many times? I would have the Product_Name field after the Component field, and it would be the product name of the component. Also, to prevent confusion, I would call it ComponentName, and it would be the Product_Name from the first table for that ProductID.

    Sam

  7. #7
    Join Date
    Mar 2012
    Location
    UK
    Posts
    4
    Hi Sam,

    OK done.

    You asked:

    What is the Product_Name? Is that the name of the assembly or the component level?

    On tbl_Product_Components the product_Name is the name of the component which I have now renamed component_name. Once this is working both the component_ID and the component_name will be derived from the Product_ID and the Product_Name from the first table.

    regards

    Howard

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Have created a 1 to Many relationship between Table 1 ProductID and Table2 ComponentID

    So am I right in thinking that tbl_Product_Components will only contain the ProductIDs (derived from tbl_products) of items which are components of products in tbl_products where the multi-component test indicator is set to YES?
    Hi, Howard,

    OK, good so far. Now to your previous posting, quoted above. You would change the one-to-many relationship to be from Table 1 ProductID to Table 2 ProductID. ComponentID is now a lower-level item, and would not relate properly to anything in Table 1.

    Yes to your question.

    You would make the data-entry form for Table 1, with a checkbox for the True/False field. If the checkbox is checked (default for the checkbox, of course, should be False), you should have your VBA open a new form for data entry for Table 2 right away, to prevent overlooking the entry of the parts list.

    Sam

Posting Permissions

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