composite keys for table?

[scratch]

I need some help with developing a certain aspect of a db. I need to track the equipment list of clinics. The equipment list doesn't really change (it has 5 possible values though each clinic may only have one to all 5 of them). The db is also to track who the manufacturer is.

I was originally going to do a three piece composite key consistenting of the clinic id, the equipment id and the manufacturer id with quantity as the non pk field. Or the other possiblility that I can see is just using an autonumber as the pk with clinic id, equipment id, and manufacturer id as fk's with qty as the non-key field. So what do you think? If you can put in explanation why your suggestion would work, that would be appreciated as I'd like to increase my knowledge base on this stuff.

If I haven't made my problem clear, I'm quite willing to provide more info.

Thanks,

scratch

 

[Pat Hartman]

There are two schools of thought on this. One is that you should always use natural candidate keys and the other is that you should always use an arbitrary key such as an autonumber. However what the members of the second school overlook is the necessity to enforce business rules. Therefore, if you decide to go with the single autonumber pk, you MUST define a separate unique index on the three fields that make up your candidate key to prevent duplicates and enforce the business logic.

In either case, the table would include the three columns and they would always be defined as foreign keys whether they were part of a unique primary key or part of a unique index.

I usually lean toward using the composite key UNLESS the table has child tables of its own. Then I use a new autonumber and a unique index to enforce the business rules. Using the single autonumber key makes certain things easier such as using the recordset as the RowSource for a combo or list box. You can't use multi-field keys for those objects. In order for them to work properly, you need a single unique identifier.

 

[The_Doc_Man]

Also look at this thread:

http://www.access-programmers.co.uk/forums/member.php?u=24826

 

[scratch]

index question

Therefore, if you decide to go with the single autonumber pk, you MUST define a separate unique index on the three fields that make up your candidate key to prevent duplicates and enforce the business logic.

If I indexed all the candidate keys as well as the pk, will that allow me to put repeatable info at all (if I use index -> "yes, no duplicates" I mean. I could use could use "Yes, duplicates ok" but I though perhaps an index was supposed to be unique)? For instance, a clinic will generally have more than one piece of equipment so the clinic id field will repeat (though either the type of equipment and/or the manufacturer will be different). I would like to use autonumbers as the key if that' s the only way to use combo boxes since that will make life a lot easier.



Thanks

 

[The_Doc_Man]

When using a composite key, the individual components can repeat but the combination must be unique. Therefore, the composite prime key should be an indexed, no duplicates case. Where there is a separate index on a partial key that is a component of the prime, any index on that isolated field should allow duplicates.

As a matter of fact... to be rigorously correct, if there are indexes on each of the components, ALL of them must allow duplicates. If any one of the components could be successfully set to NO DUPLICATES and left that way, the other two components CANNOT be candidate keys for membership in the primary. (If so, you would have fields in the key on which the rest of the record did not depend, which is another way of describing one of the many things disallowed by normalization rules.)

 

[Pat Hartman]

To create a composite primary key - use cntl-click to select up to 10 columns. When all the components of the key are highlighted, press the key icon on the toolbar. To create a composite index - open the indexes dialog. On the first totally blank line, add a name for the index and select the first column. Choose the appropriate options. On the next line, leave the index name blank and choose the next field in the index and so on - up to a total of 10. If you want to add another index, add an index name and then choose the columns as you did with the first index.