SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Implementing Table Interfaces
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/19/2008 :  08:14:30  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

Last time, we discussed Table inheritance, which allowed us to easily reduce redundancies in our table design by creating "base" or "super" tables that contain columns and relations that "sub-tables" automatically inherit.


That generally works well, but what if you just want to have several entities share a relation, but no common attributes? That is, the entities are not really the same type, and a base class wouldn't make a lot of sense. For example, suppose you are modeling Employees and Offices, and both entities can have multiple phone numbers that you'd like to store. Is there a simple way to create a data model for that without the need for redundant tables and code?




Read Implementing Table Interfaces

abeaupre
Starting Member

United Kingdom
2 Posts

Posted - 05/19/2008 :  11:41:59  Show Profile  Reply with Quote
I've seen in other entity database models (Siebel) a different approach. Rather than having separate base objects for Employee or Company, you have a single base entity table of which Employee and Company are both children of. This will create a unique identifier across all types. Using that technique, your phone or address tables could then relate to this base table with a single relationship. That would remove a lot of the complexity involved - which would likely increase as you added other base types.

Andrew

Edited by - abeaupre on 05/19/2008 11:43:30
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/19/2008 :  11:49:45  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Andrew -- that technique was covered in the "Table Inheritance" article referenced in the intro.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

abeaupre
Starting Member

United Kingdom
2 Posts

Posted - 05/19/2008 :  11:58:42  Show Profile  Reply with Quote
I read that article (which was quite good). While it covered a similar implementation for similar data types, it doesn't solve the "interface problem" for dissimilar types.

I, personally, don't like the idea of supporting multiple parent reference types. It doesn't scale well. If you could guarantee a unique ID across all objects, then you wouldn't have to worry about deciding which object type an address was related to.


Andrew
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
273 Posts

Posted - 05/22/2008 :  08:34:01  Show Profile  Visit henrikop's Homepage  Reply with Quote
Hey Jeff,

I'm glad you write these kind of articles. SQL is nothing, concept everything. (with that I mean that if the concept is clear, the T-SQL is relative easy).

Genericity is the word that comes up in my mind when reading these two articles. I'm wrestling with these kind of things for years.

To give my two cents on the subject:

What I usualy do is make a DataObject table. For every person, organisation, Article, Meeting or whatever record I make in one of these tables I make a "counterpart" record in the DataObject table (working with newsequential GUIDs of course). No I can make links between dataobject with linktypes and I make a sort of workflow enginge bij giving dataobjects a "status". Very generic, but it comes with a price.

The datamodel is flexible and it's hard to see through an implementation of these objects and relations. DataObject is becoming a large table and logic is often recursive and when there's a lot of user concurrency it will be a performance hit (not to mention Locking).

Good side is scalability and a very fast time to markte for (small) solutions. Bad side is that it's hard to document and to support all these instances of databases. Lot's of query's are custommade and when requirements change..... not to mention bug resolving...

Instead of phonenumber entity I used CommunicationResource which can be phonenumber, e-mail address, internetsite and such.

One of the nifty problems was "Addresses". When you want to display an address list of the people working at your organisation, it was not easy AT ALL!

What if someone had two houses? (consider a child of divorced parents with a co-parent-ship) Then we needed a property "Primary". But what if someone was moving and you wanted to keep a future of historical home-address? Query's got complicated very easily not to mention a peformance hit on this generic datamodel.

Anyways... these kind of topic are very interesting but I haven't found the "silver bullit" yet.







Henri
~~~~
There's no place like 127.0.0.1
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 05/22/2008 :  08:37:42  Show Profile  Visit spirit1's Homepage  Reply with Quote
there is no silver bullet

it's good to be familiar with all concepts to know which one to use on which occasion.


_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

anonymous1
Posting Yak Master

185 Posts

Posted - 05/23/2008 :  11:02:29  Show Profile  Reply with Quote
here is an alterative using a partitioned view...

