Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
Microsoft SQL Server Programming
- An Outline for Development
- Database Services
- Database Objects: Databases
- Database Objects: Tables
- Database Objects: Table Relationships
- Database Objects: Keys
- Database Objects: Constraints
- Database Objects: Data Types
- Database Objects: Views
- Database Objects: Stored Procedures
- Database Objects: Indexes
- Database Objects: User Defined Functions
- Database Objects: Triggers
- Database Design: Requirements, Entities, and Attributes
- Business Process Model Notation (BPMN) and the Data Professional
- Business Questions for Database Design, Part One
- Business Questions for Database Design, Part Two
- Database Design: Finalizing Requirements and Defining Relationships
- Database Design: Creating an Entity Relationship Diagram
- Database Design: The Logical ERD
- Database Design: Adjusting The Model
- Database Design: Normalizing the Model
- Creating The Physical Model
- Database Design: Changing Attributes to Columns
- Database Design: Creating The Physical Database
- Database Design Example: Curriculum Vitae
- The SQL Server Sample Databases
- The SQL Server Sample Databases: pubs
- The SQL Server Sample Databases: NorthWind
- The SQL Server Sample Databases: AdventureWorks
- The SQL Server Sample Databases: Adventureworks Derivatives
- UniversalDB: The Demo and Testing Database, Part 1
- UniversalDB: The Demo and Testing Database, Part 2
- UniversalDB: The Demo and Testing Database, Part 3
- UniversalDB: The Demo and Testing Database, Part 4
- Getting Started with Transact-SQL
- Transact-SQL: Data Definition Language (DDL) Basics
- Transact-SQL: Limiting Results
- Transact-SQL: More Operators
- Transact-SQL: Ordering and Aggregating Data
- Transact-SQL: Subqueries
- Transact-SQL: Joins
- Transact-SQL: Complex Joins - Building a View with Multiple JOINs
- Transact-SQL: Inserts, Updates, and Deletes
- An Introduction to the CLR in SQL Server 2005
- Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
- Design Elements Part 2: Controlling SQL's Scope
- Design Elements Part 3: Error Handling
- Design Elements Part 4: Variables
- Design Elements Part 5: Where Does The Code Live?
- Design Elements Part 6: Math Operators and Functions
- Design Elements Part 7: Statistical Functions
- Design Elements Part 8: Summarization Statistical Algorithms
- Design Elements Part 9:Representing Data with Statistical Algorithms
- Design Elements Part 10: Interpreting the Data—Regression
- Design Elements Part 11: String Manipulation
- Design Elements Part 12: Loops
- Design Elements Part 13: Recursion
- Design Elements Part 14: Arrays
- Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
- Design Elements Part 16: Event-Driven Programming
- Design Elements Part 17: Program Flow
- Forming Queries Part 1: Design
- Forming Queries Part 2: Query Basics
- Forming Queries Part 3: Query Optimization
- Forming Queries Part 4: SET Options
- Forming Queries Part 5: Table Optimization Hints
- Using SQL Server Templates
- Transact-SQL Unit Testing
- Index Tuning Wizard
- Unicode and SQL Server
- SQL Server Development Tools
- The SQL Server Transact-SQL Debugger
- The Transact-SQL Debugger, Part 2
- Basic Troubleshooting for Transact-SQL Code
- An Introduction to Spatial Data in SQL Server 2008
- Performance Tuning
- Practical Applications
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
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:
Here’s my logic on each of these elements, and what sub-elements I added to them to make the final table.
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.
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.
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.
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.
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.
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.