7 Replies Latest reply on Feb 24, 2015 3:56 AM by NickLightbody

    Customer database design

    ChrisG

      To me this has been an age old conundrum. A customer record, is it a company or is it an individual? If it is a company then it has individuals working there. What is the best way to structure this? Currently I have 3 tables; Customer, Company and Contacts. The customer table is the main entity with related company and contact data. Essentially the customer table behaves as a join table between company and contacts. This gives me a single parent ID to link to regardless of wether I'm referring to a contact or a company.

      I was just wandering if any of you have a more elegant way of structuring this data?

       

      regards

       

      Chris G

        • 1. Re: Customer database design
          Bill_Harper

          A lot of ideas and concepts have been studied during my RDBMS design and development days since 1985. Back in the early '90s, I had the honor to meet and work with Les Cardwell prior to him earning his PhD in database design theory when he was creating his new concepts on "Abstract Normalization."

           

          Here's his "White Paper": An Advanced Concept of Relational Theory

           

          Since then, I have employed this concept in all projects where "Data Driven" is my design "motto" in order to minimize data redundancy and speed up most all database operations.

           

          If interested, see my 01/05/2013 9:50 AM reply on this subject: DB Design/Normalization

           

          Here's another related topic: FM Newbie - Easy Question for someone about Contact Mgmt.

           

          Hope you find these discussions of some value that might lead you to a desired design construct.

           

          Bill

          • 2. Re: Customer database design
            ibrahim_bittar

            Hi Chris

             

            I use the same concept as you: Customers are the organizations which we have a commercial relationship with and contacts are the individuals who work for a customer.

             

            Sometimes an individual can be a customer.

             

            Sometimes an individual may work for two or more customers.

             

            What I do is to have a Customers table, a Contacts table and a join table for customers and contacts, so if a contact works for more that one customer (or vendor, the structure is the same) the the join table will have two records for that specific contact.

             

            See attached image:

             

            Screenshot 2015-02-23 10.09.58.png

            • 3. Re: Customer database design
              ChrisG

              Thank you Bill and Ibrahim. I asked for "food for thought", now I have a 5 course meal to chew over.

              • 4. Re: Customer database design
                richardsrussell

                I don't know about "elegant", but a system I'm currently working on for a small family-owned and -operated business uses 3 main tables:

                • Destinations (physical locations, each with a name, street address [necessary for shipments, tho PO box alone suffices for billings], and possibly main phone [receptionist, front desk, info line])

                • Humans (actual people, each with a name, eddress, and possibly personal phone [cell or home, not office], but no address; some of these people work at multiple locations [office, warehouse, garage] within a single company)

                • DHLinx (a join table between Destinations and Humans, each with a department name [if any], job title [if any], possibly direct phone [office or extension], and designation as either the "TalkTo", "ShipTo", or "BillTo" person for that destination)

                 

                A "Destinations" record may also have a link to a parent company (another "Destinations" record), since such a link makes the parent company's "DHLinx" records visible to its branch offices and subsidiaries, useful when the "BillTo" person is in a corporate central accounting office.

                 

                Occasionally my client does business with private individuals, in which case I have both a "Humans" record for Sue Smith and a "Destinations" record for "Sue Smith Household" joined via "DHLinx".

                 

                Obviously, there are other tables involved in this system as well (Invoices, Line Items, Visuals, Transaction Types, etc.), but the above summary is how I sort out the people from the property. (Aside: I used "Humans" instead of "People" as the table name, because I like to be able to use the initial letter of the table in my internal abbreviations of table names, and "P" was already taken up by "Packets", but "H" was still available.)

                • 5. Re: Customer database design
                  nicolai

                  Exactly this question did not let me sleep at night when I was at University. I discussed it with a professor reading database theory and a few Phd students, later with a few developers as well and tried different approaches myself. The answer is - there is no answer. Whatever method you chose will have its own advantages and drawbacks. RDBMS rely on a rigid structure and to add more flexibility you will need to sacrifice something. In your case it is an overhead of creating and maintaining a join table. A lot of developers would advocate this solution and I used it on a few occasions.


                  Finding an ideal always open schema will not be resolved by more expensive RDBMS as at the core they have the same rigid structure.


                  I am not saying what it is impossible to build a good flexible structure in FileMaker. I am actually saying that any reasonable design is going to work.

                   

                  The only other alternative are graph databases which can resolve these problems easily. Unfortunately, they much harder to use and their development tend to be much more expensive.

                  • 6. Re: Customer database design
                    ChrisG

                    Thank you everyone for your feedback. Aside from the "normalised data" paper which I need to get my head around, I can say that my thinking is at least quite similar to what others are doing.

                    • 7. Re: Customer database design
                      NickLightbody

                      Chris,

                       

                      I have found more recently that whenever I am faced with a FileMaker problem, having found an initial solution, if I look at it very critically and ask myself what can I remove and have it still work, I always end up with something simpler and faster.

                       

                      As a general rule in response to your specific question, as an ex lawyer, I take the view that Clients are legal entities whom you can bill. Each Client must have a Billing Contact, selected from the Contacts table, ever if it is only an office holder such as the VP Finance.

                       

                      As your other respondees have made clear there is no absolute answer, only one that best fits the use case for the solution - with in my view - the thing simplified as much as possible.

                       

                      Cheers, Nick