General Table Design Question 2

[Auguy]

I have an order table that has several fields that store things like invoice date, post date, EDI date, etc. My question is (in general) should these types of fields that indicate the "State" of the order be in the order table or in a separate table? These fields are normally updated through procedures other than the normal editing of the record. I do have some concurrency issues that I deal with through the use of a reserved date/time field and it works quite well. Just looking for some guidelines and opinions here, and maybe a better method of dealing with this type of data.

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

This is a loaded question with a lot of "it depends" answers.

The obvious answer is... "If it ain't broke, don't fix it".

The biggest question is... is there a problem with the orders table that is making you consider a change? Is is slow? Are there a lot of rows in this table? Are you getting blocking conditions? How many Date columns do you have? Do you store time as well as date in these columns? Would a smallDateTime (or Date) data type work?

From a normalization perspective, having all the date columns in the same table is fine because each one represents another piece of data that depends on the primary key but not on each other.

On the other hand, if there are performance problems with this table, it's probably because it is "wide". A wide table is one that has a lot of columns with many of them being large strings. If the table is wide, and these dates are accessed infrequently, then it may make sense to separate them in to another table. By "infrequently", I mean... suppose you have 20 procedures that touch the Orders table, how many of those procedures reference these columns.





-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George, I sort of knew it was a loaded question, but your thoughts expand upon some of my own and were exactly what I was looking for. It's a fairly wide table, but I'm moving a lot of delivery information as well as the note fields to other tables to be able to support multiple delivery locations, etc. There are no great problems now and the table is fairly small at about 500K records and only growing about 50K a year. Just thought that if I'm going to make these other changes, thne now is the time to clean anything else up too.

Auguy
Sylvania/Toledo Ohio

 

[trevil620]

As George mentioned, there is no "one size fits all" answer ... and touched on "normalization". As for your question "fields that indicate the "State" of the order be in the order table or in a separate table", some people find it easier to comprehend a flat table structure which contains all columns needed for that particular record. I try to create owner/member tables where it seems appropriate, thus creating a relationship that gets enforced. One simple example of the benefit, using your 'multiple delivery location' scenario, may be that location 1 has delivery instructions that say "Mail it", while location 2 may have 10 pages of conditions if snow, hot, Tuesday... that you certainly don't want to store thousands of times; thus I would not embed that field in your standard table. However, some people take that approach too far which can make life miserable also. My view is "Limit redundancy" not "Eliminate redundancy".

Sometimes computer problems are like genealogy... The answer to one problem leads to two more!

 

[Olaf Doschke]

A lightweight order history table with order state and date/time of change would perhaps make it easy to list the history of the order, but if you want to find orders at a certain stage or above, such queries get more complicated with such a separate history, than if having all date fields in the order table. Especially if a state could also be skipped. You end up with EXISTS queries or such things. The advantage of course is, you can easily extend further individual states, but do you need that? If your workflow often changes such a structure would help datawise, but alongside of that you'd need application changes, too, most probably.

What happens if an important state is missing? That's perhaps an easy case, comparing to a NULL value in the structure you have now, but what about double states in the separated table? Which date is correct, then? Of course you can only have one date in your order table structure now, and if a date would be set double, you would find the last stored date in it. The order history would perhaps reveal such things you don't detect with a single field for that data, which may be good to know, but it may also cause new conflicts.

So overall it may cause more trouble than it's worth and it's only a good consideration, if you need more flexibility, eg many different workflows with the orders, if they are partly physical products, partly services, lending, or also downloads and you offer all that, eg you are amazon, then this would make sense.

Bye, Olaf.

 

[Auguy]

Olaf, thanks for the good points. It helps enforce what I was thinking. I already have a History table where I store the major changes to a record like status changes, order changes, shipped, etc. The user can the display this list at any time to show who made the change and the date/time of the change.

Auguy
Sylvania/Toledo Ohio