Too Many Keys?

[alex]

Hello everyone,

I’m basically looking for an opinion(s), which maybe difficult to
give…

I have a database that collects an employee’s time and action.

My main table has only a few fields and I hope is well normalized.

It contains the following fields: UserID (key); GroupID (key), Hours,
Count, ActionID (key), TimeID (key), TimeStamp (key).
Translated: UserN, worked in Group A, for 3 hours, built 8, widgets,
on overtime, on Jan 02, 2009.

That same user (UserN) could have another record exactly the same as
the one above except he/she built the widgets on regular time.

That same user (UserN) could have a third record exactly the same as
the one above except he/she worked in GroupB.

The aforementioned configuration seemed to work fine during testing
(notice the five key combination). What the table lacked, I thought,
was its own primary key, which I added (auto num field). What this
now allowed, however, is duplicate information in my table. E.g.,

1 (tblKey),UserN, worked in Group A, for 3 hours, built 8, widgets, on
overtime, on Jan 02, 2009.
2 (tblKey), UserN, worked in Group A, for 3 hours, built 8, widgets,
on overtime, on Jan 02, 2009.

My question, and I apologize it took me so long to get there:
Does my table appear normalized?
Does the table (a table) need its own key if it’s designed like the
one above?
Am I making any sense?

Thanks,
alex

 

[Douglas J. Steele]

You've opened a real can of worms, here: "natural keys" (i.e.: the 5
separate fields you mention) vs. "artificial keys" (autnonumber) is a hot
topic, like religion or politics! <g>

I typically use natural keys, but if the table is going to be part of a
relationship to another table, so that you need a foreign key pointing to
the table, that can be very cumbersome.

However, be aware that you can use an autonumber key as the Primary Key, but
also create a unique index on the combination of the five fields, so that
duplicates won't be allowed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello everyone,

I’m basically looking for an opinion(s), which maybe difficult to
give…

I have a database that collects an employee’s time and action.

My main table has only a few fields and I hope is well normalized.

It contains the following fields: UserID (key); GroupID (key), Hours,
Count, ActionID (key), TimeID (key), TimeStamp (key).
Translated: UserN, worked in Group A, for 3 hours, built 8, widgets,
on overtime, on Jan 02, 2009.

That same user (UserN) could have another record exactly the same as
the one above except he/she built the widgets on regular time.

That same user (UserN) could have a third record exactly the same as
the one above except he/she worked in GroupB.

The aforementioned configuration seemed to work fine during testing
(notice the five key combination). What the table lacked, I thought,
was its own primary key, which I added (auto num field). What this
now allowed, however, is duplicate information in my table. E.g.,

1 (tblKey),UserN, worked in Group A, for 3 hours, built 8, widgets, on
overtime, on Jan 02, 2009.
2 (tblKey), UserN, worked in Group A, for 3 hours, built 8, widgets,
on overtime, on Jan 02, 2009.

My question, and I apologize it took me so long to get there:
Does my table appear normalized?
Does the table (a table) need its own key if it’s designed like the
one above?
Am I making any sense?

Thanks,
alex

 

[alex]

You've opened a real can of worms, here: "natural keys" (i.e.: the 5
separate fields you mention) vs. "artificial keys" (autnonumber) is a hot
topic, like religion or politics! <g>

I typically use natural keys, but if the table is going to be part of a
relationship to another table, so that you need a foreign key pointing to
the table, that can be very cumbersome.

However, be aware that you can use an autonumber key as the Primary Key, but
also create a unique index on the combination of the five fields, so that
duplicates won't be allowed.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


Hello everyone,

I’m basically looking for an opinion(s), which maybe difficult to
give…

I have a database that collects an employee’s time and action.

My main table has only a few fields and I hope is well normalized.

It contains the following fields: UserID (key); GroupID (key), Hours,
Count, ActionID (key), TimeID (key), TimeStamp (key).
Translated: UserN, worked in Group A, for 3 hours, built 8, widgets,
on overtime, on Jan 02, 2009.

That same user (UserN) could have another record exactly the same as
the one above except he/she built the widgets on regular time.

That same user (UserN) could have a third record exactly the same as
the one above except he/she worked in GroupB.

The aforementioned configuration seemed to work fine during testing
(notice the five key combination).  What the table lacked, I thought,
was its own primary key, which I added (auto num field).  What this
now allowed, however, is duplicate information in my table. E.g.,

1 (tblKey),UserN, worked in Group A, for 3 hours, built 8, widgets, on
overtime, on Jan 02, 2009.
2 (tblKey), UserN, worked in Group A, for 3 hours, built 8, widgets,
on overtime, on Jan 02, 2009.

My question, and I apologize it took me so long to get there:
Does my table appear normalized?
Does the table (a table) need its own key if it’s designed like the
one above?
Am I making any sense?

Thanks,
alex

Thanks Doug, I'm glad you replied.
I'm reading about the multiple indexes; didn't know it was possible.
Thanks again and I'll assume from your lack of critcism that my table
isn't too messed up!!
alex

 

[John W. Vinson]

My question, and I apologize it took me so long to get there:
Does my table appear normalized?
Does the table (a table) need its own key if it’s designed like the
one above?

Not really, though it can be a convenience to have a single-field (e.g.
autonumber) key if this table itself has child tables.

You can create a unique five-field index independent of the primary key if you
wish to do so.

 

[Ken Sheridan]

Alex:

