Relational Database Designs Optimizing Object Oriented Programming
Today I’d like to share another relational database design I find useful when working with Object Oriented Programs. Let me remind you once again that you don’t want your Object programs to determine your schema. However, when they align more closely, both worlds benefit.
I really love working with the Type/Subtype schema when writing Object Oriented Programs. The intention of this database pattern is to have a shared table with an indirect foreign key relationship to multiple tables.
I’m going to share an address table with both a Person and an Organization as demonstrated in the figure above. You can’t do that by having a foreign key from the address table to the Organization table and the Person table unless both tables have rows with the same key. While you may want to do that in some instances…that is not the goal of a type/subtype pattern as demonstrated here. The goal is simply to share the same address table, and have an address record point to only one Person or one Organization, but not both.
The Address table has an indirect relationship to both tables because both tables also share the same key from the Entity table. There is nothing relationally keeping you from creating a record in both the Person and Organization table with the same EntityID. This is something you have to manage through your own check constraints.
This schema design works nicely when using object oriented techniques because I can use inheritance or composition to manage my address information with my Person objects or my Organization objects. Granted, I can do that regardless. However, now I don’t have to write two (or more) ORM persistence methods to manage different tables for address information. One persistence method serves for all addresses data.
Here is some sample code creating four tables, Entity, Person, Organization and Address.
Create Table Entity (
EntityID INT NOT NULL PRIMARY KEY
,EnityTypeCode CHAR(1) NOT NULL
)
INSERT INTO Entity SELECT 1, 'P'
INSERT INTO Entity SELECT 2, 'P'
INSERT INTO Entity SELECT 3, 'O'
INSERT INTO Entity SELECT 4, 'O'
create table person (
PersonID INT NOT NULL PRIMARY KEY
,EntityID INT NOT NULL
)
ALTER TABLE Person
ADD CONSTRAINT FK_Person_Entity
FOREIGN KEY (EntityID)
REFERENCES Entity (EntityID)
INSERT INTO person SELECT 1, 1
INSERT INTO person SELECT 2, 2
CREATE TABLE Organization (
OrganizationID INT NOT NULL PRIMARY KEY
,EntityID INT NOT NULL
)
ALTER TABLE Organization
ADD CONSTRAINT FK_Organization_Entity
FOREIGN KEY (EntityID)
REFERENCES Entity (EntityID)
INSERT INTO Organization SELECT 1, 3
INSERT INTO Organization SELECT 2, 4
CREATE TABLE ADDRESS (
AddressID INT NOT NULL PRIMARY KEY
,EntityID INT NOT NULL
)
ALTER TABLE Address
Add Constraint FK_Address_Entity
FOREIGN KEY (EntityID)
REFERENCES Entity (EntityID)
INSERT INTO ADDRESS SELECT 1, 1
INSERT INTO ADDRESS SELECT 2, 2
INSERT INTO ADDRESS SELECT 3, 3
INSERT INTO ADDRESS SELECT 4, 4
SELECT P.PersonID, P.EntityID, A.AddressID
FROM Person P
JOIN ADDRESS A ON P.EntityID = A.EntityID
SELECT O.OrganizationID, O.EntityID, A.AddressID
FROM Organization O
JOIN ADDRESS A ON O.EntityID = A.EntityID
SQL Server TIP
Sometimes you need to transfer a database from a later version of SQL Server to an older version. With SQL Server 2008 R2 this can be done using the database scripting tool. In SQL Server Management Studio, right click on the database to transfer. Choose tasks->Script Database.
Follow the wizards. In the wizard dialog be sure to click on the Advanced button so that you can include both the schema (if you require it) and/or the data in your output.
If you include the data, the scripting tool will generate a series of insert scripts to populate your tables.
You may now use this script to generate and populate a database in another instance, including older versions. If you are going to execute your script on an older version, be sure to select the appropriate version in the Advanced dialog as one of your scripting options.
Please share your tips, experiences or comments with us by sending your response to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
The Procedural DBA (Part 1)
As RDBMS products gained popularity, the role of the DBA expanded. Of course, DBAs still designed databases, but increasingly these were generated from logical data models created by data administration staffs.
Featured White Paper(s)
Web Content Management
Written by AvePoint
The power of the World Wide Web has dramatically change… (read more)
Featured Script
dba3_MS_ScriptsToTransferLoginsAndPasswords
The MS Scripts for the stored procedures to use to Transfer Logins and Passwords Between SQL Servers (Q246133)… (read more)