Contacts Database

[Gbowe]

Hi,

I need to build a contacts database, which will have the usual entries, name, addess, phone etc.
It will also need to include different company/institute information, department, research fields, technologies, experience etc.
I will have multiple contacts from within the same company/institute and probably multiple contacts within various departments.

Should I divide the information into separate tables (contacts and address book), or should I keep all the information in one table?

Is there any standard way for dealing with different address formats (it will contain entries from many countries)?

thanks
ger

[mark_W]

Hiya Ger,

I would be inclined to split it into the two tables. You can always use JOIN on them if you need to at a later date.

Mark

[Busch]

Is there any standard way for dealing with different address formats (it will contain entries from many countries)?

I feel i should input on this because this problem annoys me. Currently i am living in Korea but occasionally i have to fill out online address forms. Because I am from the states those usually include "State" and no appropriate place for an international phone number. In the past I have actually stopped filling out a form because it wouldn't let me past the "State" field without lying. (Korea doesn't have states so I would have to leave it blank).

I recommend breaking your address form into fewer categories such as:
Name: Most people at least have these
Address: Since most people know how to type their address and know the order the address is supposed to appear in their country, let them do it. This textarea should be large to let them include a full address, except for their country and possibly the city, though it depends on what you are using the information for.
Country: A drop down of choices.
Tel: I am not sure what numbers look like in other countries but this is a valid phone number for korea with international code 82 (042) 1234-5678

I hope this helps you help your international relations.

[elgumbo]

You probably should have posted this message in the MySQL forum and not the PHP one (http://www.sitepoint.com/forums/forumdisplay.php?f=182) but in the absence of r937 I'll try to help

The basic rule of database Normalization is that you should not duplicate information in the database (unless there is a very good reason to).

At first glance you could put everything in the one table, but just imagine how many times the same companies and departments will appear. You need to work out the relationship of the data. eg Will a person only work for one Company? Will they only ever be under one Department or could they job share? Answering these type of questions at the beginning will make it a lot easier in the end.

Off the top of my head, I would want a Table for People (holding personal info), a table for Departments (holding, er, departmental information), a lookup table for the People / Department (to tie the 2 together - in case somebody works for more than one), and a Table for the Company (holding all the Company info). You could also have a table for Countries (for addresses) and a table for Research (again with a lookup).

There is a document on Microsoft that covers the theory behind normalization - http://msdn.microsoft.com/library/de...malization.asp or http://search.microsoft.com/search/r...ion+access&s=1 t's for Access but the thought process you will need to go through is the same regardless of database used.

With regards to Address fields, I would think as long as you allow certain fields to be null (like state / county / postcode) you should be fine.