8 Replies Latest reply on Sep 20, 2016 8:03 AM by Atefe

    different records with identical content

    Atefe

      Hello everybody!


      I develope a database with FM 13. The Data of one part of the DB should be treated in this way,
      There are about 20 lists and each list is assigned to different orders of one costumer. For example a Customer "John" has 200 orders and they should be numbered as John001, John002,...John199...
      All of these orders have the same list. that means the above-mentioned 200 records have a  identical list "Equiment".
      That means finally I have 200 records, in which just the suffixs of "John" are different.


      My question is, whether I should create really 200 records for each list or is there another way to have all these data without 200 times repeating 

      one record? Is there a better Idea?!


      I thank you in Advance!

       

      Regards,

      Atefe

        • 1. Re: different records with identical content
          coherentkris

          if each record is a discrete order then they should be kept as discrete records even if the content is the same

          1 of 1 people found this helpful
          • 2. Re: different records with identical content
            Markus Schneider

            if those orders result in something like an invoice, I would treat them as separate records..

             

            you could make a script that creates the orders with the corresponding line-items (list of equipement)

            1 of 1 people found this helpful
            • 3. Re: different records with identical content
              wimdecorte

              It depends on what data is tracked for each of those records across the different customers.  Things like:

              - does the price for one item change over time for the same customer

              - does the price for one item change based on the customer

              - ...

               

              Sometimes it helps to be a bit more descriptive and make the question less abstract.  Can you describe what items are in one order?

              • 4. Re: different records with identical content
                Atefe

                Thank you all.

                 

                Also in this layout, for each list, should just the name of Customer,suffix-numbers and one or 2 other data be kept, but the problem is that, the field "John+suffix" must be used as the primary key for linking 2 tables. I mean in another table, I have some other info about the orders of each customer and I should give for example John023 to recieve infos of order 023 of John. 

                Because of this reason, these records should be somehow seperate and distingushable.

                I was thinkig about something like arrays, but I do not know, how it can work.

                 

                WR
                Atefe

                • 5. Re: different records with identical content
                  philmodjunk

                  but the problem is that, the field "John+suffix" must be used as the primary key for linking 2 tables.

                   

                  Sorry, but that's not the case. Just because you have such a field labeling each record does not mean that you should use it as your primary key nor does it mean that you should use it to link records in this table to records in another table in a relationship.

                   

                  In addition to the other good advice given here, it's very likely that each item in your "list" should also be a separate record producing a set of related records linked to a single parent record for each sales transaction.

                  1 of 1 people found this helpful
                  • 6. Re: different records with identical content
                    Atefe

                    Thank you for your answers.

                    Also I totally agree with you about selecting the primary keys. but unfortunatelly I have everywhere defiend the ""Customer+suffix" as the Pk or fk...

                    Another Question, if I create discrete records, It does not cause to slow performance of Database? because that would be about 100000 records!!!

                     

                    WR
                    Atefe

                    • 7. Re: different records with identical content
                      philmodjunk

                      The number of records shouldn't be an issue as you won't be dealing with them "enmasse". The fact that you would now have narrower tables (fewer fields per record) can even result in performance improvements in some cases. Plus you gain flexibility that you do not have with your current design.

                       

                      But this isn't simply producing a set of discrete records, it's a matter of producing a flexible data model that combines better table design with relationships that correctly link the records.

                       

                      A typical invoicing solution is built around these basic tables and relationships:

                       

                      Customers----<Invoices----<LineItems>-----Products

                       

                      Customers::__pkCustomerID = Invoices::__pkInvoiceID

                      Invoices::__pkInvoiceID = LineItems::_fkInvoiceID

                      Products::__pkProductID = LineItems::_fkProductID

                      but unfortunatelly I have everywhere defiend the ""Customer+suffix" as the Pk or fk...

                      Yes, and I am strongly recommending that you change this. It appears that you are trying to use the same key to identify the customer and the invoice. Note that with the above recommended data model, you have one key that identifies the customer and one that identifies the invoice.

                       

                      Using names as primary keys is going to be an ongoing source of trouble and unnecessary complication in your design. People change their names. Names are not unique. Users type in names incorrectly and do not immediately discover the mistake--which can result in records linked to each other by a misspelled name.

                       

                      A primary key in FileMaker should be defined a simple serial number or UUID text generated by the Get ( UUID ) function.

                       

                      The ideal primary key should meet the following criteria.

                       

                      a) Always unique

                      b) Is never, ever, no never changed once it is assigned during record creation

                      c) Does not include "encoded meaning" data -- such as a customer's name

                      d) Is created via native database features in as simple and "bullet proof" as can be arranged.

                       

                      c) and set up situations where you end up violating a) or b)

                      1 of 1 people found this helpful
                      • 8. Re: different records with identical content
                        Atefe

                        Thank you very much Philmodjunk for your useful and complete answer. You are right, maybe I should try to make some changes to my DB, although it is not so easy now...

                         

                        I thank you all again for your help.

                         

                        Warm Regards
                        Atefe