Re: Joins with nulls

Bob Badour wrote:
>
>
> I disagree. In more involved examples where one allows the user to
> enter partial information, one requires a more involved design. NULL
> causes far more problems than it claims to solve. If the database
> knows of no correct answers to my question, I am happy to receive an
> empty set of answers. Again, no NULL required.
>
>
Bob:

Your entire argument can be summarized as: That can't happen so you are wrong.

I'll try one more time to show my point. I'll use a real situation.

The data consists of the performance characteristics of a piece of industrial equipment called a flotation cell. The operator (let's call him Homer) must record at set intervals:

Date sample was taken, Time sample was taken, Froth depth at the time sample was taken.

There are clear functional dependancies with each of these and they should all go into a single table. The DDL for such a table is relatively trivial.

Other tables would hold related information, such as sample results, product destinations, PLC data etc.

For any particular set of data, Homer may:

Forget the time the sample was taken (or neglect to note it) and / or Forget to measure (or neglect to note) the froth depth.

So in a situation with three bits of data, two of which are required for the candidate key, I am faced with up to two pieces of missing information, one of which results in a candidate key that is incomplete. Please show me this "more involved design" that will allow me to have a table(s) without a blank. Note that 0 is a valid froth depth and entering a 0 when it isn't would be wrong and could lead to actions detrimental to the process. Also note that other information, such as analytical results from the sample, must be recorded, whether or not this particular table is complete. Also note that mathematical work is performed on the froth depth data. If there is missing information, averages etc. cannot be impacted.

JE Received on Sun Nov 24 2002 - 14:56:33 CET