×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

What is normal practice on splitting customer details

What is normal practice on splitting customer details

What is normal practice on splitting customer details

(OP)
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

RE: What is normal practice on splitting customer details

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.

RE: What is normal practice on splitting customer details

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.

CODE --> Example_Tables

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.

RE: What is normal practice on splitting customer details

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

CODE

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.

RE: What is normal practice on splitting customer details

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.

RE: What is normal practice on splitting customer details

(OP)
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

RE: What is normal practice on splitting customer details

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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close