2 Replies Latest reply on Apr 12, 2010 12:52 PM by philmodjunk

    Customer Relationship confusion



      Customer Relationship confusion


      I've been working on setting up my database and can not figure out how to list the tables and make the connections when I have several different types of customers and a customer may have several different layers of customers.  Database Topic is tracking vehicles


      Example would be my main customer may be a dealership who has no ownership in the vehicle but who is hiring me and who I will bill for the work.

      Then if my customer is a dealership I now need to create a record for the owner of the vehicle who could be a company or an individual.

      Now the owner who I've identified as a company is a leasing company and they lease it to another company or individual.

      I have also identified the lessee as a company who now has an operator of the vehicle who is a individual.


      As you can see, this particular example has 4 levels of a customer related to one vehicle.  I originally set it up with 4 customer tables, but I can see getting confused later trying to find a record for a company - which table to I search in.


      I would appreciate any help I can get understanding how all these possible senarios work together and how to relate them.



        • 1. Re: Customer Relationship confusion

          If you wanted to put all customers in 1 table The only way I think you could do it would be to create a customer database (for customer info only)  and a "transaction" database that contains info about the transaction for the car and a "hierarchy" database.


          The hierarchy database would be a related portal off of the transaction layout, on which you enter the other "customers"  and their hierarchical order. So your entry into the portal for your example would be:


          Dealership 1 name

          Dealership 2 name

          Leasing company name

          Lessee company name


          If you wanted to search for all cars related to a business you would do a search for the name in the hierarchy database and all of its related records in the Transaction database.  It would find all final customers that had some dealings with that company.



          • 2. Re: Customer Relationship confusion

            Since you could have many customer records linked to many vehicles this is a classic many to many relationship.




            Your fields would match like this:

            Vehicles::VehicleID = Vehicle_Customer::VehicleID


            Customer::CustomerID = Vehicle_Customer::CustomerID


            You can place a portal to Vehicle_Customer on a Vehicle layout and use it to select existing customer records to link them to the vehicle record.

            You can place a portal to Vehicle_Customer on a Customer layout and use it to select existing vehicles to  link them to the customer record.


            That said, you might want to separate your customer data in to Companies and Personnel as a way to link multiple people to the same company.