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

You might be on the right track for 3NF but I suggest you take a closer look at your overall design.

You have a client table, but it is mostly populated with IDs for other client-related data. Why not just put all the client data into the client table? Your design demands you now create several bridge tables to tie phone numbers, addresses, etc. to a particular client.

You already have the companies in a separate table, but again, you use IDs to tie information about the company address. You are creating more work for yourself, as again you will have to create and use a bridge table to get meaningful information back about a company.

Why do you have address, email, city, country, phone information in separate tables? Think about what a query would look like if you wanted all the contact information about a particular client or company. You would have to use about 6 tables just to get that.

What you are aiming for is to have all information about a particular thing or event in a table. So you would have all data pertaining to a client in a client table (name, address, phone, email, ID, etc.). Follow this thinking and you will be on your way.

Thank you for your reply.

Your method was how I initially designed the database. i.e. all details pertaining to a client in one table. However that does not meet 2NF or 3NF normalization as you would have a lot of data repeated. It would also mean that insertion, deletion and updating anomolies would occur.

Also I will have multiple clients with the same name in the clients field, which is data repetition. For example I have a client who has multiple family members, each of them are a seperate client. They all live at the same address, so if they moved, I would have to change the address details in every single row, whereas with the linked structure, I would only need to change the relevant address once.

Can anyone please shed some light on this?

Thank you

You didn't mention this initially. I take it these are members of the same family, sharing a last name and address? In that case, then you could break out the address into a separate table.