A primary key doesn't need to be a single column.; it can be a composite one
of more than one column, which is exactly the situation in your case, where
the five columns area a 'candidate key' and can consequently be designated as
the primary key.

Your table in fact illustrates a common situation as it is modelling a
relationship type between users, groups, actions and times (regular, or
overtime). You are probably familiar with tables modelling many-to-many
relationship types between two entity types (sometimes called 'junction'
tables or similar), but the same can be true of any number of entity types
which are related in this way. In situations like this a candidate key of
the table will normally be all the foreign key columns, plus where necessary
other columns which represent attributes which make each row distinct, e.g.
your TimeStamp column.

In the majority of cases a table modelling a relationship type like this
won't be referenced by another table, so there is little point in introducing
a surrogate single column key. As John has pointed out, however, sometimes
that can make things more convenient if the table does happen to be
referenced by another table as its then necessary to include only the one
foreign key table in the other (referencing) table rather than having a
composite foreign key referencing the composite primary key of the referenced
table. If you do introduce a surrogate key, however, its essential that you
also create a unique index on the other candidate key columns to prevent
duplication of the type you've identified.

Its not directly relevant to your question, but out of general interest its
worth noting that sometimes a table can have more than one candidate key to
choose from. On occasions two multi-column candidate keys can overlap,
having one or more columns in common. This was found to create a problem
with the original 5 normal forms, and resulted in the introduction of an
additional normal form, Boyce Codd Normal Form (BCNF) which handles
situations where a table might be in 3NF, but still allow for update
anomalies as a result of overlapping candidate keys. Unfortunately BCNF,
while providing a solution to this problem itself can introduce other
problems! I won't go into details, if you are interested you'll find it
written up in plenty of places if you Google BCNF.

Ken Sheridan
Stafford, England

 

[alex]

Alex:

A primary key doesn't need to be a single column.; it can be a composite one
of more than one column, which is exactly the situation in your case, where
the five columns area a 'candidate key' and can consequently be designated as
the primary key.

Your table in fact  illustrates a common situation as it is modelling a
relationship type between users, groups, actions and times (regular, or
overtime).  You are probably familiar with tables modelling many-to-many
relationship types between two entity types (sometimes called 'junction'
tables or similar), but the same can be true of any number of entity types
which are related in this way.  In situations like this a candidate keyof
the table will normally be all the foreign key columns,  plus where necessary
other columns which represent attributes which make each row distinct, e.g.
your TimeStamp column.

In the majority of cases a table modelling a relationship type like this
won't be referenced by another table, so there is little point in introducing
a surrogate single column key.  As John has pointed out, however, sometimes
that can make things more convenient if the table does happen to be
referenced by another table as its then necessary to include only the one
foreign key table in the other (referencing) table rather than having a
composite foreign key referencing the composite primary key of the referenced
table.  If you do introduce a surrogate key, however, its essential that you
also create a unique index on the other candidate key columns to prevent
duplication of the type you've identified.

Its not directly relevant to your question, but out of general interest its
worth noting that sometimes a table can have more than one candidate key to
choose from.  On occasions two multi-column candidate keys can overlap,
having one or more columns in common.  This was found to create a problem
with the original 5 normal forms, and resulted in the introduction of an
additional normal form, Boyce Codd Normal Form (BCNF) which handles
situations where a table might be in 3NF, but still allow for update
anomalies as a result of overlapping candidate keys.  Unfortunately BCNF,
while providing a solution to this problem itself can introduce other
problems!  I won't go into details, if you are interested you'll find it
written up in plenty of places if you Google BCNF.  

Ken Sheridan
Stafford, England















- Show quoted text -

Thanks Ken and John for taking the time to explain your
thoughts...always appreciated.
Ken; I'll need a few moments to digest what you wrote!
alex

 

[Paul Shapiro]

Others already commented on the keys and uniqueness, so nothing to add
there. But you might consider a slight structural modification. Instead of
recording just the date, you could use startDateTime. Then the PK would just
be {userID, startDateTime}, assuming a person can only be doing one
operation at a time. You would still have the other existing fields as FK's.
You can't enforce the rule that no two work efforts for the same user can
overlap with Access table or field validation rules, but you could enforce
that with your data entry form. The existing structure would only let you
prevent more than 24 hours work in a day.

Hello everyone,

I’m basically looking for an opinion(s), which maybe difficult to
give…

I have a database that collects an employee’s time and action.

My main table has only a few fields and I hope is well normalized.

It contains the following fields: UserID (key); GroupID (key), Hours,
Count, ActionID (key), TimeID (key), TimeStamp (key).
Translated: UserN, worked in Group A, for 3 hours, built 8, widgets,
on overtime, on Jan 02, 2009.

That same user (UserN) could have another record exactly the same as
the one above except he/she built the widgets on regular time.

That same user (UserN) could have a third record exactly the same as
the one above except he/she worked in GroupB.

The aforementioned configuration seemed to work fine during testing
(notice the five key combination). What the table lacked, I thought,
was its own primary key, which I added (auto num field). What this
now allowed, however, is duplicate information in my table. E.g.,

1 (tblKey),UserN, worked in Group A, for 3 hours, built 8, widgets, on
overtime, on Jan 02, 2009.
2 (tblKey), UserN, worked in Group A, for 3 hours, built 8, widgets,
on overtime, on Jan 02, 2009.

My question, and I apologize it took me so long to get there:
Does my table appear normalized?
Does the table (a table) need its own key if it’s designed like the
one above?
Am I making any sense?

Thanks,
alex