Normalisation - again !

[BeeJayEff]

I was browsing through a thread somewhere here when I came across an old discussion which set me thinking.

The poster had a table design which contained a number of dates - let's say they were SaleDate, DespatchDate and DeliveredDate in a tPurchases table (I cannot remember exactly, but I don't think the detail matters). The advice given was that, for normalisation purposes, the design should instead include a separate table called tDates, which would have three fields : a foreign key to tPurchases, a string identifying the type of date ("SaleDate", "DespatchDate", etc.) and the date value itself.

Now I don't see that as part of 1NF, 2NF or 3NF - and it seems to me to be adding design complexity for no benefit. Am I wrong ?

 

[Minty]

It seems unnecessary for most data models and an additional overhead to me.

If we take an simple order process structure for instance, every order header would have an order date, and the underlying order line(s) would have probably a picked date and a shipped date field. I can see no advantage to pushing those into a separate table. You could argue that those be put into a Order Status list but again it seems too much down the "Over Normalised" route in my simple mind.

I can see in certain data models it could be advantageous , a call logging system where many different types of date fields could be used, but these would be quite rare I think.

 

[pbaldy]

I have limo and taxi reservation/dispatch apps where I do both. In the reservation table I have date/time fields for when the call was taken, when the customer wants the car, and when the car has to leave the yard to get to the customer's on time. In a separate "normalized" table, I record other activity, like when the driver loads, unloads, etc (sort of an audit trail table).

Your fields sound like my first 3; static fields that every record will have. I'd leave them alone too.

 

[NauticalGent]

Emphatically agree with pbaldy's last sentence.

 

[gemma-the-husky]

it's a matter of taste, I think.

Not sure about SQL, but in Access there is an absolute maximum of 4000 bytes per record - so if you have a lot of text fields, you may hit this limit in a normalised system, and be forced to find a solution.

I tend to have a "System Constants" record with a number of system wide settings that rarely change.

From time to time I may find I need to add some more settings. I have taken to using a "vertically" designed structure as I can add new settings within the vertical table without need to redesign.

eg - if say you need to add other dates. "order date", "cancelled date" and so onm, it may be easier with the "dates" table.

If you need to collect multiple dates at the same time, then having the dates table can become inefficient, as you need multiple reads.

 

[Minty]

Size limits in SQL are quite generous (This is for 2008 R2);
https://msdn.microsoft.com/en-us/library/ms143432(v=sql.105).aspx

 

[The_Doc_Man]

We have a similar case in one of our database tables. I'll spare the details of the whole setup, but here's the similarity. We get notices from U.S. CyberCom regarding software vulnerabilities. We have hundreds of servers for which said vulnerabilities might apply.

In the "master" record for each individual notice, we have 3 dates - the arrival date, the internal report date, and the external report date. For the individual servers, the records that are the junction between the servers and the notices have individual action dates, but the master record holds dates that apply to everyone and to every server by providing common milestone/due dates. So in my design, I surely could have chosen to normalize those dates, but the careful analysis of their behavior made me choose otherwise. The questions were:

1. Do the dates change a lot once entered? Well, the "arrived" date is constant once entered; the internal report date (a reporting milestone) is constant; and the external report date is defined by CyberCom so we don't change IT either. So first consideration: The dates are essentially constant once entered. Therefore, enter them as part of the original notice and be done with it.

2. Do the dates potentially depend on anything else in the database? Answer: No. They depend ONLY on the individual notice entry's PK field. So that essentially blows away one of the normalization rules that usually drives table splits.

3. Can you ever do without any of them (i.e. "sparse" child table of dates)? Answer: No, so there is nothing to gain in supporting their possible absence. They should never be absent. So there is no reason to build an infrastructure that hides or glosses over their absence.

4. Would you ever record multiple event dates related to the notices? Answer: Yes, but in the junction tables, not in the parent. The tree dates are targets. The multiple dates are implementation dates per server. So the dates in the master record don't have the same purpose as the dates in the junction table. They functionally don't belong together.

Therefore, based on USAGE, my choice was to keep those 3 dates as part of the parent notice record rather than in a child table of dates. The other possible dates (used to show when we made the individual servers compliant with requirements) WERE moved into other tables.

In the final analysis, it is always the data that should drive the design. BeeJayEff, I would agree with you that if the dates in question are used as intrinsic properties associated with the object being described, they belong in that object's record and not in a child record. When faced with a similar situation, I made the same choice you would have.