none
Table Design: Storing customer name, company name or both RRS feed

  • Question

  • I am developing an application for a customer who needs to track transactions for either an individual, a company or and individual who works for a company. Just trying to wrap my head around how to setup the tables for this. I could add a CompanyID field to the customers table and set table validation to ensure that a record will not save if both Customer name and CompanyID are empty but this the transaction is for a company only then most of the fields in the customers table will be empty for that record. Not a good design.

    Any suggestions would be appreciated.

    Monday, February 22, 2016 10:12 PM

Answers

  • Hi, Dtours<u1:p></u1:p>

    >>I would almost want to have a CompanyNames table and a CustomerNames table linked to a Customers table that only holds a CustomerID, CompanyNameID and CustomerNameID field. So on the base of your description you think like below.

    But I would like to suggest to design a table like below.

    So here you only have to make 2 tables instead of 3 like you are currently doing. The design of the table will be simple.

    On the base of your description I would like to give you simple suggestion that you make 1 table which contains the common properties of Company and individual. And make another table which contains the data about company and individual from which you make the difference between company or individual and create a relationship between 2 tables. if you work like this then your table will not contains empty fields.  For more information regarding your issue please provide detailed information about your issue.<u1:p></u1:p>

    >> Then just make sure that there is a value stored in CompanyNameID or CustomerNameID field (or sometimes both). This is where I am currently at anyway...

    If you work by suggested way then there is always value available for either company or individual.

    Regards<u1:p></u1:p>

    Deepak

    • Marked as answer by Dtours Thursday, February 25, 2016 5:59 AM
    Tuesday, February 23, 2016 7:23 AM
    Moderator

All replies

  • You haven't really given us enough information.

    What are these transactions?

    What is the company or individual? Are they the same thing (e.g suppliers or customers)? Do you need to distinguish between them and why?

    What is the relationship between the transaction and the company or individual?


    If these are all customers, it seems to me that you have two types, company or individual. The individual who works for a company is a 'contact' and just another attribute of the company which might change if the person leaves and is replaced. I would lean toward putting them all in one table with an indicator for individual or company. I imagine most columns would be in common e.g. name, address, phone number, email etc. etc.
    Tuesday, February 23, 2016 12:51 AM
  • Thanks for the quick response.

    Transactions are financial.

    A customer can either be an individual or company so yes, both can be the same thing. Yes an individual who works for a company could be classified as a contact but a contact could also make a transaction as an individual as well so I would hate to store the same name in customers and a contacts table.

    I would almost want to have a CompanyNames table and a CustomerNames table linked to a Customers table that only holds a CustomerID, CompanyNameID and CustomerNameID field. Then just make sure that there is a value stored in CompanyNameID or CustomerNameID field (or sometimes both). This is where I am currently at anyway...


    • Edited by Dtours Tuesday, February 23, 2016 1:55 AM
    Tuesday, February 23, 2016 1:53 AM
  • Hi, Dtours<u1:p></u1:p>

    >>I would almost want to have a CompanyNames table and a CustomerNames table linked to a Customers table that only holds a CustomerID, CompanyNameID and CustomerNameID field. So on the base of your description you think like below.

    But I would like to suggest to design a table like below.

    So here you only have to make 2 tables instead of 3 like you are currently doing. The design of the table will be simple.

    On the base of your description I would like to give you simple suggestion that you make 1 table which contains the common properties of Company and individual. And make another table which contains the data about company and individual from which you make the difference between company or individual and create a relationship between 2 tables. if you work like this then your table will not contains empty fields.  For more information regarding your issue please provide detailed information about your issue.<u1:p></u1:p>

    >> Then just make sure that there is a value stored in CompanyNameID or CustomerNameID field (or sometimes both). This is where I am currently at anyway...

    If you work by suggested way then there is always value available for either company or individual.

    Regards<u1:p></u1:p>

    Deepak

    • Marked as answer by Dtours Thursday, February 25, 2016 5:59 AM
    Tuesday, February 23, 2016 7:23 AM
    Moderator
  • Thanks for the detailed reply. I have learned that whenever things are not sitting well in the design stage that there may be a problem with the client's process. So upon further investigation I clarified that an individual's name will always be entered for a transaction where sometimes an individual is acting on behalf of a company. This simplifies things considerably and will save be a lot of work.

    Here is an example of how important it is for a developer to fully understand a client's business processes to avoid doing a lot of unneeded work and backtracking.

    I will post a reworked table diagram later today to ensure it makes sense but it will probably look similar to what Deepak has posted.

    Wednesday, February 24, 2016 4:02 PM
  • Hi,Dtours

    if you find that solution was provided to you is helpful and can solve your issue then please click "Mark as Answered".

    Regards

    Deepak

    Thursday, February 25, 2016 5:39 AM
    Moderator