Best tabel design

What would be the best table design for a simple customer ?                                    

Design1 (One table)            
tabel 1                                    
cus_Address 1
cus_Address 2                                    
cus_Address 3                                    

5001-Clyde-MyAddress1-MyAddress2- MyAddress3

Design2 (Two tables)      
tabel 1            

tabel 2            


Errol FarroAsked:
Who is Participating?
SkipFireConnect With a Mentor Commented:
I would go with the second one but extend it to have an Address type field so that you know if it is a billing, shipping, home address, etc.
Design1 is simpler, quicker etc, with the restriction that you have 3 and only 3 address fields per customer.
Design 2 is more complex, and slighlty slower, but has the flexibility that you can store any number of address fields per customer.
Ask yourself - do you need this flexibility? Will you have a system whereby customers will have variable numbers of address lines? or will they always be limited to a fixed number by the application layer anyway?

Typically for a 'simple' system, design1 is sufficient. Design2, or a modified version of it, is useful when you have named address fields, such as 'house nr', 'street nr', 'state', 'county', 'zip code' etc, and is particularly useful when you need to record addresses from different countries which typically have different structures - US addresses have state and ZIP fields, UK addresses have postcode, but no state  etc.

When you're searching for all customers in a certain state (for example),
the second design is better, because you can search table2 in the cus_state field.

In the first design you would have to search cus_state1, cus_state2 and cus_state3.

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Definately for a simple customer, design 1.
Dave FordSoftware Developer / Database AdministratorCommented:
Having done this for many, many years, I've found that I always regret it later when I go for the "simple, yet somewhat limited" aproach.  Since change their minds and add new requirements ALL THE TIME, I've found it best to design all my data models to be as flexible as possible.

With that in mind, I would DEFINTELY go for "Design 2". A little normalization now can save MANY headaches tomorrow.

i would definelty go with design 2 (im actually surprised there are such bold, yet diffrent views on this).

The second design is more normalized than the first.  A normalized design will help you avaoid erronious data in years to come.  

Some things that could go wrong with design one would be, if someone had more than three address.  Or is evreyone had only one address it would be a giant waste of space.  

You can query many more things from the second design, or i should say, you can query just as many things, but a lot easier!

Design two lends itself to change in the future.

I suggest you do a search on google "database normalization" and see what you come up with, there have been many people, smarter than i am to tell you why design two is better.

Good Luck!

Errol FarroAuthor Commented:
WOuld not the second one require more disk operation ?
The second design is more flexible.
It allows you to work with one (or group of) address column(s) for example when you find a client by an address or during global operations on addresses.
There is less impact when change a structure of an address record.
It allows you to change number of stored addresses.
It allows you to differ distinct types of the addresses such as permanent, contact... with a new field address_type
It allows to store history of addresses with a new fields valid_from , valid_to.

The first design has just one advance - you have to operate just one table - it would be denormalized solution.

Is the miniscule amount of disk I/O going to be a problem?
A performance makes an usuall reason for denormalization.
But in the case, if not all customers have 3 addresses it could be significant.
All Courses

From novice to tech pro — start learning today.