SQL Server Performance Forum – Threads Archive

Best table implementation

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". In short, it’s not easy (for me <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> ). What do you suggest in this case? What would be better concerning performance and maintenance?

Nobody? Well, thanx anyway guys.

Sorry, didn’t spend much time on the boards lately. Also, "performance tuning" is the wrong section for this type of question – although to your defence, there is no proper "database design" section. This usually ends up among the "general" sections. But anyway … The "telephone" entity you are describing is not independent: it can only exist in relation to a parent entity. It looks like you have contact addresses in different categories (Agent, Facility, …). Some of these contact addresses have contact persons: if they do, then the telephone number relates to a contact person, if they don’t then it relates to the contact address. I think you need to normalize into one OtherParty table, with an OtherPartyType column that distinguishes between Agent, Facility, etc. You then add a child table for OtherPartyContact with OtherPartyId as FK. If a single contact has only a single telephone detail, then add one column on OtherPartyContact. If a single contact can have 0 to n telephone details, then add another child table OtherPartyContactTelephone, with OtherPartyContactId as FK.

Adriaan, thanx for the reply. Actually I didnt know where to post this, and hence a part of the problem is related to performance, I’ve decided to write it down here. If there is a way to move it to the general section, just let me know! Indeed, the "telephone" is a weak entity. And "agent" can have N phones (0:N), as well as "facility" (0:N), but a "contact" may have only one phone (0:1). So, either way, according to the normal rules, I should migrate agent, facility and contact keys into the telephone table. If I do it your way, using OtherPartyId (PK) and OtherPartyType in "OtherParty" table, I would still need to know whose "party" is this. So, for every entity that needs a phone, there would be an ID in the "OtherParty" table, or do you mean to create an "OwnerId", which would be filled with either AgentId, FacilityId or ContactId? This way I would need a trigger to check the field’s content in the parent table, wouldnt I?

No, you need to use a three-table structure, where the telephone is the lowest one. If an entity seems to exist only on the first level, then make sure that a dummy entry is entered on the second table, and vice versa.

Thanx a lot Adriaan!

If you are still interested to hear the opinions on your question, here the approach I use. Essentially, you can have just two types of "phone-owning" (and address-owning!) entities: a person/contact or a company. So I usually create two "basic" tables – Contact (it would have Name, Title, DOB, etc.) and Company. They would connect to Phone (and address) tables using ContactPhone and CompanyPhone tables. Just two link tables, no more. These link tables would also have PhoneTypeID attribute to distinguish between business phones, home phones, faxes, etc. Now, how to connect new tables to your existing structure:
* Agent (AgentID, AgentType, etc.)
* Facility (FacilityID, FacilityName, facilityType, etc.) and
* Contact (ContactID, FacilityID, ContactName, etc.). 1. Your Agent table represents a Contact with certain additional attributes – it should get a ContactID column to link it with "basic" personal information. Same would happen with similar "specialized" tables, like Driver, Lawyer, Inspector. 2. From the few attributes you listed it looks like your Facility table represents a generic company, and then – it’s just my Company table with another name. If, on the other hand, Facility table has some specialized meaning and carries specialized attributes – add CompanyID to it to link it with "basic" Company table. 3. In order to link a Contact to a Facility (or maybe several facilities, as it often happens), I would create additional table ContactFacility (or ContactCompany).
What do you think about it? -vitaly

Hey vitaly, Sorry about the delay. Since the post is old – and had no replies – I’ve stopped checking it. Actually, you are right: 1 – Agent is (also) something like your company (ex.: Chell, Ezzon, etc. <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> ) 2 – Facility is the "working place" of your company (ex.: each oil refinery belonging to Ezzon) I’ve used something similar to your linked tables solution, but the relationship tween contact and facility, which is ternary: a company may have a general contact (for all of its facilities), or a specific contact for each facility, demanding an entity like Representative(#RepresentativeID, AgentID, FacilityID (may be null), ContactID). And this is the entity linked to the Phone, thru a linked table (RepresentativePhone). Thanx for your reply!

]]>