Table design question

[Guest]

I helped someone develop an Access db to store info about their suppliers and
distribution process.
This is what I have,
One table that defines the suppliers of bottles (SUPPLIER), A table that
defines each type of bottle(TYPE) and another table that defines the plant
where these bottles are filled (FILLING).

I then included a linking table that joins the supplier, the type of bottle
and the location of the plant where they are filled. This linking table has
3 fields. I made the supplier and type fields a multi-field primary key and
also included the Filling ID field.

Problem is, now I realize I may have times when the primary key may need to
be duplicated, that is the supplier and type combo can appear more than once
because it is possible that bottles of the same type could come from the same
supplier but go to different Filling locations.

What would be the best way to correct this? I am thinking I may need to
recreate the table with the supplier and type using an autonumber field as
the primary and a new table that includes the new ID and the filling
location. Am I on the right track? Any help would be greatly appreciated.

Thanks
Vicky

 

[J. Goddard]

Hi -

Why not just make the filling_id part of the primary key too?

John

 

[Guest]

After thinking about this a bit, am I better off creating a new unique field
(autonumber) in the table? Is there any reason to avoid a three key primary?

 

[Guest]

I wonder if it is safe to consider a three key primary. Would I be better
off creating a 4th unique field and using that (autonumber) as the primary?

 

[David F Cox]

I would have an autonumber primary key, especially if I was going to join
this table to others. If nothing else it will save you concatenating keys in
many of your queries. It is surprising how often "unique" values turn out
not to be unique and big turns ugly in an application.

 

[Rick Brandt]

I wonder if it is safe to consider a three key primary. Would I be better
off creating a 4th unique field and using that (autonumber) as the primary?

The "natural key" versus "surrogate key" question is an old argument that many
people are very dogmatic about. I personally prefer natural keys when they are
available, but have no qualms about using surrogate keys when they solve
specific problems for me.

One issue with natural keys is that you often have to use multiple fields to
create them. If the table is not referenced with foreign keys in other tables I
have no problem with this, but I generally draw the line at two fields when
foreign key references are required. If I would need to create three or more
foreign key fields in related tables I will generally opt for adding a numeric
surrogate key so that I can use a single field for relating to other tables.

This is admittedly a personal preference in that I have fewer fields to create
and it makes query building and relationship documenting *easier for me*. I
don't believe that the use of a surrogate key is actually *superior* strictly
from a database design standpoint. Only that I find it easier to work with.

 

[Anonymous]

You can see the pros and cons of surrogate keys in the following
thread...
http://forums.oracle.com/forums/thread.jspa?threadID=426548