Compositkey dilema...

[Ahmed]

hi,
Thanks in advance..
I am having trouble with composit key..
is it wise to use composit key .. ?
If I want to use composit key of one table as foreign key in second table
... I have to add (or create those fields
in second table.. ) those fields in second table .. is this
the correct way ?

e.g tbl1 with pktblfld , tbl2 with pktbl2fld , tbl3 with
pktbl1fld+pktbl2fld as Pk , and tbl4 having pktbl4fld,
fldprog,fldrostr and tbl3's PK which is pktbl1fld+pktbl2fld

am I in right direction pls explain am nu to access

Thank you very much

 

[Vincent Johns]

hi,
Thanks in advance..
I am having trouble with composit key..
is it wise to use composit key .. ?
If I want to use composit key of one table as foreign key in second table
.. I have to add (or create those fields
in second table.. ) those fields in second table .. is this
the correct way ?

e.g tbl1 with pktblfld , tbl2 with pktbl2fld , tbl3 with
pktbl1fld+pktbl2fld as Pk , and tbl4 having pktbl4fld,
fldprog,fldrostr and tbl3's PK which is pktbl1fld+pktbl2fld

am I in right direction pls explain am nu to access

Thank you very much

I usually think of a composite key as consisting of several fields in
the same table, which you might want to do to save space by not adding a
separate unique-identifier field there. But the unique identifier,
being (for example) just a number, is often a more convenient value to
store in the other tables that link to the first one. That way, you
don't have to duplicate those other fields in the linked tables.

What you are proposing is possible, but why do you want to do it? You
could easily give tbl3 its own "Autonumber" key, guaranteeing unique
values there, and include foreign-key links to whatever other tables it
needs to relate to. If you later decide to remove one of the
relationships, you can then just delete the one field that's involved
without disturbing any of the others.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Ahmed]

Thank you very much John,
Reason for that I asked .. in my database I came across
twice this senerio.. may be false thinking..
e.g tbl3 and tbl4 both have many to many possibilities
like Issues... Solutions ,
stampissues.. individual stamps
(with vrieties) ,
Patients, ResponsibleParty and Insurances etc.

but both 4th table can be uniquely Identify
then I endup creating other junctional table.. is it ok then
I appreciate your response and Thanks.
Ahmed

 

[Vincent Johns]

Thank you very much John,
Reason for that I asked .. in my database I came across
twice this senerio.. may be false thinking..
e.g tbl3 and tbl4 both have many to many possibilities
like Issues... Solutions ,
stampissues.. individual stamps
(with vrieties) ,
Patients, ResponsibleParty and Insurances etc.

but both 4th table can be uniquely Identify
then I endup creating other junctional table.. is it ok then
I appreciate your response and Thanks.
Ahmed

OK, sorry, I didn't understand what you were doing. Yes, each record in
your transitional table will contain both (or all) relevant links, and
the record will represent a unique association of records from the other
tables, such as an insurance policy that links one of several patients
to one of several insurers. You may or may not wish to include
additional fields in the table.

It's possible that you won't need to store the combined keys in the
transitional [tbl3] table anywhere else; you might just link to (and
through) [tbl3] in your queries. Often, when I do this, I don't even
bother to define a separate primary key field for the transitional table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Ahmed]

Hi John,
Thank you very much
Appreciate..
Ahmed

Thank you very much John,
Reason for that I asked .. in my database I came across
twice this senerio.. may be false thinking..
e.g tbl3 and tbl4 both have many to many possibilities
like Issues... Solutions ,
stampissues.. individual stamps
(with vrieties) ,
Patients, ResponsibleParty and Insurances etc.

but both 4th table can be uniquely Identify
then I endup creating other junctional table.. is it ok then
I appreciate your response and Thanks.
Ahmed

OK, sorry, I didn't understand what you were doing. Yes, each record in
your transitional table will contain both (or all) relevant links, and the
record will represent a unique association of records from the other
tables, such as an insurance policy that links one of several patients to
one of several insurers. You may or may not wish to include additional
fields in the table.

It's possible that you won't need to store the combined keys in the
transitional [tbl3] table anywhere else; you might just link to (and
through) [tbl3] in your queries. Often, when I do this, I don't even
bother to define a separate primary key field for the transitional table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Hi Ahmed,

Here is an article that you might find helpful:

Why to avoid composite Primary Keys
http://www.utteraccess.com/forums/s...=247916&page=0&view=collapsed&sb=5&o=&fpart=1


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

hi,
Thanks in advance..
I am having trouble with composit key..
is it wise to use composit key .. ?
If I want to use composit key of one table as foreign key in second table
.... I have to add (or create those fields
in second table.. ) those fields in second table .. is this
the correct way ?

e.g tbl1 with pktblfld , tbl2 with pktbl2fld , tbl3 with
pktbl1fld+pktbl2fld as Pk , and tbl4 having pktbl4fld,
fldprog,fldrostr and tbl3's PK which is pktbl1fld+pktbl2fld

