A quicker,cleaner way to code ?

swatisonee

Hi,

I have a table called Adbk which lists complete info of customers - names, cities, streets, tel nos. etc.

Now several companies have multiple personnel sitting in the same location - so I have company say Example Inc. that has 10 entries with the same street and city address but different names and cell numbers (example john doe ,123-456-789 , jack smith 987-654-321, jane doe , 456-789-123 etc). Sometime john doe will appear twice or thrice if he also operates from the offices of Example Inc in other cities

So each time i have the table is called, it has to search thru so much of data.

I was wondering if there was another way i could do this ? Would it be worthwhile splitting up the data into 2 tables - the addresses in one and the personnel names and numbers in another ?

What would be the right way of splitting it ?

This question came to be because of another post i made earlier on updating strings in multiple tables that Bodzan has been kind enough to guide me thru.

Any and all suggestions appreciated.

Thanks.

laserlight

There's an ancient (but nonetheless useful and valid) PHPBuilder article on Database Normalization And Design Techniques.

bodzan

I would suggest the following structure:

Table 1:
id | CompanyName

Table 2:
id | OfficeName | companyID

Table 3:
id | Address | City | State | Country | telephone | officeID

Table 4:
id | name | lastname | cellPhone | etc

Table 5:
contactID | locationID

Now the idea is following:
1. You have a companies that can change name for whatever reason and they are listed in Table 1.
2. Each company from table 1 have offices all over the country or world and they are all listed in Table 2 and are connected to the company it's ID. Those can change be deleted aded or whatever but the Company will remain the same.
3. Every office can have more than one place of origin (different city, state, country etc) and thus in Table 3 all of those places are listed. Those are connected through office via it's ID. Locations are also free to change whatever they want and it will not affect an office.
4. You have contacts and all of their personal data in Table 4.
5. Here is my personal favorite way of dealing with this kind of situations: many-to-many database table: It connects every contact to an location so for every location a contact will have different address or telephone number. So contact ID is connected to location ID of the Table 3.

Hope this helps...

Ahh, laserlight I know I have read it somewhere!!! Thanx a million...

swatisonee

Thanks a ton , both of you ! I've started reading the article.

I also have 2 other issues to resolve before i start the splitting :

a. Example Inc and XYZ Systems Inc both share the same office - they are 2 subsidiaries of ABC Inc. So their locations, addresses, tel nos. etc are all the same. They have some personnel in common and some unique. In this instance how would I be able to link all 3 so that I know they all are part of the same group but at the same time different business entities ?

b. There are also some people who are independent techs. but are also on a retainer for say ,Example Inc. Now they have no "company" of their own but I have 2 sets of contact numbers for them - one their own and one thru Example.

Would you suggest I create a "null" company or something else ?

Thanks again !

bodzan

...I would create a contact which has no ties whatsoever to no known office nor company nor location with locationID set to 0 for example... That way you would know that the person is a freelancer...
Also you would be able to set this person connected to whatever location through the same table by setting locationID to whatever location he is tied with...
That way he will be connected to the both freelancer status and the location he is employed at the moment... This is the beauty of the many-to-many type of relationship table... 🙂

As for problem #1:
This requires a bit more complicated approach - you'll need another many-to-many table that will connect location to office... That way you would lose the officeID key from table 3 and create a new table where only locationID and officeID reside simillar to table 5:

Table 3
id | Address | City | State | Country | telephone

Table 6
locationID | officeID

So every location can be connected to unlimited amount of offices...

This goes for every relationship that needs this kind of freedom...

swatisonee

Hi,

That does seem a bit complicated but not difficult ..will now start noting down how i must structure the tables.

In the meantime, could you guide me on the best way to split this table into 5 /6?

Thanks.

bodzan

I mean, I gave you a basic draft of the tables both 5 and 6... What do you need splitting?

swatisonee

Hi,

I meant the existing table "Adbk" . How do i move that existing data of addresses etc to the 6 that i create ? A simple copy table wont do because all 6 will have different structures .

Ummm as i write this i realise i need to read back - i'm missing something....