Dhrubo
Posts: 32
Nickname: dhrubo
Registered: Mar, 2003
|
|
Re: Database normalization
|
Posted: Sep 21, 2005 6:05 AM
|
|
Hi Dallas,
This is not a correct DB design. Your design is a denormalised one and is very cramped.
Ideally there should be two tables
1> TBL_Client
2> TBL_Client_Contact
TBL_Client
----------
Client_id NUMBER(20) Primary Key
First_Name: VARCHAR (20) Not Null
Last_Name: VARCHAR (20)Not Null
Business_Name: VARCHAR (60)Not Null
TBL_Client_Contact
------------------
Client_id NUMBER(20) Primary Key
Contact_id NUMBER(20) Primary Key
Email: VARCHAR (60)
Address_1: VARCHAR (60)
Address_2: VARCHAR (40)
City: VARCHAR (40)
State: VARCHAR (2)
Zip: SMALLINT
Phone: VARCHAR (20)
This table has a composite primary key. I have kept the client_id column as the first column of the PK, as most searches in this table will happen based on client_id, so this will ensure a index range scan in selects.
Please note e-mails can be used as PK, but its ideal to have id or number columns as the PK as number comparison is more efficient than string comparison.
This probably shows you how to normalise your db design.
But be careful too much normalisation may cause performance bottlenects, so you need to be careful and make intelligent trade-off and denormalisation
|
|
Previous Topic
