Hi all
I am in the process of designing a database structure for a very common use: to store client data such as names, addresses, company information, etc.
I have been reading up a lot efficient database design and am trying to normalise to 3NF.
As I am new to this I imagine these questions might seem quite trivial so I apologise in advance.
I will start off by listing my table structure so far. I have not included everything as I believe if I can understand one method I can apply it too all my tables. My questions are below the table structure.
client table - This would list each client name and then link to the relevant tables
------------
clientID (PK)
lastName
firstName
addressID
emailID
phoneID
companyID
company table - Used to store the company name and link to address table
-----------------
companyID (PK)
companyName
addressID
address table - Used to store addresses and link to city and country tables
---------------
addressID (PK)
typeID
street
postcode
cityID
countryID
addressType table - Used to specify if the address is home, second home or business
---------------------
typeID (PK)
type
city table
----------
cityID (PK)
city
country table
---------------
countryID (PK)
country
email table - used to store personal, business and other email addresses
------------
emailID (PK)
perEmail
busEmail
addEmail
phone table - used to store personal, business, mobile, fax, numbers
-------------
phoneID (PK)
homeTel
homeFax
busTel
busFax
perMobile
busMobile
So my questions are:
1) Am I on the right track for the design to meet 3NF normalization?
2) I can see that there will be a lot of fields that will be left blank. For instance the client might not have a business email or a business phone or be linked to a company. Is there a better way of saving on wasted space?
3) If I have a client that has multiple addresses, would this mean that the address table would need to have a column for linking to the client? I am slightly confused as to how one to many relationship works, I've read a few explanations but I can't seem to grasp it. Could someone try and explain in simple terms how this works?
Hope you can help.
Thanks in advance
Peter