Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011

    Self Referential Many-to Many relationship

    After a long while doing research, i've discovered a SRM2M Relationship is what I need here, but i fail to understand it quite well: this is the problem.

    My only table format is "IDproduct, product, cost". We have tons of products, but only some of them are normally for sale: because some products are their components, but they are living on the same table. I we selled cars, in this table i would have, for instance, the registries:

    1 CAR
    2 ENGINE
    3 10mm SCREW

    So, what i would need is a self referenced many to many relationship to indicate which parts are components of other parts and viceversa. Of course, many parts can have the same components, and many components can form the same part.

    ¿Is this the way to go? Many have told me "Split it into two tables: Components and FinalProducts!". But the problem is that i have parts (like ENGINE) with aren't final products but still have components....

    ¿Maybe three tables? ¿BasicComponents, MidComponents and Final Products?

    ¿To complicate this even further, does MS Access support this type of relationship? Because It's a simple program and i would like to keep it in Access.

  2. #2
    Join Date
    Apr 2010
    how about:

    product_id, product
    1, car
    2, engine
    3, screw

    product, component
    1, 2 (engine is a component of car)
    1, 3 (screw is a component of car)
    2, 3 (screw is a component of engine)

Posting Permissions

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