|
|
Posted by Erland Sommarskog on 10/14/07 22:27
theintrepidfox (theintrepidfox@hotmail.com) writes:
> 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.
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@partaj.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@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]
|