Access customer, name and phone tables

[Majdi Musafir]

I have two tasbles. One called suctomer and has custID, and custname. The other table is cusphone. I need only one realtionship between the two tables. I need to search for an id number, and a phone number. how could join these two tables without having the custID get duplicated? thank you

[Stewart Ross]

Hi. You need to reconsider your table design, as you have incorrectly separated out components of the customer table such as the customer phone into separate tables. These are simply attributes of the customer entity, and should remain as part of your customer table.

There is no need at all to add the complexity of maintaining separate customer-attribute tables joined by customer ID when a single table is all that is required.

When you come across 1-to-1 relationships such as those involved in your separated customer / customer phone tables, it is normal to combine these into the one table as long as all fields of the sub-tables are dependent on nothing other than the key of the master table - customer ID in your case.

I recognise that customers can have more than one phone number. This is dealt with by recording as separate attributes a landline number, mobile number, and fax number. If customers may be business clients you may also wish to record the business phone number in addition to their landline (home) phone number.

We have a helpful article on database normalisation and table structures which you may find useful.

-Stewart

[code green]

I agree with Stewart, as this is standard industry practice.
The only justification for a seperate telno table would be to monitor calls made, along with a timestamp, id and maybe reason.

[Stewart Ross]

Further to my reply above, if you wish to keep your current two-table approach - which I do not recommend - then the customer ID must be a component of the customer phone table. You cannot avoid this as there would be no other way to identify which customer's phone number belonged to which customer.

If I had to implement a search on your two tables I'd simply join them on the customer ID field in a SELECT query and search the join results. But, as mentioned in my previous post, I think your current two-table design unnecessarily complicates things for you and I would strongly urge you to reconsider your approach.

-Stewart

[OldBirdman]

Is the data normalized with the one table approach? It seems to me that it is not! With:

Land Phone
Cell Phone
Fax
Land Office Phone
Cell Office Phone
Office Fax
Office Secretary (Leave message)
Pager (field left from when such existed)
Land SummerCabin Phone
Land Temp Phone while on assignment out-of-town

All queries on the phone number become quite awkward. There is no provision for adding numbers with new technology, and old technology leaves fields behind.
I agree with Stewart's Post #4 paragraph 1, there must be a field to join the two tables. Stewart references an insight on Normalization, and for First Normal Form (1NF) there is a statement "Do not use multiple fields in a single table to store similar data ". I believe that this applies here, and one table is not the way to go.
OldBirdman

[code green]

That is quite a good point OldBirdman.
But in keeping with this line of thought this could also be extrapolated to addresses.
invoice address, delivery address, registered business address, home address, log cabin address.
Most databases I work with do have all telephone/fax numbers in one table normally a customer table, sometimes delivery addresses in a seperate table to invoice address.
From experience there is not more than say two telephone entries per customer , the rest being empty (NULL).
I am quite thankful for this as queries extracting customer information are kept short, as opposed to multiple JOINs to tables named Land_Phone,Cell _Phone,Fax,Land _Office etc

One table may not be the way to go but it is convenient