Field Independence in Normalised Tables

"Chris2" wrote:
"Relation" is the technical name of what Relational Databases call
"Tables".

One of my pet peeves about MS Access is that it "robbed" the use of "relationship" to
replace the term referential integrity constraints (which it should not have done, in my
opinion).

One of my pet peeves about MS Access is DAO (g), which has a collection
class consisting of such 'Relationships' named 'Relations'!!

Note that MS Access 'Relationships' is not the same as concept as an
engine-level FOREIGN KEY (DRI). I can create a MS Access 'Relationship' that
does not enforce referential integrity, one between columns of wildly
different data types, etc. Hence the need for a new term with a suitably
vague meaning.

Jamie.

--

"Chris2" wrote:
Employees hire and fire dates are a separate subset of data. Employees can be hired and
fired multiple times in the vast majority of businesses, and so that information must be
removed to another table.

Employment
EmploymentID AUTOINCREMENT -- Primary Key
EmployeeID INTEGER -- Foreign Key to tblEmployees
DateHired DATETIME
DateTerminated DATETIME
ReasonForTermination TEXT(255)

"Must"? Temporal databases are hard work in SQL. For one reason or another,
many (most?) tables model the current (or most recent) state. I note your
proposed design lacks a sequenced primary key. Care to declare which Normal
Form it is in? Does "fully normalized" mean 6NF? Isn't 'ReasonForTermination'
an attribute of a 'termination', therefore does not belong in the
'employment' table?

Jamie.

--

"Jamie Collins" wrote in message
...
"Chris2" wrote:

snip


Note that MS Access 'Relationships' is not the same as concept as an
engine-level FOREIGN KEY (DRI). I can create a MS Access 'Relationship' that
does not enforce referential integrity, one between columns of wildly
different data types, etc. Hence the need for a new term with a suitably
vague meaning.


Jamie,

When using MS Access Relationships, I always enforce RI, and never create one between
columns of different *attributes*, much less different data types, so I tend not to think
of that aspect.

The main reason for that is that I always create them via DDL, and so "Enforce RI" is
automatically set, which is what I want.

It is not immediately obvious to me what good they would be if not used to enforce RI.
I'll have to dig into their definitions in the books I have and the JET SQL reference and
see if I can figure it out.


Sincerely,

Chris O.

Jamie,

Comments in-line.

"Jamie Collins" wrote in message
...
"Chris2" wrote:
Employees hire and fire dates are a separate subset of data. Employees can be hired
and
fired multiple times in the vast majority of businesses, and so that information must
be
removed to another table.

Employment
EmploymentID AUTOINCREMENT -- Primary Key
EmployeeID INTEGER -- Foreign Key to tblEmployees
DateHired DATETIME
DateTerminated DATETIME
ReasonForTermination TEXT(255)

"Must"?

"Must" from my point of view only.


Does "fully normalized" mean 6NF?

Yes it does.

I didn't say that I was shooting for full normalization when I gave the above. (When I
said "fully normalized" later, I was only commenting on the table design presented, which
I also further qualified by stating that I didn't have the full db available to comment
on.)

For the level of the discussion, shooting for the range of 1-3NF is often pretty good.


Isn't 'ReasonForTermination'
an attribute of a 'termination', therefore does not belong in the
'employment' table?

Jamie.

I was looking at that column, thinking it should be moved to a different table, even as I
wrote the table out. I even had a EmployeeTerminationHistory table started (which I
deleted).

To me, the normalize/denormalize decision would involve questions about the overall
structure, extent, and purpose of the rest of the db, something I didn't know and felt was
beyond the scope of the discussion.

I also skipped normalize/denormalize discussions in regard to it for the same reason.

(The OP discussed just getting into this.)


Sincerely,

Chris O.

"Chris2" wrote:
When using MS Access Relationships, I always enforce RI, and never create one between
columns of different *attributes*, much less different data types, so I tend not to think
of that aspect.

It is not immediately obvious to me what good they would be if not used to enforce RI.

IIRC MS Access uses a 'Relationship' to automatically create a JOIN when
both tables are pulled into the query builder tool thing (another one of
those things us 'real' SQL coder don't use g). Not all tables joined in
queries will have RI e.g. auxiliary tables such as Calendar.

I'll have to dig into their definitions in the books I have and the JET SQL reference and
see if I can figure it out.

Note that 'Relationships' are not a feature of the engine.

Jamie.

--

"Chris2" wrote:

Jamie,

Comments in-line.

Interesting interpretation of 'in line': you snipped out

I note your
proposed design lacks a sequenced primary key.

Your proposed schema offers no means of uniquely identifying data e.g. it
will allow this:

INSERT INTO Employment (EmployeeID,
DateHired, DateTerminated)
VALUES (1, #2007-01-01 00:00:00#, #2007-01-31 00:00:00#)
;
INSERT INTO Employment (EmployeeID,
DateHired, DateTerminated)
VALUES (1, #2007-10-01 00:00:00#, NULL)
;

not to mention other funky combinations e.g. termination reason with NULL
DateTerminated, DateTerminated before DateHired, etc. It doesn't matter what
Normal Form a table is in if it's not fit for purpose. Care to add the
missing constraints now?

For the level of the discussion, shooting for the range of 1-3NF is often pretty good.

You opinion. I'd be looking for 5NF, especially from someone critiquing in
the same thread another's design as regards Normal Form violations.

Jamie.

--

On Fri, 22 Jun 2007 04:47:41 -0700, "Chris2"
wrote:

It is not immediately obvious to me what good they would be if not used to enforce RI.
I'll have to dig into their definitions in the books I have and the JET SQL reference and
see if I can figure it out.


About the ONLY benefit, and it's a very tiny one, is that if there is a
nonenforced relationship between two tables it will default to creating that
Join when you use the query builder and add those two tables.

I never use the feature either.

John W. Vinson [MVP]

"Jamie Collins" wrote in message
...



snip


IIRC MS Access uses a 'Relationship' to automatically create a JOIN when
both tables are pulled into the query builder tool thing (another one of
those things us 'real' SQL coder don't use g). Not all tables joined in
queries will have RI e.g. auxiliary tables such as Calendar.


Jamie,

Thank you for the tip. I hadn't know that.


Sincerely,

Chris O.

"John W. Vinson" wrote in message
...
On Fri, 22 Jun 2007 04:47:41 -0700, "Chris2"
wrote:

It is not immediately obvious to me what good they would be if not used to enforce RI.
I'll have to dig into their definitions in the books I have and the JET SQL reference
and
see if I can figure it out.


About the ONLY benefit, and it's a very tiny one, is that if there is a
nonenforced relationship between two tables it will default to creating that
Join when you use the query builder and add those two tables.

I never use the feature either.

John W. Vinson [MVP]

John Vinson,

Thank you!


Sincerely,

Chris O.

"Jamie Collins" wrote in message
...

"Chris2" wrote:

I note your
proposed design lacks a sequenced primary key.

Yes, you're correct. The PK selection was insufficient. It should have included the
first three columns.


You opinion. I'd be looking for 5NF, especially from someone critiquing in
the same thread another's design as regards Normal Form violations.

How do you rate the normalization of table that was presented from the book in question?


Sincerely,

Chris O.