Maybe you can help me folks:
I have 3 tables (for now) which (may) have 0,N phone numbers.
* Agent (AgentID, AgentType, etc.);
* Facility (FacilityID, FacilityName, facilityType, etc.) and
* Concact (ContactID, FacilityID, ContactName, etc.).
The phone table is like this:
* Telephone (PhoneID, PhoneCountryCode, PhoneRegionCode, PhoneNumber, InternalExtension)
According to the normal rules, since the relationship is 0..N for these 3 tables their codes must migrate to the phone table and a check constraint should be created so as to allow only one code (AgentID, FacilityID or ContactID) to be filled at a time in the phone table.
This sounds fair, but more entities (other than those) would demand a phone, what would make me add more columns and FKs to the phone table (and modifications in the check constraint). On the other hand, there might be entities/tables with a single phone, in which a reference (to add a PhoneID column and to create a FK) would be enough.
A solution might be "bending" normalization and turn those 0..N relationships into N..N ones, creating the correlated tables, what I think wouldnt be so nice concerning performance, and I would have to create another table to every entity which has more than 1 phone.
Another solution would the the creation of a single table, registering the code of the phone's owner, the phone code and a column like "PhoneOwnerType" ('A' for Agent, 'F' for facility, etc.), which both "OwnerCode", "PhoneOwnerType" and PhoneCode as PK. This is not so clean (kinda denormalization), and would demand a trigger to check if the owner's code is in the proper parent table, according to "PhoneOwnerType".
What do you suggest in this case? What would be better concerning performance and maintenance?
dBforums
>
Help in table implementation!
12-04-06, 08:42
Help in table implementation!
Linear Mode