--------------------------------------------------------------------------------
-- PHONE
--------------------------------------------------------------------------------
CREATE TABLE dbo.Phone
(
PhoneID int identity (1,1) not null
,AreaCode char(3) not null
,ExchangeCode char(3) not null
,LineCode char(4) not null
,PRIMARY KEY CLUSTERED (PhoneID)
,UNIQUE (AreaCode, ExchangeCode, LineCode)
);
GO
--------------------------------------------------------------------------------
-- PERSON
--------------------------------------------------------------------------------
CREATE TABLE dbo.Person
(
PersonID int identity (1,1) not null
,PersonName varchar(50) not null
,PRIMARY KEY CLUSTERED (PersonID)
);
GO
--------------------------------------------------------------------------------
-- PERSON_PHONE
--------------------------------------------------------------------------------
CREATE TABLE dbo.PersonPhone
(
PersonID int not null REFERENCES dbo.Person(PersonID)
,PhoneID int not null REFERENCES dbo.Phone(PhoneID)
,EntityIndicator tinyint not null DEFAULT (1)
,CHECK (EntityIndicator = 1)
,PRIMARY KEY (EntityIndicator, PersonID, PhoneID)
,UNIQUE CLUSTERED (PersonID, PhoneID)
);
GO
--------------------------------------------------------------------------------
-- OFFICE
--------------------------------------------------------------------------------
CREATE TABLE dbo.Office
(
OfficeID int identity (1,1) not null
,OfficeName varchar(50) not null
,PRIMARY KEY CLUSTERED (OfficeID)
);
GO
--------------------------------------------------------------------------------
-- OFFICE_PHONE
--------------------------------------------------------------------------------
CREATE TABLE dbo.OfficePhone
(
OfficeID int not null REFERENCES dbo.Office(OfficeID)
,PhoneID int not null REFERENCES dbo.Phone(PhoneID)
,EntityIndicator tinyint not null DEFAULT (2)
,CHECK (EntityIndicator = 2)
,PRIMARY KEY (EntityIndicator, OfficeID, PhoneID)
,UNIQUE CLUSTERED (OfficeID, PhoneID)
);
GO
--------------------------------------------------------------------------------
-- INTERFACE
--------------------------------------------------------------------------------
CREATE VIEW dbo.IPhoneContact
AS
SELECT
EntityIndicator
,PersonID AS InstanceID
,PhoneID
FROM
dbo.PersonPhone
UNION ALL
SELECT
EntityIndicator
,OfficeID AS InstanceID
,PhoneID
FROM
dbo.OfficePhone;
GO
--------------------------------------------------------------------------------
-- TEST DATA
--------------------------------------------------------------------------------
INSERT dbo.Person (PersonName)
SELECT 'Anne' UNION ALL
SELECT 'Bob' UNION ALL
SELECT 'Carol';
GO
INSERT dbo.Phone (AreaCode,ExchangeCode,LineCode)
SELECT '800','555','1212' UNION ALL
SELECT '888','555','1212' UNION ALL
SELECT '877','555','1212' UNION ALL
SELECT '123','555','1212' UNION ALL
SELECT '456','555','1212' UNION ALL
SELECT '789','555','1212';
GO
INSERT dbo.PersonPhone (PersonID,PhoneID)
SELECT
(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Anne')
,(SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '800')
UNION ALL
SELECT
(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Bob')
,(SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '888')
UNION ALL
SELECT
(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Carol')
,(SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '888');
GO
INSERT dbo.Office (OfficeName)
SELECT 'DBA Group' UNION ALL
SELECT 'Engineering';
GO
INSERT dbo.OfficePhone (OfficeID,PhoneID)
SELECT
(SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'DBA Group')
,(SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '800')
UNION ALL
SELECT
(SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'Engineering')
,(SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '877');
GO
select * from dbo.IPhoneContact;
GO
--------------------------------------------------------------------------------
-- TEST CASES
--------------------------------------------------------------------------------
DELETE
FROM dbo.IPhoneContact
WHERE PhoneID = (SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '800');
GO
DELETE
FROM dbo.IPhoneContact
WHERE PhoneID = (SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '888');
GO
select * from dbo.IPhoneContact;
GO
INSERT dbo.IPhoneContact (EntityIndicator,InstanceID,PhoneID)
SELECT
1
,(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Anne')
,(SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '123');
GO
INSERT dbo.IPhoneContact (EntityIndicator,InstanceID,PhoneID)
SELECT
2
,(SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'DBA Group')
,(SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '456');
GO
select * from dbo.IPhoneContact;
GO
UPDATE dbo.IPhoneContact
SET PhoneID = (SELECT PhoneID FROM dbo.Phone WHERE AreaCode = '789')
WHERE InstanceID = (SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'Engineering')
AND EntityIndicator = 2;
GO
select * from dbo.IPhoneContact;
GO
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/23/2008 :  11:50:26  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
anonymous1 -- I like the way you are thinking, but your example treats Phone numbers as entities, not attributes. Deleting a PhoneContact always leaves an orphan row in the Phone table. These are the things I aimed to avoid in the article.

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 05/23/2008 11:55:39
Go to Top of Page

wavesailor
Starting Member

2 Posts

Posted - 05/28/2008 :  14:16:46  Show Profile  Reply with Quote
Jeff,

Thanks for the great article. I have two questions though. The first one I may sound like a bit of a noobie ... and I actually am

Your code implementing the "exclusive or" boolean in T-SQL I could not follow:
check (case when EmpId is null then 0 else 1 end +
case when OfficeID is null then 0 else 1 end = 1)
Please could you explain how it gets evaluated?
The 2nd question I had was around your The "Phone Entity" Approach. If you need to add a Contacts Phone numbers, then you would just add another field to the table PhoneEntities called ContactID - Is that correct?

Many thanks,

Grant
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/28/2008 :  14:59:06  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:

Your code implementing the "exclusive or" boolean in T-SQL I could not follow:
check (case when EmpId is null then 0 else 1 end +
case when OfficeID is null then 0 else 1 end = 1)
Please could you explain how it gets evaluated?



It is simply adding up two case expressions that return either 1 or 0 depending on the condition. If you are not sure how CASE works, look it up in Books On Line, it is explained pretty well there with examples better than I could do here. But basically it is checking that either EmpID is null and OfficeID isn't (1+0=1) or EmpID is NOT null and OfficeID is (0+1=1). If both are null, the check is (0+0=0), and if both are NOT null, the check comes out as (1+1=2).

quote:

The 2nd question I had was around your The "Phone Entity" Approach. If you need to add a Contacts Phone numbers, then you would just add another field to the table PhoneEntities called ContactID - Is that correct?



That's the first step, but you'd also need to alter the unique constraint, the check constraint, and any stored procedures to include a @ContactID parameter as well.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

anonymous1
Posting Yak Master

185 Posts

Posted - 05/30/2008 :  13:06:40  Show Profile  Reply with Quote
took another stab, this approach does not leverage features of a partitioned view, so i took out the partitioning column and encoded the pk. (it seemed like you were not woried about following typical database guidlines for this atypical senario)...


--------------------------------------------------------------------------------
-- PHONE
--------------------------------------------------------------------------------
CREATE TABLE dbo.Phone
(
PhoneID int identity (1,1) not null
,AreaCode char(3) not null
,ExchangeCode char(3) not null
,LineCode char(4) not null
,PRIMARY KEY CLUSTERED (PhoneID)
,UNIQUE (AreaCode, ExchangeCode, LineCode)
);
GO
--------------------------------------------------------------------------------
-- PERSON
--------------------------------------------------------------------------------
CREATE TABLE dbo.Person
(
PersonID int identity (1,10) not null --ensures pk stays mutually exclusive accross entities
,PersonName varchar(50) not null
,CHECK (PersonID % 10 = 1) --ensures pk stays mutually exclusive accross entities
,PRIMARY KEY CLUSTERED (PersonID)
);
GO
--------------------------------------------------------------------------------
-- PERSON_PHONE
--------------------------------------------------------------------------------
CREATE TABLE dbo.PersonPhone
(
PersonID int not null REFERENCES dbo.Person(PersonID)
,AreaCode char(3) not null
,ExchangeCode char(3) not null
,LineCode char(4) not null
,FOREIGN KEY (AreaCode, ExchangeCode, LineCode) REFERENCES dbo.Phone (AreaCode, ExchangeCode, LineCode) ON UPDATE CASCADE ON DELETE CASCADE
,PRIMARY KEY (PersonID, AreaCode, ExchangeCode, LineCode)
);
GO
--------------------------------------------------------------------------------
-- OFFICE
--------------------------------------------------------------------------------
CREATE TABLE dbo.Office
(
OfficeID int identity (2,10) not null --ensures pk stays mutually exclusive accross entities
,OfficeName varchar(50) not null
,CHECK (OfficeID % 10 = 2) --ensures pk stays mutually exclusive accross entities
,PRIMARY KEY CLUSTERED (OfficeID)
);
GO
--------------------------------------------------------------------------------
-- OFFICE_PHONE
--------------------------------------------------------------------------------
CREATE TABLE dbo.OfficePhone
(
OfficeID int not null REFERENCES dbo.Office(OfficeID)
,AreaCode char(3) not null
,ExchangeCode char(3) not null
,LineCode char(4) not null
,FOREIGN KEY (AreaCode,ExchangeCode,LineCode) REFERENCES dbo.Phone (AreaCode,ExchangeCode,LineCode) ON UPDATE CASCADE ON DELETE CASCADE
,PRIMARY KEY (OfficeID,AreaCode,ExchangeCode,LineCode)
);
GO
--------------------------------------------------------------------------------
-- INTERFACE
--------------------------------------------------------------------------------
CREATE VIEW dbo.IPhoneContact
AS
SELECT
PersonID AS InstanceID
,AreaCode
,ExchangeCode
,LineCode
FROM
dbo.PersonPhone
UNION ALL
SELECT
OfficeID AS InstanceID
,AreaCode
,ExchangeCode
,LineCode
FROM
dbo.OfficePhone;
GO
--------------------------------------------------------------------------------
-- INSTEAD OF TRIGGER
--------------------------------------------------------------------------------
CREATE TRIGGER dbo.IPhoneContact_InsteadOf
ON dbo.IPhoneContact
INSTEAD OF INSERT, UPDATE, DELETE
AS
begin
DECLARE @TranCount tinyint
SET @TranCount = @@TRANCOUNT

BEGIN TRAN

BEGIN TRY
----------------------------------------------------------
--PHONE DELETES
----------------------------------------------------------
--person (pk least significant digit = 1)
DELETE
dbo.PersonPhone
FROM
dbo.PersonPhone
JOIN
deleted
ON deleted.InstanceID = dbo.PersonPhone.PersonID --id mutually exclusive by check constraint
--office (pk least significant digit = 2)
DELETE
dbo.OfficePhone
FROM
dbo.OfficePhone
JOIN
deleted
ON deleted.InstanceID = dbo.OfficePhone.OfficeID --id mutually exclusive by check constraint

--remove orphaned parents
DELETE
dbo.Phone
FROM
dbo.Phone
LEFT JOIN
dbo.PersonPhone
ON dbo.PersonPhone.AreaCode = dbo.Phone.AreaCode
AND dbo.PersonPhone.ExchangeCode = dbo.Phone.ExchangeCode
AND dbo.PersonPhone.LineCode = dbo.Phone.LineCode
LEFT JOIN
dbo.OfficePhone
ON dbo.OfficePhone.AreaCode = dbo.Phone.AreaCode
AND dbo.OfficePhone.ExchangeCode = dbo.Phone.ExchangeCode
AND dbo.OfficePhone.LineCode = dbo.Phone.LineCode
WHERE
dbo.PersonPhone.AreaCode IS NULL
AND
dbo.OfficePhone.AreaCode IS NULL

----------------------------------------------------------
--PHONE INSERTS
----------------------------------------------------------
--create parents
INSERT dbo.Phone
(
AreaCode
,ExchangeCode
,LineCode
)
SELECT
inserted.AreaCode
,inserted.ExchangeCode
,inserted.LineCode
FROM
inserted
LEFT JOIN
dbo.Phone
ON dbo.Phone.AreaCode = inserted.AreaCode
AND dbo.Phone.ExchangeCode = inserted.ExchangeCode
AND dbo.Phone.LineCode = inserted.LineCode
WHERE
dbo.Phone.AreaCode IS NULL
GROUP BY
inserted.AreaCode
,inserted.ExchangeCode
,inserted.LineCode
--person (pk least significant digit = 1)
INSERT dbo.PersonPhone
(
PersonID
,AreaCode
,ExchangeCode
,LineCode
)
SELECT
InstanceID
,AreaCode
,ExchangeCode
,LineCode
FROM
inserted
WHERE
InstanceID % 10 = 1 --least significant digit determines entity type
--office (pk least significant digit = 2)
INSERT dbo.OfficePhone
(
OfficeID
,AreaCode
,ExchangeCode
,LineCode
)
SELECT
InstanceID
,AreaCode
,ExchangeCode
,LineCode
FROM
inserted
WHERE
InstanceID % 10 = 2 --least significant digit determines entity type
END TRY

BEGIN CATCH
if @@TRANCOUNT > @TranCount
begin
ROLLBACK TRAN
end
END CATCH

if @@TRANCOUNT > @TranCount
begin
COMMIT TRAN
end

end;
GO
--------------------------------------------------------------------------------
-- TEST CASES
--------------------------------------------------------------------------------
INSERT dbo.Person (PersonName)
SELECT 'Anne' UNION ALL
SELECT 'Bob' UNION ALL
SELECT 'Carol';
GO
INSERT dbo.Office (OfficeName)
SELECT 'DBA Group' UNION ALL
SELECT 'Engineering';
GO
INSERT dbo.IPhoneContact (InstanceID,AreaCode,ExchangeCode,LineCode)
SELECT

(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Anne')
,'800','555','1212'
UNION ALL
SELECT

(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Bob')
,'888','555','1212'
UNION ALL
SELECT

(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Carol')
,'888','555','1212'
UNION ALL
SELECT

(SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'DBA Group')
,'800','555','1212'
UNION ALL
SELECT

(SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'Engineering')
,'877','555','1212'
GO
INSERT dbo.Phone (AreaCode,ExchangeCode,LineCode)
SELECT '123','555','1212'
UNION ALL
SELECT '456','555','1212';
GO
select * from dbo.IPhoneContact;
GO
DELETE
FROM dbo.IPhoneContact
WHERE InstanceID = (SELECT PersonID FROM dbo.Person WHERE PersonName = 'Anne') AND AreaCode = '800';
GO
select * from dbo.Phone where AreaCode = '800';
GO
DELETE
FROM dbo.IPhoneContact
WHERE InstanceID = (SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'DBA Group') AND AreaCode = '800';
GO
select * from dbo.Phone where AreaCode = '800';
GO
select * from dbo.IPhoneContact;
GO
INSERT dbo.IPhoneContact (InstanceID,AreaCode,ExchangeCode,LineCode)
SELECT

(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Anne')
,'800','555','1212'
UNION ALL
SELECT

(SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'DBA Group')
,'800','555','1212';
GO
select * from dbo.IPhoneContact;
GO
DELETE
FROM dbo.IPhoneContact
WHERE AreaCode = '800';
GO
DELETE
FROM dbo.IPhoneContact
WHERE AreaCode = '888';
GO
select * from dbo.Phone;
GO
select * from dbo.IPhoneContact;
GO
INSERT dbo.IPhoneContact (InstanceID,AreaCode,ExchangeCode,LineCode)
SELECT
(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Anne')
,'123','555','1212';
GO
INSERT dbo.IPhoneContact (InstanceID,AreaCode,ExchangeCode,LineCode)
SELECT
(SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'DBA Group')
,'456','555','1212';
GO
select * from dbo.IPhoneContact;
GO
INSERT dbo.IPhoneContact (InstanceID,AreaCode,ExchangeCode,LineCode)
SELECT

(SELECT PersonID FROM dbo.Person WHERE PersonName = 'Anne')
,'800','555','1212'
UNION ALL
SELECT

(SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'DBA Group')
,'800','555','1212';
GO
UPDATE dbo.IPhoneContact
SET AreaCode = '789'
WHERE AreaCode = '800';
GO
UPDATE dbo.IPhoneContact
SET InstanceID = (SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'DBA Group')
WHERE AreaCode = '877';
GO
select * from dbo.IPhoneContact;
GO
UPDATE dbo.IPhoneContact
SET InstanceID = (SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'Engineering')
WHERE InstanceID = (SELECT OfficeID FROM dbo.Office WHERE OfficeName = 'DBA Group');
GO
/*
delete from iphonecontact;
delete from person;
delete from office;
*/
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/03/2008 :  08:30:05  Show Profile  Reply with Quote
Next up, interface inheritance ... Phone Number is really a sub-type of Point of Contact, which also has children of Email, IM, SMS, Street Address, GPS Coordinates and whatever else ...

hehe ... good stuff here.

Jay
to here knows when
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000