Dear Group

I'd be grateful if you can provide me with a hint for the following:

Fields Table Contact
ContactID
Firstname
Lastname

Fields Table ContactMethod
ContactMethodID
ContactMethodTxt
ContactMethodTypeID

Linked by Tables:

Fields Table LkTbl_Contact_ContactMethod
LkTblID
ContactID
ContactMethodTypeID

Fields Table ContactMethodType
ContactMethodTypeID
ContactMethod

The purpose of this construct is that I keep all contactmethods such
as Email, Mobile, Home Work, Web in table ContactMethods. Whether it's
e.g. an Email or Mobile is identified through the ContactMethodType.
That enables me that a Contact can have 3 Mobile Numbers, 2 Email
addresses.

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

I must note that the contents of table ContactMethodType for column
Contactmethod looks like this:
Email
Mobile
Home
Work
Web

I do:

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

The question is, how do I map the result from table ContactmethodType
to my fields Home, Work and Mobile in the resultset? The Type is
identified in ContactMethodType but I can't figure out how to do this
best.

Your help and suggestions or any other better solution is very
appreciated.

Thanks in advance for your help and efforts,

Martin

theintrepidfox (theintrepidfox (AT) hotmail (DOT) com) writes:
I was looking at this, but I could not really grasp how the table was
supposed to work. In any case, the design does not look right to me.
It seems to me that you should have one table with the contacts, and
then one with their addresses. I'm not sure that I see the purpose of
the link table.

Below is a script with my suggestion for table design, and also a query
for you at the end. I retained the address-types table, although it
could be replaced by a CHECK constraint. The nice thing with a table, is
that it's easy to add an other value, without altering the schema. But
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.

If your table are cast in stone, please post a script similar to mine
with sample data, so we can understand how your tables work.



CREATE TABLE contacts
(contactid int NOT NULL,
firstname nvarchar(50) NOT NULL,
lastname nvarchar(50) NOT NULL,
CONSTRAINT pk_contact PRIMARY KEY (contactid))
go
CREATE TABLE addresstypes
(adrtype varchar(7) NOT NULL,
CONSTRAINT pk_adrtype PRIMARY KEY (adrtype))
go
CREATE TABLE contactaddresses (
contactid int NOT NULL,
adrno smallint NOT NULL,
adrtype varchar(7) NOT NULL,
address nvarchar(50) NOT NULL,
CONSTRAINT pk_contaddress PRIMARY KEY (contactid, address),
CONSTRAINT fk_contaddress_contact FOREIGN KEY(contactid)
REFERENCES contacts(contactid),
CONSTRAINT rk_contaddress_adrtype FOREIGN KEY (adrtype)
REFERENCES addresstypes (adrtype)
)
go
INSERT addresstypes (adrtype)
VALUES ('EMAIL');
INSERT addresstypes (adrtype)
VALUES ('WEB');
INSERT addresstypes (adrtype)
VALUES ('HOME');
INSERT addresstypes (adrtype)
VALUES ('WORK');
INSERT addresstypes (adrtype)
VALUES ('MOBILE');
go
INSERT contacts(contactid, firstname, lastname)
VALUES (1, 'Frank', 'Zappa');
INSERT contacts(contactid, firstname, lastname)
VALUES (2, 'Fröken', 'Ur');
INSERT contacts(contactid, firstname, lastname)
VALUES (3, 'Joe', 'Cool');
INSERT contacts(contactid, firstname, lastname)
VALUES (4, 'Gretchen', 'Phillips');
INSERT contacts(contactid, firstname, lastname)
VALUES (5, 'Nisse', 'Hult');
go
INSERT contactaddresses (contactid, adrno, adrtype, address)
VALUES (2, 1, 'WORK', '90510')
INSERT contactaddresses (contactid, adrno, adrtype, address)
VALUES (3, 1, 'EMAIL', 'joe.cool@kårhuset.se')
INSERT contactaddresses (contactid, adrno, adrtype, address)
VALUES (3, 2, 'WORK', '+46-46-122753')
INSERT contactaddresses (contactid, adrno, adrtype, address)
VALUES (3, 3, 'WORK', '+46-46-122754')
INSERT contactaddresses (contactid, adrno, adrtype, address)
VALUES (4, 1, 'WEB', 'http://www.two-nice-girls.com/')
INSERT contactaddresses (contactid, adrno, adrtype, address)
VALUES (4, 2, 'HOME', '+1-555-1234567')
INSERT contactaddresses (contactid, adrno, adrtype, address)
VALUES (4, 3, 'WORK', '+1-555-7894561')
INSERT contactaddresses (contactid, adrno, adrtype, address)
VALUES (5, 1, 'HOME', '+46-40-70841')
INSERT contactaddresses (contactid, adrno, adrtype, address)
VALUES (5, 2, 'EMAIL', 'nisse.hult (AT) partaj (DOT) se')
go
SELECT c.firstname, c.lastname,
home = MIN(CASE ca.adrtype WHEN 'HOME' THEN ca.address END),
work = MIN(CASE ca.adrtype WHEN 'WORK' THEN ca.address END),
email = MIN(CASE ca.adrtype WHEN 'EMAIL' THEN ca.address END)
FROM contacts c
LEFT JOIN contactaddresses ca ON c.contactid = ca.contactid
GROUP BY c.firstname, c.lastname
go
DROP TABLE contactaddresses, addresstypes, contacts



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 14 Oct, 23:27, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks Erland, I know I can count on you :-)

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.

