Database Design Help and Suggestions

[pgulbis]

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

[trudge]

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.

[pgulbis]

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

[trudge]

I will have multiple clients with the same name in the clients field, which is data repetition.

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.

[saasaa1]

plez explain the file processing system vs database management system?