What is normal practice on splitting customer details

[G12Consult]

Hi,

I am curious to know people's thoughts on whether you would split customer information into separate tables all combine in one.

So a table for each of the following:

Personal Details - Name, DOB, Gender....
Contact Details - Postal Address, Residential Address, Phone
Emergency Contact Details - Next of Kin

What is standard practice? Is one way slower than the other way?

I understand if you wanted to combine all the info you would need to join through a query. Is this any slower than querying one table?


Thanks
Andrew

 

[Andrzejek]

My rule of thumb is: have data/information in one and only one place in the DB.
So, if Joe Sixpack has 3 people to contact as Emergency Contacts, you do not want to list Joe Sixpack’s name 3 times for 3 records in Emergency Contacts table.

As far as Personal Details and Contact Details tables, I would have just one table. This information will be one record per person anyways, right? Unless in your world - Name, DOB, Gender information will relate to (possibly) many Postal Address(es), Residential Address(es), Phone(s), then I would have separate tables.



---- Andy

There is a great need for a sarcasm font.

 

[SamBones]

It's funny you should use the word "normal" in the subject line of the post. The process your are asking about is called Data or Database Normalization.

Take a look at this link for a good simple overview of relation data modeling and normalization:

https://www.guru99.com/database-normalization.html

There's also a Wikipedia page that goes into much more detail, but it may get too deep into the weeds if you're just starting out.

To simplify it, any data that is repeated a variable number of times, should be in a separate table. That is, people can have zero to many ways to contact then, or emergency contacts for them.

Table: PEOPLE

ID_NO    FNAME   LNAME    DOB           GENDER    SHOE_SIZE    EYE_COLOR
  1      Joe     Smith    01/01/1990    M         9            Blue
  2      Jane    Smith    02/02/1992    F         6            Green
  3      Billy   Smith    03/03/2012    M         5            Brown

Table: CONTACT_DETAILS

PEOPLE_ID_NO    TYPE      DETAILS
   1            MOBILE    123-456-7890
   1            WORK_PH   123-123-4567
   1            HOME_PH   123-234-5678
   1            EMAIL     jsmith@gmail.com
   1            WK_EMAIL  joe.smith@initech.com
   2            EMAIL     jsmith2@gmail.com

Table: EMERGENCY_CONTACTS

PEOPLE_ID_NO    CONTACT_ID_NO    RELATIONSHIP
   1               2             WIFE
   2               1             HUSBAND
   3               1             FATHER
   3               2             MOTHER

This allows you to give a person in your database as many ways to contact them, or as many emergency contacts as they want or need. And getting the data back out in a useful way is just a matter of some fairly simple SQL select statements.

 

[Andrzejek]

You may go a step further what SamBones suggests and do (according to my rule):

Table: CONTACT_DETAILS

PEOPLE_ID_NO    TYPE_ID   DETAILS
   1            1         123-456-7890
   1            2         123-123-4567
   1            3         123-234-5678
   1            4         jsmith@gmail.com
   1            2         joe.smith@initech.com
   2            4         jsmith2@gmail.com

Table: CONATACT_TYPES

TYPE_ID     WHAT
1          MOBILE
2          WORK PHONE 
3          HOME PHONE
4          E-MAIL
5          SMOKE SIGNALS

Do you see how nice the Contact Types be accessed to populate a simple combo box with the options to select from?


---- Andy

There is a great need for a sarcasm font.

 

[SamBones]

I agree with Andy 100%.

BUT, it also depends on why you are modeling this information, or what it will be used for.

If you are just trying to track your own family and friends as contacts, then you don't really need that complexity. Just drop it all into a single Excel sheet.

If you are trying to track customers of a business, small or large, then YES, by all means properly design the tables this way. This will allow the database to grow as big as it needs to be, while minimizing storage and processing to use it. You'll need to learn more about data modeling and access, but it will be well worth it in the long run.

 

[G12Consult]

Thanks for the responses guys.

Its a mini erp I am setting up so the client will only have 1 postal address and 1 residential address on record.

As for the emergency contacts, there may be many per client so I will seperate this as advised.

Thanks again

 

[johnherman]

Normalization was the rule during the 1980s and into the 1990s. Then De-normalization and dimensional modeling came into popularity with the advent of multidimensional databases such as Teradata, and cube technology like PowerPlay and Microsoft OLAP. Denormalization would say to combine all the popular customer data into one table so that it can be accessed with a single read. Less popular data could be normalized (contracts, addresses, for instance) or set up as a subrecord to the master customer record.

In a nutshell....it depends on what you are doing with the data structures.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity