Selective Uniqueness

We previously discussed PRIMARY KEY and UNIQUE constraints, but in some situations, neither of these will exactly fit the situation. For example, you may need to make sure some subset of the data, rather than every row, is unique. An example of this is a one-to-one relationship where you need to allow nulls, for example, a customerSettings table that lets you add a row for optional settings for a customer. If a user has settings, a row is created, but you want to ensure that only one row is created.

For example, say you have an employee table, and each employee can possibly have an insurance policy. The policy numbers must be unique, but the user might not have a policy.

There are two solutions to this problem that are common:

• Filtered indexes : This feature that was new in SQL Server 2008. The CREATE INDEX command syntax has a WHERE clause so that the index pertains only to certain rows in the table.
• Indexed view : In recent versions prior to 2008, the way to implement this is to create a view that has a WHERE clause and then index the view.

As a demonstration, I will create a schema and table for the human resources employee table with a column for employee number and insurance policy number as well (the examples in this chapter will be placed in a file named Chapter8 in the downloads and hence, any error messages will appear there as well).

 CREATE SCHEMA HumanResources;

 GO

 CREATE TABLE HumanResources.employee

 (

      EmployeeId int NOT NULL IDENTITY(1,1) CONSTRAINT PKalt_employee PRIMARY KEY,

      EmployeeNumber char(5) not null

           CONSTRAINT AKalt_employee_employeeNummer UNIQUE,

      --skipping other columns you would likely have

      InsurancePolicyNumber char(10) NULL

 );

One of the lesser known but pretty interesting features of indexes is the filtered index . Everything about the index is the same, save for the WHERE clause. So, you add an index like this:

 --Filtered Alternate Key (AKF)

 CREATE UNIQUE INDEX AKFHumanResources_Employee_InsurancePolicyNumber ON

 HumanResources.employee(InsurancePolicyNumber)

      WHERE InsurancePolicyNumber IS NOT NULL;

Then, create an initial sample row :

 INSERT INTO HumanResources.Employee (EmployeeNumber, InsurancePolicyNumber)

 VALUES ('A0001','1111111111'),

If you attempt to give another employee the same insurancePolicyNumber

 INSERT INTO HumanResources.Employee (EmployeeNumber, InsurancePolicyNumber)

 VALUES ('A0002','1111111111'),

this fails:

However, adding two rows with null will work fine:

 INSERT INTO HumanResources.Employee (EmployeeNumber, InsurancePolicyNumber)

 VALUES('A0003','2222222222'),

       ('A0004',NULL),

       ('A0005',NULL);

You can see that this:

 SELECT     *

 FROM       HumanResources.Employee;

returns the following:

The NULL example is the classic example, because it is common to desire this functionality. However, this technique can be used for more than just NULL exclusion. As another example, consider the case where you want to ensure that only a single row is set as primary for a group of rows, such as a primary contact for an account:

 CREATE SCHEMA Account;

 GO

 CREATE TABLE Account.Contact

 (

     ContactId varchar(10) not null,

     AccountNumber char(5) not null, --would be FK in full example

     PrimaryContactFlag bit not null,

     CONSTRAINT PKalt_accountContact

        PRIMARY KEY(ContactId, AccountNumber)

 );

Again, create an index, but this time, choose only those rows with primaryContactFlag = 1. The other values in the table could have as many other values as you want (of course, in this case, since it is a bit, the values could be only 0 or 1):

 CREATE UNIQUE INDEX

     AKFAccount_Contact_PrimaryContact

        ON Account.Contact(AccountNumber)

        WHERE PrimaryContactFlag = 1;

If you try to insert two rows that are primary, as in the following statements that will set both contacts 'fred' and 'bob' as the primary contact for the account with account number '11111':

 INSERT INTO Account.Contact

 SELECT 'bob','11111',1;

 GO

 INSERT INTO Account.Contact

 SELECT 'fred','11111',1;

the following error is returned:

To insert the row with 'fred' as the name and set it as primary (assuming the 'bob' row was inserted previously), you will need to update the other row to be not primary and then insert the new primary row:

 BEGIN TRANSACTION;

 UPDATE Account.Contact

 SET primaryContactFlag = 0

 WHERE accountNumber = '11111';

 INSERT Account.Contact

 SELECT 'fred','11111', 1;

 COMMIT TRANSACTION;

Note that in cases like this you would definitely want to use a transaction in your code so you don’t end up without a primary contact if the insert fails for some other reason.

Prior to SQL Server 2008, where there were no filtered indexes, the preferred method of implementing this was to create an indexed view. There are a couple of other ways to do this (such as in a trigger or stored procedure using an EXISTS query , or even using a user-defined function in a CHECK constraint), but the indexed view is the easiest. Then when the insert does its cascade operation to the indexed view, if there are duplicate values, the operation will fail. You can use indexed views in all versions of SQL Server, though only Enterprise Edition will make special use of the indexes for performance purposes. (In other versions, you have to specifically reference the indexed view to realize performance gains. Using indexed views for performance reasons will be demonstrated in Chapter 10.)

Returning to the InsurancePolicyNumber uniqueness example, you can create a view that returns all rows other than null insurancePolicyNumber values. Note that it has to be schema bound to allow for indexing:

 CREATE VIEW HumanResources.Employee_InsurancePolicyNumberUniqueness

 WITH SCHEMABINDING

 AS

   SELECT   InsurancePolicyNumber

   FROM     HumanResources.Employee

   WHERE    InsurancePolicyNumber  IS NOT NULL;

Now, you can index the view by creating a unique, clustered index on the view:

 CREATE UNIQUE CLUSTERED INDEX

    AKHumanResources_Employee_InsurancePolicyNumberUniqueness

    ON HumanResources.Employee_InsurancePolicyNumberUniqueness(InsurancePolicyNumber);

Now, attempts to insert duplicate values will be met with the following (assuming you drop the existing filtered index, which will be included in the code download.)

Both of these techniques are really quite fast and easy to implement. However, the filtered index has a greater chance of being useful for searches against the table, so it is really just a question of education for the programming staff members who might come up against the slightly confusing error messages in their UI or SSIS packages, for example (even with good naming I find I frequently have to look up what the constraint actually does when it makes one of my SSIS packages fail). Pretty much no constraint error should be bubbled up to the end users, unless they are a very advanced group of users, so the UI should be smart enough to either prevent the error from occurring or at least translate it into words that the end user can understand.

Range Uniqueness

In some cases, uniqueness isn’t uniqueness on a single column or even a composite set of columns, but rather over a range of values. Very common examples of this include appointment times, college classes, or even teachers/employees who can only be assigned to one location at a time.

We can protect against situations such as overlapping appointment times by employing a trigger and a range overlapping checking query. The toughest part about checking item ranges is that be three basic situations have to be checked. Say you have appointment1, and it is defined with precision to the second, and starting on '20110712 1:00:00PM', and ending at '20110712 1:59:59PM'. To validate the data, we need to look for rows where any of the following conditions are met, indicating an improper data situation :

• The start or end time for the new appointment falls between the start and end for another appointment
• The start time for the new appointment is before and the end time is after the end time for another appointment

If these two conditions are not met, the new row is acceptable. We will implement a simplistic example of assigning a doctor to an office. Clearly, other parameters that need to be considered, like office space, assistants, and so on, but I don’t want this section to be larger than the allotment of pages for the entire book. First, we create a table for the doctor and another to set appointments for the doctor.

 CREATE SCHEMA office;

 GO

 CREATE TABLE office.doctor

 (

        doctorId int NOT NULL CONSTRAINT PKOfficeDoctor PRIMARY KEY,

        doctorNumber char(5) NOT NULL CONSTRAINT AKOfficeDoctor_doctorNumber UNIQUE

 );

 CREATE TABLE office.appointment

 (

        appointmentId int NOT NULL CONSTRAINT PKOfficeAppointment PRIMARY KEY,

        --real situation would include room, patient, etc,

        doctorId int NOT NULL,

        startTime datetime2(0) NOT NULL, --precision to the second

        endTime datetime2(0) NOT NULL,

        CONSTRAINT AKOfficeAppointment_DoctorStartTime UNIQUE (doctorId,startTime),

        CONSTRAINT AKOfficeAppointment_StartBeforeEnd CHECK (startTime <= endTime)

 );

Next, we will add some data to our new table. The row with appointmentId value 5 will include a bad date range that overlaps another row for demonstration purposes:

 INSERT INTO office.doctor (doctorId, doctorNumber)

 VALUES (1,'00001'),(2,'00002'),

 INSERT INTO office.appointment

 VALUES (1,1,'20110712 14:00','20110712 14:59:59'),

         (2,1,'20110712 15:00','20110712 16:59:59'),

         (3,2,'20110712 8:00','20110712 11:59:59'),

         (4,2,'20110712 13:00','20110712 17:59:59'),

         (5,2,'20110712 14:00','20110712 14:59:59'), --offensive item for demo, conflicts

                                                     --with 4

Now, we run the following query to test the data :

 SELECT   appointment.appointmentId,

          Acheck.appointmentId as conflictingAppointmentId

 FROM     office.appointment

          JOIN office.appointment as ACheck

              ON appointment.doctorId = ACheck.doctorId

          /*1*/ and appointment.appointmentId <> ACheck.appointmentId

          /*2*/ and (Appointment.startTime between Acheck.startTime and Acheck.endTime

          /*3*/ or Appointment.endTime between Acheck.startTime and Acheck.endTime

          /*4*/ or (appointment.startTime < Acheck.startTime and appointment.endTime > Acheck.endTime));

In this query, I have highlighted four points:

1. In the join, we have to make sure that we don’t compare the current row to itself, because an appointment will always overlap itself.
2. Here, we check to see if the startTime is between the start and end, inclusive of the actual values.
3. Same as 2 for the endTime.
4. Finally, we check to see if any appointment is engulfing another.

Running the query, we see that

The interesting part of these results is that you will always get a pair of offending rows, because if one row is offending in one way, like starting before and after another appointment, the conflicting row will have a start and end time between the first appointment’s time. This won’t a problem, but the shared blame can make the results more interesting to deal with.

Next, we remove the bad row for now:

 DELETE FROM office.appointment where AppointmentId = 5;

We will now implement a trigger (using the template as defined in Appendix B and used in previous chapters) that will check for this condition based on the values in new rows being inserted or updated. There’s no need to check the deletion, because all a delete operation can do is help the situation. Note that the basis of this trigger is the query we used previously to check for bad values:

 CREATE TRIGGER office.appointment$insertAndUpdate Trigger

 ON office.appointment

 AFTER UPDATE, INSERT AS

 BEGIN

  SET NOCOUNT ON;

  SET ROWCOUNT 0; --in case the client has modified the rowcount

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  DECLARE @msg varchar(2000), --used to hold the error message

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

       @rowsAffected int = (SELECT COUNT(*) FROM inserted);

 --         @rowsAffected int = (SELECT COUNT(*) FROM deleted);

 --no need to continue on if no rows affected

 IF @rowsAffected = 0 RETURN;

 BEGIN TRY

     --[validation section]

  IF UPDATE(startTime) or UPDATE(endTime) or UPDATE(doctorId)

     BEGIN

     IF EXISTS ( SELECT *

                 FROM office.appointment

                    JOIN office.appointment as ACheck

                    on appointment.doctorId = ACheck.doctorId

                 AND appointment.appointmentId <> ACheck.appointmentId

                 AND (Appointment.startTime between Acheck.startTime

                 AND Acheck.endTime

                 OR Appointment.endTime between Acheck.startTime

                 AND Acheck.endTime

                 OR (appointment.startTime < Acheck.startTime

                 AND appointment.endTime > Acheck.endTime))

                WHERE EXISTS (SELECT *

                   FROM inserted

                   WHERE inserted.doctorId = Acheck.doctorId))

  BEGIN

  IF @rowsAffected = 1

  SELECT @msg = 'Appointment for doctor ' + doctorNumber +

                'overlapped existing appointment'

  FROM inserted

       JOIN office.doctor

       ON inserted.doctorId = doctor.doctorId;

  ELSE

  SELECT @msg = 'One of the rows caused an overlapping ' +

                'appointment time for a doctor';

     THROW 50000,@msg,16;

       END

  END

  --[modification section]

  END TRY

  BEGIN CATCH

  IF @@trancount > 0

   ROLLBACK TRANSACTION;

  THROW; --will halt the batch or be caught by the caller's catch block

 END CATCH

