SQL Server Reference Guide

UniversalDB: The Demo and Testing Database, Part 2

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 fact, I have more requirements than that, and you can read those in the previous tutorial. In that installment I also covered the constraints I have for each requirement. This is important in the design of any system, because there are always things that you need to know about when you’re creating it. For instance, since I want to model a realistic set of activities for almost any industry, one constraint is that I’ll have to separate the data from the schema. That dictates in the implementation phase, the part I’m working on next, I’ll have at least two scripts for that. In other words, some constraints will actually dictate an implementation choice.

With the requirements and constraints detailed out, my next task is to create a design. In this tutorial, I’ll explain my choices, and then I’ll implement that design and test it.

The UniversalDB Database Design

I’ll take the requirements from the last tutorial, and like any database project, I’ll start with the primary nouns I see. Next, I’ll create the “sub” nouns to fill out those requirements, and then finally I’ll lay in the relationships for those nouns. The intersection of these become the verbs in a requirements sentence.

Laying out the nouns from the requirements

In this case, the requirements I’ve laid out for the project are not exactly like what you would normally see in a database requirements specification. I don’t have any of the standard nouns and verbs from a particular industry, since I’m modeling every industry.

But that actually does bring up some nouns and verbs. In fact, it defines them for me. As I thought about modeling multiple systems, I asked myself what “base” elements would be part of any query I would run to show data to a doctor, a lawyer, a construction worker and so on. I thought of these four primary nouns:

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

Here’s my logic on each of these elements, and what sub-elements I added to them to make the final table.

Person

Anything I show will include people. Sure, I could have separate tables for an employee, a manager, a customer and so on, but not all organizations have those. For instance, a non-profit enterprise might not have “customers," in the classic sense of that word. But every organization will have people involved — whatever their role is.

People also perform actions, and since I want to model that, I include a general table like this to track those folks. So the Person table is first. But there are also “sub” elements, or attributes, or in this case, columns about those people that I want to capture. I’ll add what I think I need, trying to avoid having a “database in a field” as much as possible. That means I don’t want to assign a lot of meaning into a field, such that position one of the character matters and so on. Here’s what I came up with:

PersonPK — I need a surrogate Primary Key for the table, so this is it. I’ll repeat this pattern for every table.

PersonStatus — This might hold values like “Active” or “Inactive” to show whether someone is still in the system but not currently assigned to any activities or any of the other tables.

PersonID — This could be an employee number, code, or other organization-specific code I want to show. I could even put (fake) social security numbers here and then use whatever tools I wish to encrypt that information.

PersonType — I include this to have a self-joining table for employee/manager, customer/employee, doctor/patient, whatever I need. It could even hold a value of “loyal employee” or “gold member” or whatever.

Title — This might be Mr. or Mrs., Doctor, whatever I need for a specific demonstration or training class.

Fname — First name shown.

MName — Second name shown.

Lname — Third name shown.

AdressLine — I thought about this one long and hard. Normally you’ll have several lines for an address, to allow for an apartment number and so on. I don’t need that for the training or demo to “work,” and since this won’t be used in production, I didn’t bother to create more of these. I will make it quite long, however, so I could include several lines of information and just delineate them with a comma or something like that, should I need to.

CityOrMunicipaility — I do want to be able to group on city, but I used this name since some countries might not have a formal “city” designation.

StateOrRegion — Same logic here: I want to group by State in my training, but some countries don’t use that term.

PostalIdentification — Once again, this is a nice sorting, grouping or selection criteria, and most countries have some form of it.

Country — This allows my database to store data from around the world, demonstrating a word-wide enterprise.

AssignedTo — I used this for two purposes. The first is to “self-join” the table so that I could show managers and employees, things like that. But I could also use it to assign a patient to a hospital, etc.

Phones — Same logic as the address line. Normally you break out the phones to multiple rows in another table, but that seemed excessive for a demo/training system. If I really need multiple phones, I probably will not store them in multiple bunches in this field, but use the “Demographics” element that follows.

EContact — I can use this for an e-mail address, web page or whatever other electronic needs I have.

Demographics — A very interesting column indeed. This is an XML document field, so I can store a rich set of data about someone to demonstrate XML features and also to extend the design when it doesn’t “fit” what I need. I’ll just store any XML elements I need here that are tied to a specific person in the database.

Initiation — This is a date field that I can use to show when the record was initiated or even store a birth date or other start date if I wish.

Updated — Another date field that I can use to track when the record was changed.

Organization

People reside in, report to, or are affected by a business or other form of organization, hence this table. Organizations can also perform actions, just like a person. Here are the columns I chose for this table:

OrganizationPK — Primary Key, also a surrogate.

