82848

PK on table with (simulated) polymorphic association

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.

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/FNfgg.png" data-original="https://i.stack.imgur.com/FNfgg.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

Answer1:

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.

Recommend

  • VBA: How can I keep only the date values from a string?
  • Using a support vector classifier with polynomial kernel in scikit-learn
  • SMT prover yields 'unknown' despite strong proven assertions
  • add two double given wrong result
  • Using Python help throws SEGFAULT [closed]
  • Solving multiple interface implementation
  • ASM ClassReader failed to parse class file - probably due to a new Java class file version that isn&
  • How to make a user wait with Laravel
  • Where in the relevant specification is it documented that some comments in a SQL script are, in fact
  • Generating anchors with PyYAML.dump()?
  • Get Currently Active User in Android
  • How do I include a SWC in an AS2 Flash project?
  • How to add a focus style to an editable ComboBox in WPF
  • RxJava debounce by arbitrary value
  • Array with custom indexes in Ionic2
  • How do I superscript characters in a UIButton?
  • onBackPressed() not being executed
  • How to define and use opencv mat of user type
  • Alert pop up with LWUIT
  • Using variable in a value field in jMeter
  • Get one-time binding to work for ng-if
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • Fetching methods from BroadcastReceiver to update UI
  • How to recover from a Spring Social ExpiredAuthorizationException
  • Check if a string to interpolate provides expected placeholders
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Cassandra Data Model
  • Javascript + PHP Encryption with pidCrypt
  • ActionScript 2 vs ActionScript 3 performance
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • SQL merge duplicate rows and join values that are different
  • Why joiner is not used after Sequence generator or Update statergy
  • Getting Messege Twice Using IMvxMessenger
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • LevelDB C iterator
  • How can I remove ASP.NET Designer.cs files?
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Can't mass-assign protected attributes when import data from csv file
  • Python/Django TangoWithDjango Models and Databases