"Intellegent" Key - This is bad?

[Rick Neal]

I ask this question due to remarks I saw in relation to
another question on these boards.

In my current application I have a child table based on
two parent tables. Therefore when I append data to this
table I uses a concantonated Text string to create unique
values based on one of the parent tables.

For instance...
Each Customer has multiple employees processing different
phases of the customers transaction.

So my field is a concantonation of the two other key
fields.
[Transaction#] & [Trans Processing Phase]

Due to the logic of the application I cannot allow
duplicate Processing phases on one transaction, so my
solution was to concantonate the two fields into one
unique primary key for the child table.

Is this bad design? and if so Is there a better way?

 

[Roger Carlson]

What you are describing is a Linking table that resolves a Many-To-Many
relationship between two other tables. Something like this:

Many-To-Many

Order OrderDetails Products

--------------- ----------------- ----------
------

OrderID (pk)----------<OrderID (cpk)(fk) |---- ProductID

OrderDate ProductID (cpk)(fk)>--| ProductName

....other fields ...other fields


It is perfectly acceptable to create a multiple field primary key in this
case. If the Primary Keys of the other two tables are Autonumber, then it
is not really an "Intelligent" key anyway.

On the other hand, there is no real liability to create a Autonumber primary
key on this table. However, if you do, you will want to create a Unique
Index on the other two fields anyway. Might as well make it the primary
key.

 

[Douglas J. Steele]

I don't think that's what Rick was describing, Roger.

He said "So my field is a concantonation of the two other key fields.
[Transaction#] & [Trans Processing Phase]"

Rick: There's no reason to concatenate fields like that. An index in Access
can have up to 10 separate fields in it. Just define the primary key as
being both fields.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

What you are describing is a Linking table that resolves a Many-To-Many
relationship between two other tables. Something like this:

Many-To-Many

Order OrderDetails Products

--------------- ----------------- -------- --
------

OrderID (pk)----------<OrderID (cpk)(fk) |---- ProductID

OrderDate ProductID (cpk)(fk)>--| ProductName

...other fields ...other fields


It is perfectly acceptable to create a multiple field primary key in this
case. If the Primary Keys of the other two tables are Autonumber, then it
is not really an "Intelligent" key anyway.

On the other hand, there is no real liability to create a Autonumber primary
key on this table. However, if you do, you will want to create a Unique
Index on the other two fields anyway. Might as well make it the primary
key.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I ask this question due to remarks I saw in relation to
another question on these boards.

In my current application I have a child table based on
two parent tables. Therefore when I append data to this
table I uses a concantonated Text string to create unique
values based on one of the parent tables.

For instance...
Each Customer has multiple employees processing different
phases of the customers transaction.

So my field is a concantonation of the two other key
fields.
[Transaction#] & [Trans Processing Phase]

Due to the logic of the application I cannot allow
duplicate Processing phases on one transaction, so my
solution was to concantonate the two fields into one
unique primary key for the child table.

Is this bad design? and if so Is there a better way?

 

[Jeff Boyce]

Rick

Doug's point is about not needing to combine other table's keys to create a
single field as a primary key in your "child" table.

You prompted this thread with a question about the notion of "intelligent"
keys. As a sometimes derogator of same, my rationale has been that a
primary key is typically arbitrary, and contains no particular meaning re:
the rest of the row. On the other hand, the use of a "natural" key as a
primary key would seem to argue for embedding meaning in a primary key.

I think the distinction comes from the notion that relational design calls
for one fact in one field. As soon as multiple facts are combined together
into a single field, parsing the "intelligence" embedded in that field
becomes problematic, and doesn't match the one fact/one field notion.

But that's just one person's opinion...

 

[John Vinson]

So my field is a concantonation of the two other key
fields.
[Transaction#] & [Trans Processing Phase]

Due to the logic of the application I cannot allow
duplicate Processing phases on one transaction, so my
solution was to concantonate the two fields into one
unique primary key for the child table.

Is this bad design?
Yes.

and if so Is there a better way?

A Primary Key can consist of UP TO TEN fields. Just open the table in
design view, and ctrl-click the two fields, then click the Key field;
or else open the Indexes window and create a unique two field index.

 

[Guest]

-----Original Message-----

So my field is a concantonation of the two other key
fields.
[Transaction#] & [Trans Processing Phase]

Due to the logic of the application I cannot allow
duplicate Processing phases on one transaction, so my
solution was to concantonate the two fields into one
unique primary key for the child table.

Is this bad design?

Yes.

I disagree. I have designed db where the primary keys were
concantonate and where primary keys were just plain dumb
numbers. There were advantages and disadvantages to using
both. One advantage with concantonate keys is that is that
it's MUCH easier to delete records from the child tables.
It can be done without doing any joins. Of course, the
disadvantage is that you have to store all those fields
which could be overwhelming in the grandchild ( and
beyond ) tables.

When working with relational db it is always best to
remember there about 100 different ways to skin a cat.

Another approach, and I would argue if this is better, is
to assign each table a dumb primary key based on a number
( ie the key does not represent anything, not date, not,
time, etc. ).

 

[Tim Ferguson]

Of course, the
disadvantage is that you have to store all those fields
which could be overwhelming in the grandchild ( and
beyond ) tables.

No: the main disadvantage is the loss of data when the pseudo-fields get
unsynchronised from the real ones. In Access there is no way of keeping
them together, and even in a real database you have to cross your fingers
and hope you didn't leave a bug in the trigger routines.

Remember that the point behind 1NF and 2NF is about _guaranteeing_ data
integrity. By all means, if cutting corners or pretty presentation is more
important than that, then working to R rules is not vital. On the other
hand if the data themselves are paramount then there is, presently, no
alternative. And it is dangerous to pretend to new users that there is,
because they might believe you.

When working with relational db it is always best to
remember there about 100 different ways to skin a cat.

Well, yes: but there is only one way to set up 1, 2, and 3 NF. This is
simple mathematics, it's not something airy-fairy like brain surgery.

B Wishes


Tim F