3 Replies Latest reply on Oct 25, 2010 10:38 AM by philmodjunk

    Displaying Many-to-Many Relationships



      Displaying Many-to-Many Relationships


      I manufacture nine items. Each has five basic cost factors: a. Raw Materials, b. Manufacture/Assemblage,
      c. Packaging, d. Distribution, and e. Advertising. I have separate tables for each of these tables with datarows for each of the nine items. How should I construct a many-to-many database which would enable me to query one of the nine items and see the five cost factors for that item displayed on one page?
      Could anyone help me? Many Thanks, Gasser

        • 1. Re: Displaying Many-to-Many Relationships

          This isn't actually a many to many relationship as any one record in a table matches to just one record in each of the other tables. You could, in fact define all these cost values as different fields of the same record. Of course there could be very good reasons why you wouldn't do this--it depends on what you need to do with your database. If the cost factors are simply numbers that are entered--not computed from multiple related records, then a single table simplifies things quite a bit.

          Working from the relationships you've specified. You'd have relationships that look like this:

          Products::ProductID = RawMaterials::ProductID
          Products::ProductID = Manufacture::ProductID
          Products::ProductID = Packaging::ProductID
          Products::productID = Distribution::ProductID
          Products::ProductID = Adverstising::ProductID

          You can then set up a Products layout and put fields from related records from the other tables on this layout, if there is just one such related record in the table. If there are multiple related records, you can place a portal to them on the Products layout.

          • 2. Re: Displaying Many-to-Many Relationships

            First rate advice. Placing the five variables in one table should save me much time and complication. Thank you so much.

            However, I now find the need to comment on the variable itself, as it pertains to the particular item.  And to be able to change that comment, as I become wiser, gain more experience.  Could using one table become too large or cumbersome? Should I instead use the five variant tables and relate them somehow to the Master (item) table?

            As you can see, I'm a novice.

            • 3. Re: Displaying Many-to-Many Relationships

              Tables can contain literally hundreds of fields. Whether that is a good idea or not is often a judgement call.

              If you wish to track one and only one comment per variable per product record, I'd use a comment text field for each field in the same table. If you are needing something more along the lines of a "log" of comments on each variable that grows over time, then a related table of comments where you can list multiple comments for each variable makes more sense.