One to One Relationships

mnature wrote:
One way to handle a lot of dates is to put them all into their own table.

tbl_Dates
DateID (PK)
RecordedDate
DateDescriptorID (FK)

Sorry but this is a fallacy. This design flaw is so famous it has a
name ('one true lookup table'), an acronym (OTLT) and an acronym
nickname (MUCK).

Basically, you are taking attributes from multiple domains and lumping
them together, merely because they are of the same data type.

From a practical viewpoint, it will make data integrity constraints
(SQL DDL) considerable harder to write e.g. how would you enforce the
business rule (law of nature) that fieldwork start date cannot occur
before business end date? Ditto queries (SQL DML).

For details see:

http://www.dbazine.com/ofinterest/oi-articles/celko22

Jamie.

--

This is precisely why I come here to post possible solutions for others. It
is not to try and help them, but to try and hone my own abilities. Getting
slapped upside the head for posting something stupid is an excellent learning
opportunity, for which I am quite grateful. So far, I believe I have
uncovered nearly every possible way of doing something wrong. Statistically
speaking, then, I must be getting closer to perfection.

However, other than "committing spreadsheet," I don't think I have had
another error that actually has a name and an acronym and a nickname. Plus,
it has been a while since someone had to jump in and tell me I was just plain
wrong. Thanks for the save, Jamie.

"Jamie Collins" wrote:

Sorry but this is a fallacy. This design flaw is so famous it has a
name ('one true lookup table'), an acronym (OTLT) and an acronym
nickname (MUCK).

Basically, you are taking attributes from multiple domains and lumping
them together, merely because they are of the same data type.

From a practical viewpoint, it will make data integrity constraints
(SQL DDL) considerable harder to write e.g. how would you enforce the
business rule (law of nature) that fieldwork start date cannot occur
before business end date? Ditto queries (SQL DML).

For details see:

http://www.dbazine.com/ofinterest/oi-articles/celko22

Jamie.

--

mnature wrote:
other than "committing spreadsheet," I don't think I have had
another error that actually has a name and an acronym and a nickname.

Another design flaw to be aware of is called 'Entity Attribute Value',
EAV for short (I can't remember its nicknames). Google it.

Note that "committing spreadsheet", an Access convention term (John
Vinson?), is often mistaken around here for 'intentionally
denormalized' but that's another discussion...

This is precisely why I come here to post possible solutions for others. It
is not to try and help them, but to try and hone my own abilities. Getting
slapped upside the head for posting something stupid is an excellent learning
opportunity, for which I am quite grateful. So far, I believe I have
uncovered nearly every possible way of doing something wrong. Statistically
speaking, then, I must be getting closer to perfection.

Plus,
it has been a while since someone had to jump in and tell me I was just plain
wrong. Thanks for the save, Jamie.

A great approach and a commendable attitude.

Jamie.

--

On 14 Jul 2006 01:38:52 -0700, "Jamie Collins"
wrote:

Note that "committing spreadsheet", an Access convention term (John
Vinson?), is often mistaken around here for 'intentionally
denormalized' but that's another discussion...

My consulting resume includes a line "Judicious denormalization, only
when necessary" g

John W. Vinson[MVP]

John Vinson wrote:
Note that "committing spreadsheet", an Access convention term (John
Vinson?), is often mistaken around here for 'intentionally
denormalized' but that's another discussion...

My consulting resume includes a line "Judicious denormalization, only
when necessary" g

So *did* you coin the phrase "committing spreadsheet", John?

Jamie.

--

On 15 Jul 2006 07:01:52 -0700, "Jamie Collins"
wrote:


John Vinson wrote:
Note that "committing spreadsheet", an Access convention term (John
Vinson?), is often mistaken around here for 'intentionally
denormalized' but that's another discussion...

My consulting resume includes a line "Judicious denormalization, only
when necessary" g

So *did* you coin the phrase "committing spreadsheet", John?

Jamie.

I'd have to do a google search to see, but if I recall aright, yes.

John W. Vinson[MVP]

Getting back to the original posting, I'd like to ask the SME's (Jamie and
John, in particular) how they would sort out a lot of dates, such as are
being tracked for Matt's application. Would you place dates such as
FieldworkStartDate and FieldworkEndDate, etc., into various tables, keeping
that particular naming convention? When I gave my advice, I was thinking in
terms of getting these labels (FieldworkStart, FieldworkEnd) out of the field
names, and having them designated from lookup tables. Do you leave these
labels in the field names, in order to be able to enforce the business rule
of certain dates having to occur before other dates?

I get the feeling that I'm missing some part of the logic of this, and would
appreciate either some small discussion, or perhaps a link to an article
about this.

"Jamie Collins" wrote:


mnature wrote:
other than "committing spreadsheet," I don't think I have had
another error that actually has a name and an acronym and a nickname.

Another design flaw to be aware of is called 'Entity Attribute Value',
EAV for short (I can't remember its nicknames). Google it.

Note that "committing spreadsheet", an Access convention term (John
Vinson?), is often mistaken around here for 'intentionally
denormalized' but that's another discussion...

This is precisely why I come here to post possible solutions for others. It
is not to try and help them, but to try and hone my own abilities. Getting
slapped upside the head for posting something stupid is an excellent learning
opportunity, for which I am quite grateful. So far, I believe I have
uncovered nearly every possible way of doing something wrong. Statistically
speaking, then, I must be getting closer to perfection.

Plus,
it has been a while since someone had to jump in and tell me I was just plain
wrong. Thanks for the save, Jamie.

A great approach and a commendable attitude.

Jamie.

--

mnature wrote:
Getting back to the original posting, I'd like to ask the SME's (Jamie and
John, in particular) how they would sort out a lot of dates, such as are
being tracked for Matt's application. Would you place dates such as
FieldworkStartDate and FieldworkEndDate, etc., into various tables, keeping
that particular naming convention? When I gave my advice, I was thinking in
terms of getting these labels (FieldworkStart, FieldworkEnd) out of the field
names, and having them designated from lookup tables. Do you leave these
labels in the field names, in order to be able to enforce the business rule
of certain dates having to occur before other dates?

How far would you take your previous approach? You could have one table
for each data type. Or you could use a MEMO column and put all of your
data into one table a la EAV.

Basic data modelling principles would have you model attributes by
entity type rather than data type. The names you have used would
indicate a Fieldwork entity with StartDate and EndDate attributes.
Implementation of your logical model into SQL is another matter, where
you have to make practical decisions ...

I've been pondering this one and I can't come up with anything much
better than my first post to this thread, where I showed that splitting
the one table solution into three reduced the number of nullable
columns and made constraints easier to write (and therefore maintain).

I get the feeling that I'm missing some part of the logic of this, and would
appreciate either some small discussion, or perhaps a link to an article
about this.

I you would like to post a scenario I would be happy to look at it. You
may be best starting a new thread to increase you chances of attracting
a real SME g.

Jamie.

--