am I in right direction pls explain am nu to access

Thank you very much

 

[Vincent Johns]

Hi Ahmed,

Here is an article that you might find helpful:

Why to avoid composite Primary Keys
http://www.utteraccess.com/forums/s...=247916&page=0&view=collapsed&sb=5&o=&fpart=1

Tom

The article makes some valid points, but consider them in light of your
own situation.

Problem 1, that the SQL to delete a record is simpler with one key, is
correct, but both of the examples shown there work properly, so if you
don't delete records often, it may not matter to you.

Problem 2, that the key might not be unique, might not apply to you.
You'll probably want to define a "No Duplicates" index on your junction
table, and your Combo Box would thus select unique values. You'll need
to be sure that the values really ARE unique, perhaps by trying to add
duplicate values and being sure that you get an error message. (Or
maybe I don't fully understand the writer's objection -- take what I say
with a grain of salt!)

Problem 3 also seems based on a premise that the combined key might not
be unique. If I understand this correctly, see my suggestion concerning
Problem 2.

In your case, if your junctional table does *not* need to have unique
keys, then you'll need some way to distinguish those that share a set of
keys, and by the time you add enough extra keys to make the records
unique, you might save a lot of trouble just by adding an Autonumber key
to the table and using that as your primary key.

 

[Guest]

Vincent,

Problem 1, that the SQL to delete a record is simpler with one key, is
correct, but both of the examples shown there work properly, so if you
don't delete records often, it may not matter to you.

...."so if you don't delete records often, it may not matter to you"? I'm
not sure what that statement is suppose to mean. Do you really want to create
solutions where deleting records is a bit more difficult than it needs to be?
What about the example of allowing a student to take a class multiple times,
or any other situation, where you might need multiple entries in the junction
table from tbl1 and tbl2? As the author pointed out, this would require one
(or more) additional fields be used as the combined PK.

Problem 2, that the key might not be unique, might not apply to you.
You'll probably want to define a "No Duplicates" index on your junction
table...

Well, since the title of the article is "Why to avoid composite Primary
Keys", you would have already had a "No Duplicates" multi-field index on the
foreign key fields that made up the combined PK for this table. I agree that
you may want to define a "No Duplicates" multi-field index on these two
foreign key fields if they are not used as a primary key, but by doing so,
you re-introduce the problem discussed in paragraph 1 (ie. allowing a student
to repeat a class multiple times). I can't really think of too many
situations where the bound column of a combo box is not unique in the
databases that I create.

Problem 3 also seems based on a premise that the combined key might not
be unique. If I understand this correctly, see my suggestion concerning
Problem 2.

I don't think you understood it correctly. The author was saying that if you
need a new table that is related one-to-many (1:M) to your junction table
(tblStudentClasses in this example), then you will need to include three
pieces of information in your new table (tblStudentClassTests) : StudentID,
ClassID and TestID. This would require two relationship joins defined to
enforce referential integrity:

tblStudentClasses.StudentID ----> tblStudentClassTests.StudentID and
tblStudentClasses.ClassID ----> tblStudentClassTests.ClassID


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

The article makes some valid points, but consider them in light of your
own situation.

Problem 1, that the SQL to delete a record is simpler with one key, is
correct, but both of the examples shown there work properly, so if you
don't delete records often, it may not matter to you.

Problem 2, that the key might not be unique, might not apply to you.
You'll probably want to define a "No Duplicates" index on your junction
table, and your Combo Box would thus select unique values. You'll need
to be sure that the values really ARE unique, perhaps by trying to add
duplicate values and being sure that you get an error message. (Or
maybe I don't fully understand the writer's objection -- take what I say
with a grain of salt!)

Problem 3 also seems based on a premise that the combined key might not
be unique. If I understand this correctly, see my suggestion concerning
Problem 2.

In your case, if your junctional table does *not* need to have unique
keys, then you'll need some way to distinguish those that share a set of
keys, and by the time you add enough extra keys to make the records
unique, you might save a lot of trouble just by adding an Autonumber key
to the table and using that as your primary key.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
__________________________________________

Tom Wickerath wrote:

Hi Ahmed,

Here is an article that you might find helpful:

Why to avoid composite Primary Keys
http://www.utteraccess.com/forums/s...=247916&page=0&view=collapsed&sb=5&o=&fpart=1


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

hi,
Thanks in advance..
I am having trouble with composit key..
is it wise to use composit key .. ?
If I want to use composit key of one table as foreign key in second table
.... I have to add (or create those fields
in second table.. ) those fields in second table .. is this
the correct way ?

e.g tbl1 with pktblfld , tbl2 with pktbl2fld , tbl3 with
pktbl1fld+pktbl2fld as Pk , and tbl4 having pktbl4fld,
fldprog,fldrostr and tbl3's PK which is pktbl1fld+pktbl2fld

