Article: Implementing Table Interfaces

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Implementing Table Interfaces

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2008-05-19 : 08:14:30

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

2 Posts

Posted - 2008-05-19 : 11:41:59

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-19 : 11:49:45

Andrew -- that technique was covered in the "Table Inheritance" article referenced in the intro.

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

abeaupre
Starting Member

2 Posts

Posted - 2008-05-19 : 11:58:42

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

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2008-05-22 : 08:34:01

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-05-22 : 08:37:42

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!

anonymous1
Posting Yak Master

185 Posts

Posted - 2008-05-23 : 11:02:29

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-23 : 11:50:26

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

wavesailor
Starting Member

2 Posts

Posted - 2008-05-28 : 14:16:46

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-28 : 14:59:06

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

anonymous1
Posting Yak Master

185 Posts

Posted - 2008-05-30 : 13:06:40

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;
*/

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-06-03 : 08:30:05

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources