From: Brian Selzer <>
Date: Tue, 08 Jan 2008 17:23:34 GMT
Message-ID: <qkOgj.86873$>

"Hugo Kornelis" <> wrote in message
> On Fri, 04 Jan 2008 14:50:55 GMT, Brian Selzer wrote:
>>Excuse me, but I don't think normalization has any bearing whatsoever.
>>with a fully normalized schema it may be that there can be missing
>>information. Even if there is one table per elementary fact type, there
>>still be missing information: the difference is that instead of a null
>>indicates that there should be a value but it just hasn't been supplied,
>>have to decide whether the absence of a row indicates that the attribute
>>does not apply or that it does apply but that a value just hasn't been
> Hi Brian,
> No, you don't have to decide that at all. There are two possibilities:
> 1) The business wants to store this fact if available, but if not they
> don't give a hoot why it's unavailable.
> 2) The business wants to store this fact if available, and if not they
> do care why it is unavailable.
> In the first case, you don't have to decide why a fact is absent bacause
> the business doesn't care about that reason.
> In the latter case, the reason for the fact's absence becomes a new
> proposition: "The age of person <PersonID> is not recorded in the
> database because of <reason>", which eventually ends up as a seperate
> column in the table - so instead of having to decide why the age of Aunt
> Maggie is not recorded, you just fetch the reason from this column.

I don't buy that explanation. Consider the following set of relations for tracking inventory (I've left off the warehouse and bin attributes from each relation for brevity.):

P {P#},                   PK: {P#}
PQ {P#, Q},           PK: {P#},        PQ[P#] IN P[P#]
PLQ {P#, L#, Q},   PK: {P#, L#},  PLQ[P#] IN P[P#]
PS {P#, S#},           PK: {P#, S#},  PS[P#] IN P[P#]
PLS {P#, L#, S#}, PK: {P#, S#}, PLS[P#] IN P[P#] Where P# is Part Number, L# is Lot Number, S# is Serial Number and Q is Quantity

PQ is for quantities for parts that are not tracked by lot number or serial number

PQL is for quantities for parts that are tracked by lot number only

PS is for serial numbers for parts that are tracked by serial number only

PLS is for lot numbers and serial numbers for parts that are tracked by both lot number and serial number.

Note that there is no quantity if there is a serial number, since the quantity can be determined by counting tuples.

Suppose that I know that the part with part number '111' and serial number '12345' should have a lot number, but I don't know what it is. Should I then insert the information that I have into PS? But wouldn't that indicate that part '111' is not being tracked by lot? What if there already is a tuple in PLS for a part with part number '111' and serial number '12344'? Could it then be determined whether part '111' was being tracked by serial only or by lot and serial? Now if PLS allowed L# to be null, I could insert the information that I have into PLS, and it would be clear that there is a value for L# for the part with part number '111' and serial number '12345', but that that value hasn't been supplied--in other words, that part '111' is being tracked by both lot and serial, even though the lot number hadn't been supplied.

(I guess I /could/ instead have a separate relation,

PLSnoL {P#, S#}, PK: {P#, S#} PLSnoL[P#] IN P[P#]

that would permit the part and serial numbers to be recorded in the event that the lot number hadn't been supplied..., but wouldn't that run afoul of POOD, since PLSnoL has the same heading as PS? Or I could add an boolean indicator to PS that would indicate whether there should be a tuple in PLS, and change PLS[P#] IN P[P#] to PLS[P#, S#] IN PS[P#, S#]..., but now I must have a tuple with an indicator in PS for every tuple in PLS instead of an indicator in PLS only for those values that haven't been supplied.)

Suppose that there is a tuple in P for P# '123' but there is no tuple in PQ for P# '123'. Should I infer that P# '123' /is/ being tracked, and look for the quantity in PLQ, PS or PLS, or that P# '123' /isn't/ being tracked, but that the quantity just hasn't been supplied? What if I don't find a tuple in any of PQ, PLQ, PS, PLS? Can I then infer that P# '123' is being tracked by serial number and maybe by lot number too, or should I conclude that the database is inconsistent?

>> Splitting the tables only alters the indicator from an explicit
>>one to an implicit one, and information is lost in the process.
> Information that the business didn't want to track in the first place.
> Unless we assume that the original model was incomplete.
> (snip)
>> the database no longer directly represents reality (as
>>is the case when using an explicit indicator), but rather what is known to
>>be true about reality.
> No database can ever directly represent reality. Since a database always
> relies on some external process or operator to feed it with data, it can
> never represent anything but what is known (to the database) to be true
> about reality.
> And even that only under the assumption that the data entered into the
> database can only be assumed to be true.
> Best, Hugo
Received on Tue Jan 08 2008 - 18:23:34 CET

Original text of this message