Your sample is great and logical. The only question I have when you
said:

'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?

I can't provide you with with the script right now but can later.

Thanks for your help and efforts. Greetings to Sweden,

Martin

Is Contact <-> ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts? If not,
then I agree with Erland that ContactMethod should be merged with
LkTbl_Contact_ContactMethod for simplicity (unless you have a pile of
code already built on top of the existing design, in which case you
should at least create a view that combines their data).

theintrepidfox wrote:

"First" in what sense? Lowest ContactMethodID value among the
candidate rows?

create view v_FirstContactMethodIDs as
select lcm.ContactID,
cm.ContactMethodTypeID,
min(cm.ContactMethodID) FirstContactMethodID
from LkTbl_Contact_ContactMethod lcm
join ContactMethod cm
on lcm.ContactMethodID = cm.ContactMethodID
group by lcm.ContactID, cm.ContactMethodTypeID
go

select c.FirstName,
c.LastName,
cm_home.FirstContactMethodTxt Home,
cm_work.FirstContactMethodTxt Work,
cm_mobile.FirstContactMethodTxt Mobile
from Contact c
left join v_FirstContactMethodIDs v_fci_home
on c.ContactID = v_fci_home.ContactID
and v_fci_home.ContactMethodTypeID = 'Home'
left join ContactMethod cm_home
on v_fci_home.ContactMethodID = cm_home.ContactMethodID
left join v_FirstContactMethodIDs v_fci_work
on c.ContactID = v_fci_work.ContactID
and v_fci_work.ContactMethodTypeID = 'Work'
left join ContactMethod cm_work
on v_fci_work.ContactMethodID = cm_work.ContactMethodID
left join v_FirstContactMethodIDs v_fci_mobile
on c.ContactID = v_fci_mobile.ContactID
and v_fci_mobile.ContactMethodTypeID = 'Mobile'
left join ContactMethod cm_mobile
on v_fci_mobile.ContactMethodID = cm_mobile.ContactMethodID

On 15 Oct, 05:52, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:
Hi Ed

Thanks for your message.

'Is Contact <-> ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts?'

Yes, Contact A and Contact B both might share the same BusinessPhone
(ContactMethodType) in which case there's a single row in table
ContactMethod that refers to both.

I got the script almost working based on Erlands sample. The only
issue is that it returns me value Phone of row 1 ('777 123') in table
Contactmethod instead row 6 ('435 675') which would be the correct
one. Here's what I got, it's probably totally wrong. Please excuse my
SQL ignorance.

SELECT i2b_jajah.JajahID, UseJajah, JajahUsername,
MIN(ISNULL(NULLIF(Firstname,'') + CHAR(32),'') +
ISNULL(NULLIF(Middlename,'') + CHAR(32),'') + ISNULL(Lastname,'')) AS
RealUsername,
Phone = Min(CASE i2b_systbl_contactmethodtype.ContactMethodTypeID
WHEN 1 THEN i2b_contactmethod.ContactMethodText END)
FROM i2b_jajah
LEFT JOIN i2b_lktbl_contact_jajah ON (i2b_lktbl_contact_jajah.JajahID
= i2b_jajah.JajahID)
JOIN i2b_contact ON (i2b_contact.ContactID =
i2b_lktbl_contact_jajah.ContactID)
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)
JOIN i2b_contactmethod ON (i2b_contactmethod.ContactMethodID =
i2b_lktbl_contact_contactmethod.ContactMethodID)
JOIN i2b_systbl_contactmethodtype ON
(i2b_systbl_contactmethodtype.ContactMethodTypeID =
i2b_contactmethod.ContactMethodTypeID)
GROUP BY i2b_jajah.JajahID, UseJajah, JajahUsername

Result:
JajahID UseJajah JajahUsername
RealUsername Phone
---------- ------------
---------------------
--------------------- ---------
1 0 JSmith007
John Smith 777 123


Thank you for your help and efforts,

Martin

On 15 Oct, 07:29, theintrepidfox <theintrepid... (AT) hotmail (DOT) com> wrote:
Got It working:

LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactID = i2b_contact.ContactID)
Instead of
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)

Still, if you have any suggestions for improvement, please let me
know.

Thanks again for your great help and time.

All the best,

Martin

theintrepidfox (theintrepidfox (AT) hotmail (DOT) com) writes:
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?

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx