Use of nulls

[Paolo]

Something just dawned on me and I was wondering if anyone could help
me. I am designing a work breakdown structure database. I have a
table with several optional fields. If these fields are null, I know
that that the record is just an element. If they are not null I know
that the record is a work package. Does this knowledge disallow me
from having a separate field defining the record as a work package or
an element? My gut instinct is that it would in that it violates the
rule of field independence.

Thanks in advance,

(e-mail address removed)

 

[BruceM]

My friend Jamie is interested in the theoretical aspect to a greater extent
than am I, but I will echo his question in asking if a check box or some
such to designate the record as element or work package serves any purpose
beyond what can be served by the presence or absence of values in the
fields. You can use the form's After Update event, or the After Update
event of a control to label the record as element or work package if that
helps. You can also use the optional fields to set up queries. A
calculated query field could be set up along the lines of:
PackageType: IIf(IsNull([Field1]) AND IsNull([Field2]),"Element","Work
Package")
This field could be used for grouping records in a report, or any number of
other purposes.
Is there a specific problem you are trying to solve?

 

[Brendan Reynolds]

Including the additional field would break the rules of normalization,
certainly, as the value would be dependant on fields that do not form part
of the primary key. Whether this denormalization could be justified is a
much more open-ended question. You could, as others have suggested, use an
expression in place of the field. I would probably do it something like this
....

SELECT Table1.*, [MayBeNull1] Is Null And [MayBeNull2] Is Null And
[MayBeNull3] Is Null AS Element
FROM Table1;

The down-side is that you can not index an expression, but then the usual
recommendation is not to index fields that contain a vary small range of
values anyway (though I have heard some knowledgeable and experienced
database designers argue that there can be exceptions to that rule too).

Personally, I would include the field only as a last resort, if I were
encountering serious problems that I was unable to resolve without it.

 

[Paolo]

Hi Bruce (and Jamie),

No there is no specific problem I am trying to solve. I am, perhaps,
being too vigilant about normalization. For now I'll just leave out
the separate field denoting the record as work package or element and
use a query instead to search out nulls to identify the record that
way.

(e-mail address removed)

My friend Jamie is interested in the theoretical aspect to a greater extent
than am I, but I will echo his question in asking if a check box or some
such to designate the record as element or work package serves any purpose
beyond what can be served by the presence or absence of values in the
fields. You can use the form's After Update event, or the After Update
event of a control to label the record as element or work package if that
helps. You can also use the optional fields to set up queries. A
calculated query field could be set up along the lines of:
PackageType: IIf(IsNull([Field1]) AND IsNull([Field2]),"Element","Work
Package")
This field could be used for grouping records in a report, or any number of
other purposes.
Is there a specific problem you are trying to solve?

Something just dawned on me and I was wondering if anyone could help
me. I am designing a work breakdown structure database. I have a
table with several optional fields. If these fields are null, I know
that that the record is just an element. If they are not null I know
that the record is a work package. Does this knowledge disallow me
from having a separate field defining the record as a work package or
an element? My gut instinct is that it would in that it violates the
rule of field independence.

Thanks in advance,

(e-mail address removed)

 

[Paolo]

Hi Brendan,

Yes I will follow your advice. Thanks a million.

(e-mail address removed)

Hi Bruce (and Jamie),

No there is no specific problem I am trying to solve. I am, perhaps,
being too vigilant about normalization. For now I'll just leave out
the separate field denoting the record as work package or element and
use a query instead to search out nulls to identify the record that
way.

(e-mail address removed)

My friend Jamie is interested in the theoretical aspect to a greater extent
than am I, but I will echo his question in asking if a check box or some
such to designate the record as element or work package serves any purpose
beyond what can be served by the presence or absence of values in the
fields. You can use the form's After Update event, or the After Update
event of a control to label the record as element or work package if that
helps. You can also use the optional fields to set up queries. A
calculated query field could be set up along the lines of:
PackageType: IIf(IsNull([Field1]) AND IsNull([Field2]),"Element","Work
Package")
This field could be used for grouping records in a report, or any number of
other purposes.
Is there a specific problem you are trying to solve?

Something just dawned on me and I was wondering if anyone could help
me. I am designing a work breakdown structure database. I have a
table with several optional fields. If these fields are null, I know
that that the record is just an element. If they are not null I know
that the record is a work package. Does this knowledge disallow me
from having a separate field defining the record as a work package or
an element? My gut instinct is that it would in that it violates the
rule of field independence.

Thanks in advance,

(e-mail address removed)