SQL Server Reference Guide

UniversalDB: The Demo and Testing Database, Part 3

Last updated Mar 28, 2003.

In Part 1 of this series, I explained the rationale behind the need for a single database that would be able to work on multiple platforms, for multiple industries. Since I do a lot of teaching, demonstrations and testing, I would like something that is simple to understand, and quick to implement and customize.

In Part 2 of this series, I covered the “base” tables I think cover most of the requirements I have. I have the following tables designed:

1. Person
2. Organization
3. Material
4. Accounting
5. Activity

I also covered the columns within those tables and how they can be used. It’s important to note that since the industries I deal with are so different, there are some rather distinct design choices I’m making along the way.

Handling the Joins

Within the tables, I have a Primary Key on every table. Also within the tables, I have a field that can be used to perform a join against, such as “AssignedTo” or an ID field. Those allow for self-joins on a table (such as a manager’s PK in an employee’s “Assigned To” field) or even cross-table. Of course, this turns into Programmatic Referential Integrity rather than Declarative Referential Integrity, but for my training classes, demos and testing this is an acceptable tradeoff.

Using the fields this way also allows me to have both one-to-one and many-to-one joins. So it’s fairly easy for me to see that a doctor is assigned to hundreds of patients, or even that a particular doctor works for multiple hospitals. But what it does not account for is a many-to-many join.

So to handle a situation where I need to show a multiple doctor-patient-hospital or product-sale-register, I created a VERY ugly table which has an interesting set of fields.

The table is in a schema called “Relationships” (clever, no?) and is called “TableToTable.” It has the Primary Keys from all of the other tables in it. I also include a date-time field and a description field as well, so that I can even store join-specific data.

Before I detail the fields from this table, I need to stop a moment and re-iterate that this design in no way represents a best-practice for a production database. But it is a good practice for the requirements I have, which is why I’ve set it up this way. In fact, it even helps one goal very well — it makes a very good training database, especially when you want to teach a class on taking a design meant for one system and optimizing it to another.

The point is, don’t get hung up too much on the fact that this database isn’t following a set of rules you may have learned — even if you learned those rules from me. The key is that the database fit the requirements, and in this case, it works.

So with all of those caveats, here is the “TableToTable” table:

PersonPK — This serves as the Person table key reference

OrganizationPK — The Organization table key reference

MaterialPK — The Material table key reference

ActivityPK — The Activity table key reference

AccountingPK — Accounting table key reference

Category — This allows you to give a category of this particular line item in the relationship

RelationshipLevel — There are several options here if you want to use them — primary, secondary, patient, doctor, etc. Even a numeric leveling would work.

Description — I included this in case the relationship for this row actually described an event, rather than just serving as a link.

Modified — Similar to the above, this allows me to track the date and time the relationship event was created and then modified.

The Complete Database Creation Script

With those explanations of the requirements and the tables, columns and relationships I’ve chosen, here is the entire script to create the database and the tables. Remember, always run things like this on a test system, something you can easily do without. This should be quite harmless, but of course those are famous last words!

You’ll notice most of the columns are set to VARCHAR as the data type. I did this to be as flexible as possible for the data values that go there, which lets me modle more industries. Here’s the complete script:

CREATE DATABASE UniversalDB;
GO
USE UniversalDB;
GO
CREATE SCHEMA Base;
GO
CREATE TABLE [Base].[Accounting]
(
[AccountingPK] [bigint] NOT NULL,
[AccountingStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountingID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountingType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShortName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FullName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Breakdown] [xml] NULL,
[Initiation] [datetime] NULL,
[Updated] [datetime] NULL,
[Unit] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Measurement] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Amount] [int] NULL,
[CurrencyAmount] [money] NULL,
[CurrencyType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Direction] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [Base].[Accounting] ADD CONSTRAINT [PK_Accounting] PRIMARY KEY CLUSTERED 
([AccountingPK]) ON [PRIMARY]
GO
CREATE TABLE [Base].[Activity]
(
[ActivityPK] [bigint] NOT NULL,
[AcitivtyStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActivityID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActivityType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShortName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FullName] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Location] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Breakdown] [xml] NULL,
[Initiation] [datetime] NULL,
[Updated] [datetime] NULL,
[DateTimeStart] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateTimeComplete] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Duration] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [Base].[Activity] ADD CONSTRAINT [PK_Activity] PRIMARY KEY CLUSTERED 
([Activity-PK]) ON [PRIMARY]
GO
CREATE TABLE [Base].[Material]
(
[MaterialPK] [bigint] NOT NULL,
[MaterialStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MaterialID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MaterialType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShortName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FullName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Location] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Breakdown] [xml] NULL,
[Initiation] [datetime] NULL,
[Updated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [Base].[Material] ADD CONSTRAINT [PK_Material] PRIMARY KEY CLUSTERED ([Material-PK]) ON [PRIMARY]
GO
CREATE TABLE [Base].[Organization]
(
[OrganizationPK] [bigint] NOT NULL,
[OrganizationStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrganizationID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrganizationType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShortName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FullName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AdressLine] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CityOrMunicipaility] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StateOrRegion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostalIdentification] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AssignedTo] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phones] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EContact] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Demographics] [xml] NULL,
[Initiation] [datetime] NULL,
[Updated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [Base].[Organization] ADD CONSTRAINT [PK_Organization] PRIMARY KEY CLUSTERED 
([OrganizationPK]) ON [PRIMARY]
GO

CREATE TABLE [Base].[Person]
(
[PersonPK] [bigint] NOT NULL,
[PersonStatus] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PersonID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PersonType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Lname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AdressLine] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CityOrMunicipaility] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StateOrRegion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostalIdentification] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AssignedTo] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phones] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EContact] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Demographics] [xml] NULL,
[Initiation] [datetime] NULL,
[Updated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [Base].[Person] ADD CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([PersonPK]) ON [PRIMARY]
GO
CREATE SCHEMA Relationships;
GO
CREATE TABLE [Relationships].[TableToTable]
(
[PersonPK] [bigint] NOT NULL,
[OrganizationPK] [bigint] NOT NULL,
[MaterialPK] [bigint] NULL,
[ActivityPK] [bigint] NULL,
[AccountingPK] [bigint] NULL,
[Category] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RelationshipLevel] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Modified] [datetime] NULL
) ON [PRIMARY]
GO

In the next installment, I’ll fill the UniveralDB with some data and try a few queries.

InformIT Articles and Sample Chapters

To do “proper” design instead of this example for training and demos, check out the series of Reference Guide updates starting here.

Books and eBooks

Another great book on design is Designing Effective Database Systems, by Rebecca M. Riordan.

Online Resources

I’ll violate most of these top ten design mistakes — on purpose — in this design. But you should still check it out for production databases.