Hi Jax,
There's no room here for a full discussion of Third Normal Form, or
Relational design.
However, the point of a foreign key is to allow two different items to be
related, yet different.
One of the values of this is that you could have a one-to-many relationship
(100 people, all work for the same company. You'd need employee information
for each person, but the employer information should only be entered once).
If you have a Universal Key, then which key would you use for the company
record? Joe's key? Mary's key? You see the point?
And how would you create a many-to-many relationship if you had one record
in table A that refers to many records in table B, but you also have one
record in Table B referring to many records in Table A. Without unique
primary keys in each table, (and in this case, a third table), you can't do
it with relational databases.
Note: in relational theory, if you use a foreign key (which is the primary
key on another table) as the primary key, you have created a
one-to-zero-or-one relationship. In other words, one record in the main
table could link to one record in the dependent table or not. This is
sometimes used as a way to reduce the space consumed in a database for large
numbers of empty columns by placing the "sparse" columns in a dependent
table of this kind. It adds a good bit of useless complexity, and is often
not worth doing.
Normally, if you have the same key being used as the primary key in two
tables, you'd be better off moving all the fields to a single table.
I have no idea if this is helping you.
--- Nick
"Jax" <an*******@disc ussions.microso ft.com> wrote in message
news:05******** *************** *****@phx.gbl.. .
I'm making my first ever database for my program.
I understand the concept of one to many relationships but
fail to see the advantage of using a secondary key over a
primary one.
I have a lot of tables and link them together by one key
the CaseKey.
What is the disadvantages of this method? Why should I
include more detailed item keys (e.g an arraylist of
customer credit items would be stored in the
CustomerCreditI tem table surely it's better for them to
share a universal primary key rather then following the
key from case to customer and then to creditItem, that
needing three keys).
Can anyone provide me with excellent advise? (i'll accept
mediocre if it's all you've got though, and happily :))
Many thanks
jax
