PK on table with (simulated) polymorphic associati

Question

I am trying to set up the right indices on a table I have just created which contains 4 "polymorphic associations" and a PK ID. The 4 associations allow me not to have to quadruple the number of tables to the addition I am making to the database and should not be modified in this discussion. My question is how should I set up the indices so that I get optimal performance (speed, space not so much) ? None of the 4 keys is candidate for PK. More specifically all 4 are but only one at a time. I have added a PK "ID" because I had read that adding a PK, even if not used, is better than not adding a PK. However, I am questionning this assertion more and more.

More about the table : the logic that only 1 of the 4 FKs should be used is enforced by an Access form. Nobody non-dev has access to the tables directly. I expect there will be no more than a couple hundred entries every month for as long as this database is in use. Assuming we use it 10 more years and average 500 entries a month (which is probably a bit more than what it will be) we should have no more than 60k entries in 10 years. Basically, this is not a hugely populated table.

The db and forms run on Access 2003 (yeah I know...).

I hope that is enough information for you to help me. In the image below you can see the table structure as it is right now. The 4 FKs are NoDemandeAmendementTransit, NoDemandeAmendementRubrique, NoAmendementTransit, NoAmendementRubrique.

Many thanks.

Answer 1

A more practical design is to create a single supertype table for all of the four subtypes you are referencing. Then reference the supertype table with a single foreign key instead of having four separate FKs. It's a design pattern you can find in most good books on database design and it is simpler and more efficient than having multiple "optional" foreign keys. It will also provide you with a more useful primary key.