Hi,

I'm trying to design a simple database for a project I am working on and I just need a small amount of advice to get it wrapped up. I have a junction table taking two foreign keys and they won't necessarily be unique. Is this the kind of situation where you would add an auto-incremented index? Is it possible to then compound the three fields into a compound key?

Hopefully the picture here demonstrates my situation clearly.

Thanks a lot for your time.

http://dl.dropbox.com/u/1735585/database.jpg

no, sorry, it doesn't

i have a large problem understanding what "object" and "component" are

you show a row in the junction table with values (2,1) twice

did you intend this? because if you wanted that, you should add a "qty" column, and replace those two rows with a single one with qty=2

ordinarily you would want (objectid,componentid) to be the PK, and you should keep looking for that

The database is for storing game object information. Object is a generic object in the game world and components are children components of the object which will have associated information like mesh location and relative position. There are more fields in the junction table because each component needs its own position specified. If any object has multiple components of the same type then the two foreign keys (which would usually make up the primary key) won't be unique. So that's the situation I'm wondering how to handle.

So in the junction table I would have
http://dl.dropbox.com/u/1735585/4-24...12-27%20AM.jpg

If this still isn't understandable I'll make a complete diagram.

my advice is to use a "qty" column that says how many of this type of component are included in the object

so the two FKs will be unique

alternatively, since it's the x-y-z components which confer uniqueness, you should add them to the PK

Alright, that's simple enough then. Thanks.