am I in right direction pls explain am nu to access

Thank you very much

 

[Vincent Johns]

Vincent,


..."so if you don't delete records often, it may not matter to you"? I'm
not sure what that statement is suppose to mean. Do you really want to create
solutions where deleting records is a bit more difficult than it needs to be?

Yes, indeed. Because it's not MUCH more difficult, and the table
structure is simpler. It's a trade-off: two fields vs. three, but a
slightly longer SQL statement (which you may never look at again). How
much do you care that the computer needs to do slightly more work, if
you can save space? (The answer depends to some extent on what
resources you have available.)

What about the example of allowing a student to take a class multiple times,
or any other situation, where you might need multiple entries in the junction
table from tbl1 and tbl2? As the author pointed out, this would require one
(or more) additional fields be used as the combined PK.

I would want to determine *ahead of time* if multiple records with the
same key should ever be allowed. If I know that the second such
occurrence will supersede the first one, or if there is some external
reason that duplicates are never proper, there should be no problem.
Otherwise, I shouldn't do it this way.

Well, since the title of the article is "Why to avoid composite Primary
Keys", you would have already had a "No Duplicates" multi-field index on the
foreign key fields that made up the combined PK for this table. I agree that
you may want to define a "No Duplicates" multi-field index on these two
foreign key fields if they are not used as a primary key, but by doing so,
you re-introduce the problem discussed in paragraph 1 (ie. allowing a student
to repeat a class multiple times). I can't really think of too many
situations where the bound column of a combo box is not unique in the
databases that I create.

I agree, but how is a "No Duplicates" index going to allow something
that a combined PK won't? You can't enter duplicate sets of values in
either case.

I don't think you understood it correctly. The author was saying that if you
need a new table that is related one-to-many (1:M) to your junction table
(tblStudentClasses in this example), then you will need to include three
pieces of information in your new table (tblStudentClassTests) : StudentID,
ClassID and TestID. This would require two relationship joins defined to
enforce referential integrity:

tblStudentClasses.StudentID ----> tblStudentClassTests.StudentID and
tblStudentClasses.ClassID ----> tblStudentClassTests.ClassID


Tom

http://www.access.qbuilt.com/html/expert_contributors.html

OK, I think I see what you're saying -- that if (in Problem 3) you have
to set up a separate table, [tblTests] for multiple tests for each
student for each class, you burden this new table with an 8-byte foreign
key instead of a 4-byte key. But in this case, couldn't you dispense
entirely with [tblStudentClasses]? In [tblTests], you could either let
the 2 fields from [tblStudentClasses] plus the new [tblTestsID] field
form the (combined) Primary Key, or just specify that [tblTestsID] be an
Autonumber or otherwise unique without reference to any other fields.

So your choice would be either to

(1) include both [StudentID] and [ClassID] as foreign keys in
[tblTests], and erase [tblStudentClasses], or

(2) include just one [tblStudentClassesID] key to link to
[tblStudentClasses].

I suppose there might be valid reasons to choose (2), such as if
somebody else maintains it and you have to get permission to make
changes, but every time you use it your Query has to do 2 indirections.
I'm not opposed to making a computer do the work that a human being
might otherwise have to do, but this seems kind of unnecessary.

Option (1) is about the same as changing the design of
[tblStudentClasses] to add fields to it of [tblTestsID] (unique) and
[TestData], and resetting its primary key to be the new [tblTestsID].

I claim that both options will be correct, but that (1) is preferable
because it will probably require less space and fewer memory references.

:

The article makes some valid points, but consider them in light of your
own situation.

Problem 1, that the SQL to delete a record is simpler with one key, is
correct, but both of the examples shown there work properly, so if you
don't delete records often, it may not matter to you.

Problem 2, that the key might not be unique, might not apply to you.
You'll probably want to define a "No Duplicates" index on your junction
table, and your Combo Box would thus select unique values. You'll need
to be sure that the values really ARE unique, perhaps by trying to add
duplicate values and being sure that you get an error message. (Or
maybe I don't fully understand the writer's objection -- take what I say
with a grain of salt!)

Problem 3 also seems based on a premise that the combined key might not
be unique. If I understand this correctly, see my suggestion concerning
Problem 2.

In your case, if your junctional table does *not* need to have unique
keys, then you'll need some way to distinguish those that share a set of
keys, and by the time you add enough extra keys to make the records
unique, you might save a lot of trouble just by adding an Autonumber key
to the table and using that as your primary key.

[...]

What I meant was that by the time you have some key in your table that
is guaranteed to be unique, you can make that be the table's primary key.

Incidentally, I normally avoid using multiple-column combo boxes.
Instead, I define a Query with only two fields: a key, and a computed
field that is easy for the user to understand (such as first name,
space, last name, sorted by last name) and in the Combo Box or Text Box
display only the 2nd field but use the 1st field as the chosen value.
This is lots easier than trying to re-design existing fields to make
them easier to read.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.