3 Replies Latest reply on Jul 13, 2011 4:15 PM by philmodjunk

    More of a database structure question...



      More of a database structure question...


      In this scenario have four tables.

      Table A: a pre-populated table linked from a different database; it contains the fields CustID and Name; this data is read-only.

      Table B: an empty table; it contains the fields CustID, Name, CustData001 and CustData002

      Table C: an empty table; it contains the fields CustID and CustData003

      Table D: an empty table; it contains the fields CustID and CustData004

      All tables are linked through the CustID field.

      Table B is the entry point to the database.  When a user creates a new record and enters information into [Table B].[CustID], one of two things happens:

      (1) If the CustID doesn't exist in either Table C or Table D, records are created in C/D with the associated CustID.

      (2) If the CustID does exist in the either Table C or Table D, these records become associated with Table B.

      Here's the problem.  The information in either Table C or Table D may change, but in event it doesn't, I'd like to have that information associated to the correct record.

      Having a single unique identifier (CustID) won't get me what I need... because it's basically no longer unique.  I thought about creating another unique identifier (ArtifactID), but I'm not sure if this is the way to go about it.

      I probably didn't explain that in the best manner.  See the attached image.

      Record6 would continue to use the results of the last associated [Table B].[CustID]


        • 1. Re: More of a database structure question...

          Such generalized examples are often harder to figure out than the original problem you are trying to solve because we don't see the "big picture" and our suggestions may fail due to not taking that missing information into account.

          Something is seriously wrong with your design here.

          CustID Should, absolutely without exception be a unique value and it should not ever be allowed to change. A single field in your parent record should assign this value as an auto-entered serial number. If you are getting a customer ID of some sort from a source external to FileMaker, you should not use it as your ID value to link these tables.

          I do not see any rational in your example for why customer ID = 41051 displays a CustData002 value for Florida in Record 1 and a value of Arizona in Record 4. That makes no sense here. It may make sense in your original situation, but I can't see why this would be so here. This discrepancy suggests that your tables should be related by some other field than custID unless this is a data entry error on your part.

          • 2. Re: More of a database structure question...

            Thanks for your response.  The design worked for the original set of instructions, but fails now that the project has changed and I'm just trying to figure out the direction to go.

            I realize that I should have explained this better.

            Originally, this was a flat form that contained client information.  Most client information, such as name/email/phone, doesn't change.  The idea was that changing data would appear in the main table (Table B), while recurring information was placed in other tables (Table C/D) so individuals wouldn't repeatedly have to enter it in.  These tables were are by CustID.

            Table B represented user inputed data.  The Florida/Arizona was just to show that it was random, but probably not pertinent to the issue at hand.

            Tables C/D are assumed to be recurring information.  In Record1, (under normally situations) I'd want that information to appear each time that the user typed in CustID=41051.  However, in Record4 something changed.  I need to create a new (CustID=41051) record in Table C to reflect the fact that Canada is now an option.... so I'm assuming that I have two records relating to '41051' now, one for 'China' and the other for 'Canada'.

            I no longer have the one-to-one relationship that I have before using the CustID as the unique identifier.

            So that are basically two issues

            (1) Attributes (Tables C/D) will occasionally change, but must be associated with the corresponding correct record in Table B

            (2) The last/most-recent occurrence of attribute (Table C/D) will be used as the recurring data to populate future records as in Table D, Records1 and Record4.

            I hope that explains the situation better.

            • 3. Re: More of a database structure question...

              It helps, but doesn't explain why you want a second record with the same customer ID number instead of just editing the existing record with the new data.