1 Reply Latest reply on Mar 6, 2012 1:23 PM by philmodjunk

    Table Relations - Many tables to One

    Padster

      Title

      Table Relations - Many tables to One

      Post

      Hi All,

      I have been searching through the forums to look at possible solutions to a problem that I am having. I know logic tells me that I should be doing this in one table, but I'm trying to find a way to make this work in it's present for.

      The reason for my resistance is that the 4 Supplier tables are potentially going to be changing regularly and at different times. It is possible that the whole table will change when the supplier releases their new pricelists. Also as they are differnet suppliers, it is highly likely that the format will be extremely different.

      I have attached a link to a small Database of what I'm trying to acheive;

      http://dl.dropbox.com/u/54457200/Provider%20Data.fp7

      The idea is to have the Item Name and Item Price automatically populate when the Item Order is created. This means that the Main table will have to link to the indivdual supplier tables, Item Order --> Item_ID 

      The problem that this means is that the one table item will be linked to 4 tables, each with different references.

      Is there a way that I can get the two fields in the CustomerInfo table to look a the corresponding Supplier table based on the Itme Order, being A#, B#, C# or D#?

      Pad

        • 1. Re: Table Relations - Many tables to One
          philmodjunk

          I know you don't want to, but use a combined table. If there are specific differences that are supplier specific, you can use related tables that are supplier specific for those details, but keep a unified table for all the fields that are common to all the suppliers.

          the 4 Supplier tables are potentially going to be changing regularly and at different times.

          I really don't see why that requires separate tables. You can work with groups of records specific to a given supplier without having to keep them in separate tables in order to do so.