END;

GO

Next, as a refresher, check out the data that is in the table:

 SELECT *

 FROM office.appointment;

This returns (or at least it should, assuming you haven't deleted or added extra data)

This time, when we try to add an appointment for doctorId number 1:

 INSERT INTO office.appointment

 VALUES (5,1,'20110712 14:00','20110712 14:59:59'),

this first attempt is blocked because the row is an exact duplicate of the start time value. It might seem tricky, but the most common error is often trying to duplicate something accidentally.

Next, we check the case where the appointment fits wholly inside of another appointment :

 INSERT INTO office.appointment

 VALUES (5,1,'20110712 14:30','20110712 14:40:59'),

This fails and tells us the doctor for whom the failure occurred:

Then, we test for the case where the entire appointment engulfs another appointment:

 INSERT INTO office.appointment

 VALUES (5,1,'20110712 11:30','20110712 14:59:59'),

This quite obediently fails, just like the other case:

And, just to drive home the point of always testing your code extensively, you should always test the greater-than-one-row case, and in this case, I included rows for both doctors (this is starting to sound very Dr. Who–ish):

 INSERT into office.appointment

 VALUES(5,1,'20110712 11:30','20110712 14:59:59'),

       (6,2,'20110713 10:00','20110713 10:59:59'),

This time, it fails with our multirow error message:

Finally, add two rows that are safe to add:

 INSERT INTO office.appointment

 VALUES(5,1,'20110712 10:00','20110712 11:59:59'),

       (6,2,'20110713 10:00','20110713 10:59:59'),

This will (finally) work. Now, test failing an update operation:

 UPDATE  office.appointment

 SET     startTime = '20110712 15:30',

         endTime = '20110712 15:59:59'

 WHERE   appointmentId = 1;

which fails like it should.

If this seems like a lot of work, it kind of is. And in reality, whether or not you actually implement this solution in a trigger is going to be determined by exactly what it is you are doing. However, the techniques of checking range uniqueness can clearly be useful if only to check existing data is correct, because in some cases, what you may want to do is to let data exist in intermediate states that aren’t pristine and then write checks to “certify” that the data is correct before closing out a day. For a doctor’s office, this might involve prioritizing certain conditions above other appointments, so a checkup gets bumped for a surgery. Daily, a query may be executed by the administrative assistant at the close of the day to clear up any scheduling issues.

Approximate Uniqueness

The most difficult case of uniqueness is actually quite common, and it is usually the most critical to get right. It is also a topic far too big to cover with a coded example, because in reality, it is more of a political question than a technical one. For example, if two people call in to your company from the same phone number and say their name is Louis Davidson, are they the same person? Whether you can call them the same person is a very important decision and one that is based largely on the industry you are in, made especially tricky due to privacy laws (if you give one person who claims to be Louis Davidson the data of the real Louis Davidson, well, that just isn’t going to be good). I don’t talk much about privacy laws in this book, mostly because that subject is very messy, but also because dealing with privacy concerns is:

• Largely just an extension of the principles I have covered so far, and will cover in the next chapter on security.
• Widely varied by industry and type of data you need to store

The principles of privacy are part of what makes the process of identification so difficult. At one time, companies would just ask for a customer’s social security number and use that as identification in a very trusting manner. Of course, no sooner does some value become used widely by lots of organizations than it begins to be abused. So the goal of your design is to work at getting your customer to use a number to identify themselves to you. This customer number will be used as a login to the corporate web site, for the convenience card that is being used by so many businesses, and also likely on any correspondence. The problem is how to gather this information. When a person calls a bank or doctor, the staff member answering the call always asks some random questions to better identify the caller. For many companies, it is impossible to force the person to give information, so it is not always possible to force customers to uniquely identify themselves. You can entice them to identify themselves, such as by issuing a customer savings card, or you can just guess from bits of information that can gathered from a web browser, telephone number, and so on.

So the goal becomes to match people to the often-limited information they are willing to provide. Generally speaking, you can try to gather as much information as possible from people, such as

• Name
• Address, even partial
• Phone Number(s)
• Payment method
• E-mail address(es)

And so on. Then, depending on the industry, you determine levels of matching that work for you. Lots of methods and tools are available to you, from standardization of data to make direct matching possible, fuzzy matching, and even third-party tools that will help you with the matches. The key, of course, is that if you are going to send a message alerting of a sale to repeat customers, only a slight bit of a match might be necessary, but if you are sending personal information, like how much money they have spent, a very deterministic match ought to be done. Identification of multiple customers in your database that are actually the same is the holy grail of marketing, but it is achievable given you respect your customer’s privacy and use their data in a safe manner.

Data-Driven Design

One of the worst practices I see some programmers do is get in the habit of programming using specific keys to force a specific action. For example, they will get requirements that specify that for customer’s 1 and 2, we need to do action A, and customer 3 do action B. So they go in and code:

 IF @customerId in ('1', '2')

  Do ActionA(@customerId)

 ELSE IF @customerId in ('3')

  Do ActionB(@customerId)

It works, so they breathe a sigh of relief and move on. But the next day, they get a request that customer 4 should be treated in the same manner as customer 3. They don’t have time to do this request immediately because it requires a code change, which requires testing. So over the next month, they add'4' to the code, test it, deploy it, and claim it required 40 hours of programming time.

This is clearly not optimal, so the next best thing is to determine why we are doing ActionA or ActionB. We might determine that for CustomerType: 'Great', we do ActionA, but for 'Good', we do ActionB. So you could code

 IF @customerType = 'Great'

  Do ActionA(@customerId)

 ELSE IF @customerType = 'Good'

  Do ActionB(@customerId)

Now adding another customer to these groups is a fairly simple case. You set the customerType value to Great or Good, and one of these actions occurs in you code automatically. But (as you might hear on any infomercial) you can do better! The shortcoming in this design is now how do you change the treatment of good customers if you want to have them do ActionA temporarily? In some cases, the answer is to add to the definition of the CustomerType table and add a column to indicate what action to take. So you might code:

 CREATE TABLE CustomerType

 (

        CustomerType varchar(20) NOT NULL CONSTRAINT PKCustomerType PRIMARY KEY,

        ActionType char(1) NOT NULL CONSTRAINT CHKCustomerType_ActionType_Domain

                                    CHECK (CustomerType in ('A','B'))

 );

Now, the treatment of this CustomerType can be set at any time to whatever the user decides. The only time you may need to change code (requiring testing, downtime, etc.) is if you need to change what an action means or add a new one. Adding different types of customers, or even changing existing ones would be a nonbreaking change, so no testing is required.

The basic goal should be that the structure of data should represent the requirements, so rules are enforced by varying data, not by having to hard-code special cases. In our previous example, you could create an override at the customer level by adding the ActionType to the customer. Flexibility at the code level is ultra important, particularly to your support staff. In the end, the goal of a design should be that changing configuration should not require code changes, so create attributes that will allow you to configure your data and usage.

Hierarchies

Hierarchies are a peculiar topic in relational databases. Hierarchies happen everywhere in the “real” world, starting with a family tree, corporation organizational charts, species charts, and parts breakdowns. Even the Lego example from earlier in this chapter, if modeled to completion, would include a hierarchy for sets as sometimes sets are parts of other sets to create a complete bill of materials for any set. Structure-wise, there are two sorts of hierarchies you will face in the real world, a tree structure, where every item can have only one parent, and graphs, where you can have more than one parent in the structure.

The challenge is to implement hierarchies in such a manner that they are optimal for your needs, particularly as they relate to the operations of your OLTP database. In this section, we will quickly go over the two major methods for implementing hierarchies that are the most common for use in SQL Server:

• Self referencing/recursive relationship/adjacency list
• Using the HierarchyId datatype to implement a tree structure

Finally, we’ll take a brief architectural overview of a few other methods made popular by a couple of famous data architects; these methods can be a lot faster to use but require a lot more overhead to maintain, but sometimes, they’re just better when your hierarchy is static and you need to do a lot of processing or querying.

Generalization

Very often, it is tempting to design more tables than is truly necessary for the system needs, because we get excited and start to design perfection. For example, if you were designing a database to store information about camp activities, it might be tempting to have an individual table for the archery class, the swimming class, and so on, modeling with great detail the activities of each camp activity. If there were 50 activities at the camp, you might have 50 tables, plus a bunch of other tables to tie these 50 together. In the end though, while these tables may not exactly look the same, you will start to notice that every table is used for basically the same thing. Assign an instructor, sign up kids to attend, and add a description. Rather than the system being about each activity, and so needing a model each of the different activities as being different from one another, what you truly needed was to model was the abstraction of a camp activity.

In the end, the goal is to consider where you can combine foundationally similar tables into a single table, particularly when multiple tables are constantly being treated as one, as you would have to do with the 50 camp activity tables, particularly to make sure kids weren’t signing up their friends for every other session just to be funny.

During design, it is useful to look for similarities in utilization, columns, and so on, and consider collapsing multiple tables into one, ending up with a generalization/abstraction of what is truly needed to be modeled. Clearly, however, the biggest problem here is that sometimes you do need to store different information about some of the things your original tables were modeling. For example, if you needed special information about the snorkeling class, you might lose that if you just created one activity abstraction, and heaven knows the goal is not to end up with a table with 200 columns all prefixed with what ought to have been a table in the first place.

In those cases, you can consider using a subclassed entity for certain entities. Take the camp activity model. We include the generalized table for the generic CampActivity , which is where you would associate student and teachers who don’t need special training, and in the subclassed tables, you would include specific information about the snorkeling and archery classes, likely along with the teachers who meet specific criteria, as shown in Figure 8-15.

As a coded example, we will look at a home inventory system. You have various types of stuff in your house, and you clearly want to inventory everything or at least everything valuable. So would you simply design a table for each type of item? That seems like too much trouble, because most everything you will simply have a description, picture, value, and a receipt. On the other hand, a single table, generalizing all of the items in your house down to a single list seems like it might not be enough for items that you need specific information about, like appraisals and serial numbers.

For example, some jewelry probably ought to be appraised and have the appraisal listed. Electronics and appliances ought to have brand, model, and alternatively, serial numbers captured. So the goal is to generalize a design to the level where you have a basic list of the home inventory that you can list, but you can also print a list of jewelry alone with extra detail or electronics with their information.

So we implement the database as such. First, we create the generic table that holds generic item descriptions:

 CREATE SCHEMA Inventory;

 GO

 CREATE TABLE Inventory.Item

 (

         ItemId  int NOT NULL IDENTITY CONSTRAINT PKInventoryItem PRIMARY KEY,

         Name  varchar(30) NOT NULL CONSTRAINT AKInventoryItemName UNIQUE,

         Type  varchar(15) NOT NULL,

         Color  varchar(15) NOT NULL,

         Description varchar(100) NOT NULL,

         ApproximateValue numeric(12,2) NULL,

         ReceiptImage varbinary(max) NULL,

         PhotographicImage varbinary(max) NULL

 );

I included two columns for holding an image of the receipt as well as a picture of the item. Like we discussed in the previous section, you might want to use a filetable construct to just allow various electronic items to be associated with this data, but it would probably be sufficient to simply have a picture of the receipt and the item minimally attached to the row for easy use. In the sample data, I always load the data with a simple hex value of 0x001 as a placeholder:

 INSERT  INTO Inventory.Item

 VALUES  ('Den Couch','Furniture','Blue','Blue plaid couch, seats 4',450.00,0x001,0x001),

         ('Den Ottoman','Furniture','Blue','Blue plaid ottoman that goes with couch',

         150.00,0x001,0x001),

         ('40 Inch Sorny TV','Electronics','Black',

         '40 Inch Sorny TV, Model R2D12, Serial Number XD49292',

         800,0x001,0x001),

         ('29 Inch JQC TV','Electronics','Black','29 Inch JQC CRTVX29 TV',800,0x001,0x001),

         ('Mom''s Pearl Necklace','Jewelery','White',

         'Appraised for $1300 in June of 2003. 30 inch necklace, was Mom''s',

         1300,0x001,0x001);

Checking out the data using the following query:

 SELECT  Name, Type, Description

 FROM    Inventory.Item;

We see that we have a good little system, though data isn’t really organized how we need it, because in realistic usage, we will probably need some of the specific data from the descriptions:

At this point, we look at our data and reconsider the design. The two pieces of furniture are fine. We have a picture and a brief description. For the other two items, however, using the data becomes trickier. For electronics, the insurance company is going to want model and serial number for each, but the two TV entries use different formats, and one of them doesn’t capture the serial number. Did they forget to capture it? Or does it not exist?

So, we add subclasses for cases where we need to have more information, to help guide the user as to how to enter data:

 CREATE TABLE Inventory.JeweleryItem

 (

       ItemId int CONSTRAINT PKInventoryJewleryItem PRIMARY KEY

         CONSTRAINT FKInventoryJewleryItem$Extends$InventoryItem

               REFERENCES Inventory.Item(ItemId),

        QualityLevel      varchar(10)NOT NULL,

        AppraiserName     varchar(100) NULL,

        AppraisalValue    numeric(12,2) NULL,

        AppraisalYear     char(4) NULL

 );

 GO

 CREATE TABLE Inventory.ElectronicItem

 (

       ItemId int CONSTRAINT PKInventoryElectronicItem PRIMARY KEY

       CONSTRAINT FKInventoryElectronicItem$Extends$InventoryItem

             REFERENCES Inventory.Item(ItemId),

       BrandName varchar(20) NOT NULL,

       ModelNumber varchar(20) NOT NULL,

       SerialNumber varchar(20) NULL

 );

Now, we adjust the data in the tables to have names that are meaningful to the family, but we can create views of the data that have more or less technical information to provide to other people—first, the two TVs. Note that we still don’t have a serial number, but now, it would be simple to find electronics that have no serial number to tell us that we need to get one:

 UPDATE      Inventory.Item

 SET         Description = '40 Inch TV'

 WHERE       Name = '40 Inch Sorny TV';

 GO

 INSERT      INTO Inventory.ElectronicItem (ItemId, BrandName, ModelNumber, SerialNumber)

 SELECT      ItemId, 'Sorny','R2D12','XD49393'

 FROM        Inventory.Item

 WHERE       Name = '40 Inch Sorny TV';

 GO

 UPDATE      Inventory.Item

 SET         Description = '29 Inch TV'

 WHERE       Name = '29 Inch JQC TV';

 GO

 INSERT      INTO Inventory.ElectronicItem(ItemId, BrandName, ModelNumber, SerialNumber)

 SELECT      ItemId, 'JVC','CRTVX29',NULL

 FROM        Inventory.Item

 WHERE       Name = '29 Inch JQC TV';

Finally, we do the same for the jewelry items, adding the appraisal value from the text.

 UPDATE      Inventory.Item

 SET         Description = '30 Inch Pearl Neclace'

 WHERE       Name = 'Mom''s Pearl Necklace';

 GO

 INSERT      INTO Inventory.JeweleryItem (ItemId, QualityLevel, AppraiserName, AppraisalValue,AppraisalYear)

 SELECT      ItemId, 'Fine','Joey Appraiser',1300,'2003'

 FROM        Inventory.Item

 WHERE       Name = 'Mom''s Pearl Necklace';

Looking at the data now, you see the more generic list with names that are more specifically for the person maintaining the list:

 SELECT      Name, Type, Description

 FROM        Inventory.Item;

This returns

And to see specific electronics items with their information, you can use a query such as this, with an inner join to the parent table to get the basic nonspecific information:

 SELECTItem.Name, ElectronicItem.BrandName, ElectronicItem.ModelNumber, ElectronicItem.SerialNumber

 FROMInventory.ElectronicItem

  JOIN Inventory.Item

  ON Item.ItemId = ElectronicItem.ItemId;

This returns

Finally, it is also quite common that you may want to see a complete inventory with the specific information, since this is truly the natural way you think of the data and why the typical designer will design the table in a single table no matter what. We return an extended description column this time by formatting the data based on the type of row:

 SELECT Name, Description,

        CASE Type

          WHEN 'Electronics'

            THEN 'Brand:' + COALESCE(BrandName,'_______') +

             ' Model:' + COALESCE(ModelNumber,'________') +

             ' SerialNumber:' + COALESCE(SerialNumber,'_______')

       WHEN 'Jewelery'

         THEN 'QualityLevel:' + QualityLevel +

           ' Appraiser:' + COALESCE(AppraiserName,'_______') +

           ' AppraisalValue:' +COALESCE(Cast(AppraisalValue as varchar(20)),'_______')

           +' AppraisalYear:' + COALESCE(AppraisalYear,'____')

          ELSE '' END as ExtendedDescription

 FROM Inventory.Item --outer joins because every item will only have one of these if any

     LEFT OUTER JOIN Inventory.ElectronicItem

        ON Item.ItemId = ElectronicItem.ItemId

     LEFT OUTER JOIN Inventory.JeweleryItem

        ON Item.ItemId = JeweleryItem.ItemId;

This returns a formatted description:

The point of this section on generalization really goes back to the basic precepts that you design for the user’s needs. If we had created a table per type of item in the house: Inventory.Lamp, Inventory.ClothesHanger, and so on, the process of normalization gets the blame. But the truth is, if you really listen to the user’s needs and model them correctly, you may never need to consider generalizing your objects. However, it is still a good thing to consider, looking for commonality among the objects in your database looking for cases where you could get away with less tables rather than more.

Storing User-Specified Data

One of the common problems that has no comfortable solution is giving users a method to expand the catalog of values they can store without losing control of the database design. The biggest issue is the integrity of the data that they want to store in this database, in that it is very rare that you want to store data and not use it to make decisions. In this section, I will explore a couple of the common methods for doing expanding the data catalog by the end user.

As I have tried to make clear throughout the rest of the book so far, relational tables are not meant to be flexible. SQL Server code is not really meant to be overly flexible. T-SQL as a language is not made for flexibility (at least not from the standpoint of producing reliable databases that produce expected results and producing good performance while protecting the quality of the data, which I have said many times is almost always the most important thing).

Unfortunately, reality is that users want flexibility, and frankly, you can’t tell users that they can’t get what they want, when they want it, and in the form they want it. As an architect, I want to give the users what they want, within the confines of reality and sensibility, so it is necessary to ascertain some method of giving the user the flexibility they demand, along with methods to deal with this data in a manner that feels good to them. The technique used to solve this problem is pretty simple. It requires the design to be flexible enough to morph to the needs of the user, without the intervention of a database programmer manually changing the catalog. But with all problems, there are generally a couple of solutions that can be used to improve them.

The methods I will demonstrate are as follows:

• Big old list of generic columns
• Entity-attribute-value (EAV)
• Adding columns to the table, likely using sparse columns

The last time I had this type of need was to gather the properties on networking equipment. Each router, modem, and so on for a network has various properties (and hundreds or thousands of them at that). For this section, I will present this example as three different examples.

The basis of this example will be a simple table called Equipment . It will have a surrogate key and a tag that will identify it. It is created using the following code:

 CREATE SCHEMA Hardware;

 GO

 CREATE TABLE Hardware.Equipment

 (

     EquipmentId int NOT NULL

        CONSTRAINT PKHardwareEquipment PRIMARY KEY,

     EquipmentTag varchar(10) NOT NULL

        CONSTRAINT AKHardwareEquipment UNIQUE,

     EquipmentType varchar(10)

 );

 GO

 INSERT   INTO Hardware.Equipment

 VALUES   (1,'CLAWHAMMER','Hammer'),

          (2,'HANDSAW','Saw'),

          (3,'POWERDRILL','PowerTool'),

By this point in this book, you should know that this is not how the whole table would look in the actual solutions, but these three columns will give you enough to build an example from.

Undecipherable Data

One of the most annoying things when dealing with a database designed by a typical programmer is undecipherable values. Code such as WHERE status = 1 will pepper the code you no doubt discover using profiler, and you as the data developer end up scratching your head in wonderment as to what 5 represents. Of course, the reason for this is that the developers don’t think of the database as a primary data resource, rather they think of the database as simply the place where they hold state for their objects.

Of course, in their code, they are probably doing a decent job of presenting the meaning of the values in their coding. They aren’t actually dealing with a bunch of numbers in their code; they have a constant structure, such as

 CONST (CONST_Active = 1, CONST_Inactive = 0);

So the code they are using to generate the code make sense because they have said "WHERE status = " & CONST_Active. This is clear in the usage but not clear at the database level (where the values are actually seen and used by everyone else!). From a database standpoint, we have a few possibilities:

• Use descriptive values such as “Active” and “Inactive” directly. This makes the data more decipherable but doesn’t provide a domain of possible values. If you have no inactive values, you will not know about its existence at the database
• Create tables to implement a domain. Have a table with all possible values.

For the latter, your table could use the descriptive values as the domain, or you can use the integer values that the programmer likes as well. Yes, there will be double definitions of the values (one in the table, one in the constant declaration), but since domains such as this rarely change, it is generally not a terrible issue. The principles I tend to try to design by follow:

• Only have values that can be deciphered using the database:
  • Foreign key to a lookup table
  • Human readable values with no expansion in CASE expressions.
  • No bitmasks! (We are not writing machine code!)
• Don’t be afraid to have lots of small tables. Joins generally cost a lot less than the time needed to decipher a value, measured in programmer time, ETL time, and end user frustration.

One-Size-Fits-All Key Domain

Relational databases are based on the fundamental idea that every object represents one and only one thing. There should never be any doubt as to what a piece of data refers. By tracing through the relationships, from column name to table name to primary key, it should be easy to examine the relationships and know exactly what a piece of data means.

However, oftentimes, it will seem reasonable that, since domain type data looks the same in almost every table, creating just one such table and reusing it in multiple locations would be a great idea. This is an idea from people who are architecting a relational database who don’t really understand relational database architecture (me included, early in my career)—that the more tables there are, the more complex the design will be. So, conversely, condensing multiple tables into a single catchall table should simplify the design, right? That sounds logical, but at one time giving Pauly Shore the lead in a movie sounded like a good idea too.

As an example, consider that I am building a database to store customers and orders. I need domain values for the following:

• Customer credit status
• Customer type
• Invoice status
• Invoice line item back order status
• Invoice line item ship via carrier

Why not just use one generic table to hold these domains, as shown in Figure 8-18.

I agree with you if you are thinking that this seems like a very clean way to implement this from a coding-only standpoint. The problem from a relational coding/implementation standpoint is that it is just not natural to work with in SQL. In many cases, the person who does this does not even think about SQL access. The data in GenericDomain is most likely read into cache in the application and never queried again. Unfortunately, however, this data will need to be used when the data is reported on. For example, say the report writer wants to get the domain values for the Customer table:

 SELECT *

 FROM Customer

    JOIN GenericDomain as CustomerType

      ON Customer.CustomerTypeId = CustomerType.GenericDomainId

       AND CustomerType.RelatedToTable = 'Customer'

       AND CustomerType.RelatedToColumn = 'CustomerTypeId'

  JOIN GenericDomain as CreditStatus

      ON Customer.CreditStatusId = CreditStatus.GenericDomainId

       AND CreditStatus.RelatedToTable = 'Customer'

       AND CreditStatus.RelatedToColumn = ' CreditStatusId'

As you can see, this is far from being a natural operation in SQL. It comes down to the problem of mixing apples with oranges. When you want to make apple pie, you have to strain out only apples so you don’t get them mixed. At first glance, domain tables are just an abstract concept of a container that holds text. And from an implementation-centric standpoint, this is quite true, but it is not the correct way to build a database because we never want to mix the rows together as the same thing ever in a query. The litmus test is if you will never use a domain except for one table, it should have its own table. You can tell this pretty easily usually, because you will need to specify a table and/or column name to figure out the rows that are applicable.

In a database, the process of normalization as a means of breaking down and isolating data takes every table to the point where one table represents one type of thing and one row represents the existence of one of those things. Every independent domain of values should be thought of as a distinctly different thing from all the other domains (unless it is not, in which case one table will suffice). So, what you do, in essence, is normalize the data over and over on each usage, spreading the work out over time, rather than doing the task once and getting it over with.

Instead of a single table for all domains, you should model it as shown in Figure 8-19.

That looks harder to do, right? Well, it is initially (like for the 5 or 10 minutes it takes to create a few tables). Frankly, it took me longer to flesh out the example tables. The fact is, there are quite a few tremendous gains to be had:

• Using the data in a query is much Clearer:

   SELECT *

   FROM Customer

        JOIN CustomerType

           ON Customer.CustomerTypeId = CustomerType.CustomerTypeId

        JOIN CreditStatus

           ON Customer.CreditStatusId = CreditStatus.CreditStatusId

• Data can be validated using simple foreign key constraints: This was something not feasible for the one-table solution. Now, validation needs to be in triggers or just managed solely by the application.
• Expandability and control: If it turns out that you need to keep more information in your domain row, it is as simple as adding a column or two. For example, if you have a domain of shipping carriers, you might define a ShipViaCarrier in your master domain table. In its basic form, you would get only one column for a value for the user to choose. But if you wanted to have more information—such as a long name for reports, as in “United Parcel Service”; a description; and some form of indication when to use this carrier—you would be forced to implement a table and change all the references to the domain values.
• Performance considerations: All of the smaller domain tables will fit on a single page or disk. This ensures a single read (and likely a single page in cache). If the other case, you might have your domain table spread across many pages, unless you cluster on the referring table name, which then could cause it to be more costly to use a nonclustered index if you have many values. In a very large table, it could get to the point where a scan of a larger domain table could get costly where only a very small number of rows is needed.
• You can still make the data look like one table for the application: There is nothing precluding developers from building a caching mechanism that melds together all the individual tables to populate the cache and use the data however they need it for the application. With some clever use of extended properties, this could be as simple as adding a value to a property and letting a dynamic SQL procedure return all the data. A common concern that developers have is that now they will need 50 editors instead of one. You can still have one editor for all rows, because most domain tables will likely have the same base structure/usage, and if they don’t, you will already need to create a new table or do some sort of hokey usage to make the single table design work.

Returning to the basics of design, every table should represent one and only one thing. When you see a column in a table by itself, there should be no question as to what it means, and you certainly shouldn’t need to go to the table and figure out what the meaning of a value is.

Some tools that implement an object-oriented view of a design tend to use this frequently, because it’s easy to implement tables such as this and use a cached object. One table means one set of methods instead of hundreds of different methods for hundreds of different objects—er, tables. (The fact that it stinks when you go to use it in the database for queries is of little consequence, because generally systems like this don’t, at least initially, intend for you to go into the database and do queries, except through special interfaces that take care of this situation for you.)

Generic Key References

In an ideal situation, one table is related to another via a key. However, because the structures in SQL Server don’t require constraints or any enforcement, this can lead to interesting relationships occurring. What I am referring to here is the case where you have a table that has a primary key that can actually be a value from several different tables, instead of just one.

For example, consider the case where you have several objects, all of which need a reference to one table. In our sample, say you have a customer relationship management system with SalesOrders and TroubleTickets (just these two to keep it simple, but in reality, you might have many objects in your database that will fit this scenario). Each of these objects has the need to store journal items, outlining the user’s contact with the customer (for example, in the case where you want to make sure not to overcommunicate with a customer!). You might logically draw it up like in Figure 8-20.

You might initially consider modeling it like a classic subtype relationship, but it really doesn’t fit that mold because you probably can have more than one journal entry per sales order and trouble ticket. Fair enough, each of these relationships is 1–N, where N is between 0 and infinity (though the customer with infinite journal entries must really hate you). Having all parents relate to the same column is a possible solution to the problem but not a very favorable one. For our table in this scenario, we build something like this:

 CREATE TABLE SalesOrder

 (

       SalesOrderId <int or uniqueidentifier> PRIMARY KEY,

       <other columns>

 );

 CREATE TABLE TroubleTicket

 (

       TroubleTicketId <int or uniqueidentifier> PRIMARY KEY,

       <other columns>

 );

 CREATE TABLE JournalEntry

 (

       JournalEntryId <int or uniqueidentifier>,

       RelatedTableName sysname,

       PRIMARY KEY (JournalEntryId, RelatedTableName)

       <other columns>

 );

Now, to use this data, you have to indicate the table you want to join to, which is very much a unnatural way to do a join. You can use a universally unique GUID key so that all references to the data in the table are unique, eliminating the need for the specifically specified related table name. However, I find when this method is employed if the RelatedTableName is actually used, it is far clearer to the user what is happening.

A major concern with this method is that you cannot use constraints to enforce the relationships; you need either to use triggers or to trust the middle layers to validate data values, which definitely increases the costs of implementation/testing, since you have to verify that it works in all cases, which is something we trust for constraints; even triggers are implemented in one single location.

One reason this method is employed is that it is very easy to add references to the one table. You just put the key value and table name in there, and you are done. Unfortunately, for the people who have to use this for years and years to come, well, it would have just been easier to spend a bit longer and do some more work, because the generic relationship means that using a constraint is not possible to validate keys, leaving open the possibility of orphaned data.

A second way to do this that is marginally better is to just include keys from all tables, like this:

 CREATE TABLE JournalEntry

 (

       JournalEntryId <int or uniqueidentifier> PRIMARY KEY,

       SalesOrderId <int or uniqueidentifier> NULL REFERERENCES

       SalesOrder(SalesOrderId),

       TroubleTicketId <int or uniqueidentifier> NULL REFERERENCES

           TroubleTicket(TroubleTicketId),

       <other columns>

 );

This is better, in that now joins are clearer and the values are enforced by constraints, but now, you have one more problem (that I conveniently left out of the initial description). What if you need to store some information about the reason for the journal entry? For example, for an order, are you commenting in the journal for a cancelation notice?

There is also the matter of nor concerns, since normalization/usage concerns in that the related values doesn’t exactly relate to the key in the same way. It seems like a decent idea that one JournalEntry might relate to more than one SalesOrder or JournalEntry. So, the better idea is to model it more like Figure 8-21.

 CREATE TABLE JournalEntry

 (

      JournalEntryId <int or uniqueidentifier> PRIMARY KEY,

      <other columns>

 );

 CREATE TABLE SalesOrderJournalEntry

 (

      JournalEntryId <int or uniqueidentifier>

      REFERENCES JournalEntry(JournalId),

      SalesOrderId <int or uniqueidentifier>,

      REFERENCES SalesOrder(SalesOrderId),

      <SalesOrderSpecificColumns>

      PRIMARY KEY (JournalEntryId, SalesOrderId)

 );

 CREATE TABLE TroubleTicketJournalEntry

 (

      JournalEntryId <int or uniqueidentifier>

      REFERENCES JournalEntry(JournalId),

      TroubleTicketId <int or uniqueidentifier>,

      REFERENCES TroubleTicket (TroubleTicketId),

      <TroubleTicketSpecificColumns>

      PRIMARY KEY (JournalEntryId, SalesOrderId)

 );

Note that this database is far more self-documented as well. You can easily find the relationships between the tables and join on them. Yes, there are a few more tables, but that can play to your benefit as well in some scenarios, but most important, you can represent any data you need to represent, in any cardinality or combination of cardinalities needed. This is the goal in almost any design.

Overusing Unstructured Data

As much as I would like to deny it, or at least find some way to avoid it, people need to have unstructured notes to store various bits and pieces of information about their data. I will confess that a large number of the tables I have created in my career included some column that allowed users to insert whatever into. In the early days, it was a varchar(256) column, then varchar(8000) or text, and now varchar(max). It is not something that you can get away from, because users need this scratchpad just slightly more than Linus needs his security blanket. And it is not such a terrible practice, to be honest. What is the harm in letting the user have a place to note that the person has special requirements when you go out to lunch?

Nothing much, except that far too often what happens is that notes become a replacement for the types of stuff that I mentioned in the “User-Specified Data” section or, when this is a corporate application, the types of columns that you could go in and create in 10 minutes or about 2 days, including testing and deployment. Once the users do something once and particularly finds it useful, they will do it again. And they tell their buddies, “Hey, I have started using notes to indicate that the order needs processing. Saved me an hour yesterday .” Don’t get me wrong, I have nothing against users saving time, but in the end, everyone needs to work together.

See, if storing some value unstructured in a notes column saves the user any time at all (considering that, most likely, it will require a nonindexed search or a one-at-a-time manual search), just think what having a column in the database could do that can be easily manipulated, indexed, searched on, and oblivious to the spelling habits of the average human being. And what happens when a user decides that they can come up with a “better” way and practices change, or, worse, everyone has their own practices?

Probably the most common use of this I have seen that concerns me is contact notes. I have done this myself in the past, where you have a column that contains formatted text something like the following on a Customer table. Users can add new notes but usually are not allowed to go back and change the notes.

What a terrible waste of data. The proper solution would be to take this data that is being stored into this text column and apply the rigors of normalization to it. Clearly, in this example, you can see three “rows” of data, with at least three “columns.” So instead of having a Customer table with a ContactNotes column, implement the tables like this:

 CREATE TABLE Customer

 (

      CustomerId int CONSTRAINT PKCustomer PRIMARY KEY

     <other columns>

 );

 CREATE TABLE CustomerContactNotes

 (

      CustomerId int,

      NoteTime datetime,

      PRIMARY KEY (CustomerId, NoteTime),

      UserId datatype, --references the User table

      Notes varchar(max)

 );

You might even stretch this to the model we discussed earlier with the journal entries where the notes are a generic part of the system and can refer to the customer, multiple customers, and other objects in the database. This might even link to a reminder system to remind Stu to get back to Fred, and he would not now be jobless. Though one probably should have expected such out of a guy named Stu Pidd (ba boom ching).

Even using XML to store the notes in this structured manner would be an amazing improvement. You could then determine who entered the notes, what the day was, and what the notes were, and you could fashion a UI that allowed the users to add new fields to the XML, right on the fly. What a tremendous benefit to your users and, let’s face it, to the people who have to go in and answer questions like this, “How many times have we talked to this client by phone?”

The point of this section is simply this: educate your users. Give them a place to write the random note, but teach them that when they start to use notes to store the same specific sorts of things over and over, their jobs could be easier if you gave them a place to store their values that would be searchable, repeatable, and so on. Plus, never again would you have to write queries to “mine” information from notes.