OrganizationStatus — Active, Inactive, phase of an organization’s life, or other segmentation.

OrganizationID — A company code or another organization identifier.

OrganizationType — Another categorization of an enterprise; could be “customer,” “partner,” etc.

ShortName — I included this field because I felt that sometimes a longer organization name would not represent that firm well. I can also use this for a subsidiary name.

FullName — Longer name of the organization.

AdressLine — Same logic as the address line for the Person table. If there are multiple addresses, I create multiple organizations that then use the ShortName to handle the breakdown.

CityOrMunicipaility — Same logic as this field in the Person table.

StateOrRegion — Same logic as this field in the Person table.

PostalIdentification — Same logic as this field in the Person table.

Country — Same logic as this field in the Person table.

AssignedTo — Same logic as this field in the Person table.

Phones — Same logic as this field in the Person table.

EContact — Same logic as this field in the Person table.

Demographics — Same logic as this field in the Person table.

Initiation — Same logic as this field in the Person table.

Updated — Same logic as this field in the Person table.

Material

Whenever a person or organization does something, they use or expend a material. I’ll also call a service a “Material” as well, since some companies or organizations keep an “inventory” of the services they provide. A material might even be a room or other physical item used in the performance of an action. Here are the columns I included for Material:

MaterialPK — Primary Key for this table, surrogate.

MaterialStatus — Ordered, on-hand, delivered, etc.

MaterialID — Any number the firm uses to identify the material, good or service.

MaterialType — A further categorization of the material, good or service.

ShortName — A “friendly name” or the opposite (a part number, for instance).

FullName — The longer name, catalog identification, or even complete description of the part or service.

Location — Where the item or service is stored or delivered from.

Breakdown — An XML field, used to store a full breakdown of the good or service. Could even include small binary data for a picture.

Initiation — Date and time when the part, good or service was obtained or created.

Updated — Date and time when the data about the part, good or service was changed or updated.

Accounting

You’ll notice in the last table, Material, I did not include a “value” or “number” field for the amount of time, product or good I have on hand in the organization. I reserved that for another table, called Accounting. After all, even in a non-profit agency you have to account for the movement of goods and services.

I’ll also use this table for the money side of things. Here are the columns I included and what they are used for:

AccountingPK — Primary Key for table, surrogate value.

AccountingStatus — Values like “Pending,” “Active,” and so on show the status of the accounting event.

AccountingID — An accounting code or service movement number. Could also be used to join to another table.

AccountingType — This might identify a financial transaction from an inventory change.

ShortName — Any code or other identifier in the system. In a Point of Sale (POS) system, I made it the ID number of the receipt.

FullName — A longer description of the transaction.

Breakdown — That very popular XML column that allows me to further expand this table.

Initiation — The date and time the transaction was initiated.

Updated — The date and time the transaction was altered.

Unit — A code, symbol or word that indicates the unit of the transaction, such as “EA,” “Each,” or even “Multiple.”

Measurement — The type of grouping used, such as “Package,” “Lot,” or “Sleeve.”

Amount — The number associated with the move or transaction, like 1 or 100.

CurrencyAmount — The amount of money involved, if applicable. This might be blank for inventory moves.

CurrencyType — The type of money used. Could be a currency code or the full type spelled out.

Direction — I use “Credit” or “Debit” to mean “in” or “out,” whether the transaction deals with money or not.

Activity

The final table I’ll cover in this part of the series is “Activity.” This table represents any work, movement or, well, activity done by an organization, person or material. I included the following columns to make this work:

ActivityPK — The surrogate Primary Key for the table.

AcitivtyStatus — Whether the activity is in-work, planned or completed.

ActivityID — An activity identifier the organization, person or material can use.

ActivityType — The type of activity performed, such as “Sale,” “Processing,” “Consulting.”

ShortName — Another code to further identify the activity.

FullName — A full description of the activity.

Location — Where the activity was performed, sent to, or received from.

Breakdown — The XML field for a more customization of the activity if needed.

Initiation — The time and date that the activity was initiated.

Updated — The time and date that the activity was last altered.

DateTimeStart — If the activity is time based, this would be the “clock start.” Using this field along with Initiation and/or Updated, you can track work done in segments during a longer date, like a project.

DateTimeComplete — The final date and time of the activity, with the same uses as above.

Duration — A stored calculation of how long the activity took. This might be filled out, left blank, or used in lieu of the other date and time fields depending on the industry.

I had better stop there. I have one more table to design, one that will make possible some interesting joins. Don’t worry; this will all make a little more sense when you see some actual data go in those tables. See you next time.

InformIT Articles and Sample Chapters

To do “proper” design instead of this example for training and demos, check out the Reference Guide series Database Design: Requirements, Entities, and Attributes 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.