?
Solved

Best tabel design

Posted on 2005-03-10
10
Medium Priority
?
943 Views
Last Modified: 2013-12-03
What would be the best table design for a simple customer ?                                    

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


example
5001-Clyde-MyAddress1-MyAddress2- MyAddress3


Design2 (Two tables)      
      
tabel 1            
cus_Number            
cus_Name                        

tabel 2            
cus_Number      
cus_Sequence            
cus_Address      

example
5001-Clyde

5001-10-Myaddress1
5001-20-Myaddress2
5001-30-Myaddress3
0
Comment
Question by:Errol Farro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 17

Expert Comment

by:BillAn1
ID: 13508592
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.
0
 
LVL 6

Expert Comment

by:rvooijs
ID: 13509065
Hi,

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.

Robert
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13509093
Definately for a simple customer, design 1.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Dave Ford
ID: 13509782
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.

HTH,
DaveSlash
0
 
LVL 7

Expert Comment

by:Navicerts
ID: 13510965
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!

-Navicerts
0
 
LVL 4

Accepted Solution

by:
SkipFire earned 375 total points
ID: 13512740
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.
0
 

Author Comment

by:Errol Farro
ID: 13516257
WOuld not the second one require more disk operation ?
0
 
LVL 6

Expert Comment

by:Jankovsky
ID: 13516317
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.

Bob  
 
0
 
LVL 4

Expert Comment

by:SkipFire
ID: 13516341
Is the miniscule amount of disk I/O going to be a problem?
0
 
LVL 6

Expert Comment

by:Jankovsky
ID: 13516401
A performance makes an usuall reason for denormalization.
But in the case, if not all customers have 3 addresses it could be significant.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question