Posted by Erland Sommarskog on 10/15/07 21:40

theintrepidfox (theintrepidfox@hotmail.com) writes:
> The purpose of the link table:
> I have more entities than contacts which might have a phone number
> e.g. an organisation, a shed in a field with an alarm line (where no
> contact or organisation are associated), hence the link table for
> keeping the data of all entities in one table. e.g. a shed is linked
> through
>
> Fields Table LkTbl_Land_ContactMethod
> LkTblID
> LandID
> ContactMethodTypeID
>
> Is it wrong to tie multipe entities together like this? Apart from
> having only one contactmethodtable instead of having to deal with
> multiples, I thought it might make it easier looking up a phone number
> and to return the entity(ies) for the match. A contact and Shed might
> also share the same number which I didn't want to repeat in multiple
> tables.

I start here, because this is the easy part. In your response to Ed
you said two persons could share the same business phone. It's true that
with the model I suggested, you will get two rows for the same telephone
number. But when you think of it, this is likely to happen also with your
model. After all, some has to find that 90510 is already in the database.
And what's more, assume that Tom and Jerry have the same business number,
123445. But then Tom moves to a new job, so you update the number to
898881. But, wait, Jerry's number is still 123445.

So I would suggest that in this case, it's better to store the number
twice.

As for a contact and a shed sharing the same number, I guess the same
thing may apply there as well. Possibly you should like the contact with
shed in that case, as it sounds a bit specific relation.

Back to the question on whether to use a common table for the telephone
numbers to sheds, contacts and other entities. The question is not
entirely trivial, but I think there needs to be a considerable complexity
in the data structure to fold them into one table. And a single pair
of address type and phone number isn't that. But a full-blown address
might be, as it may require over 10 columns.

In the system I work with, with have a table Addresses, that has all
addresses in the system. Telephone numbers are in a subtable to that
table, similar to contactaddresses in my previous post, but with an
address id as the first key.

Several entities in the system can have addresses. A customer can have
more than one address, so there is a table Customeraddresses, that
sits in beweetn Customers and Addresses. An office can have exactly
one address, so there is simply an address id in the Offices tables.
I don't know about your sheds, but they sound like a single-address
entity to me.

One thing to note is that a shed is not likely to have a WEB or a SKYPE
address (I assume!) Maybe the address information for the shed is so
simple, so it can go into the Sheds table?

> 'I dropped the ID. Since this is a table where the data comes with
> the
> application, I think codes are better than ids, since you need to
> refer to them in your code.'
>
> Is there are significant performance slowdown in using codes than ids?

There is a cost, and if you're nervous, you can reduce it by using a
binary collation for the codes. However, I don't bother to that with
the codes in our system. We have plentyful of them.

Using the ids, on the other hand, causes a significant slowdown in
the development processes. EMAIL was that id 3 or 4?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]