Is there a best practice design for handling different types of the same object which have different properties?

Question

This may be a rather naive question and I am not even sure if I am asking it in the best way, so my apologies if not.

I would like to know if there is a standard, or best way to handle cases where you have multiple types of the same object, each of which have some differing properties.

For example, - payments. All payments have some properties in common (status, value, date received etc.) but some properties will depend upon the specific type of payment. e.g. cheques will have a bank, cheque number etc. Where as Paypal payments will have a Transaction ID etc.

I can see two ways of handling this, and neither seem ideal (although I might be wrong and there are probably other ways I have not thought of).

The first and simplest way would be to have a single "Payments" table with all the fields in it, and simply have NULL values for the fields which do not apply.

The second way would be to have a "Payments" table to contain all the generic payment details and then another table for each of payment types to how the type specific details.

Please see the image below for visual examples:

Is there a "correct" way to handle this type of situation? Also, should I be storing the "Payment Type" as a field, or calculating it based on the other data?

Any suggestions or advise would be gratefully received.

Answer 1

After some research, I believe that the second way I suggested, is usually considered the best method (depending on how many unique columns the sub types have).

I also discovered that this is called "Table Inheritance".

However, as it stands there are a few issues with this method:

• It is possible that no sub-type exists for a particular
• It is possible that two sub-types could refer back to the same master payment record.
• In order to identify the type of payment, you would have to query each of the sub-type tables, and decide how to deal with the possibilities mentioned above.
• To get around this, we could include a "Type" field in the master table, but that would introduce duplication of data and, even worse, the possibility of conflicting data.

Whilst looking into this problem, I found this excellent link, which I believe offers a very elegant solution:

https://www.sqlteam.com/articles/implementing-table-inheritance-in-sql-server

It only uses simple FK relationships and unique keys and doesn't require any complex triggers.

Essentially, you create a "Type" field in both the master table AND the sub-type table (the field in the sub-type table will always be the same, so a computed, persisted column is suggested). Then, when creating the relationship between the master and sub-type tables, you link on both, PaymentID AND the Type (you will need to create a uniquer key containing both on the master, "Payments" table to do this).

Although this is a little more complex to set up, it seems like it should be easy to maintain, or add additional payment types if required.

This way, you can be sure that:

• a max of only one sub-type exists for a single payment.
• every sub-type has a matching parent record.
• although it is possible for a sub-type record not to exist for a row in the master table, you do at least know what the type is supposed to be as it is stored in the master table.