One-To-Many join table to avoid nullable columns [closed]

Question

I'M wondering myself whether am I the first programmer struggling with this problem, but i can't find anything in SO about this.

Point of my question, is it a good idea to make a One-To-Many join table, in order to prevent NULL references.

Let's explain, in our business requirements, we have some activities that causes a payment, i.e. sales, loans, rents, services etc. each activity can have zero or one or more payments.

When designing the DB, we have tables for each activity, Sales – Loans – Rents - Services etc, and a Payment table. The relation between the activities and the payments are one to many, each loan can have many payments, and each rent can have many payments.

But there is a problem, each payment can be a loan or a sale or any other activity, we need to relate it to its corresponding activity. I think about two options:

1) Add some Foreign keys in the Payments table for each kind of activity, LoanID - RentID - ServiceID etc. And make them Nullable, due to a loan is neither a service nor a rent.

I personally don't like this solution, it is very error prone, man can very easy forgot to add the matching FK due to it is Nullable, and then we don't know what this payment is about, we lose the Referential integrity. Although it is possible to overcome this problem by creating some constraint to ensure that there are Neither more nor less than one FK, but it is not so easy to create the right constraint and take into account all possible options, and it is hard to recreate the constraint when adding new FK columns.

Needless to say about the ugliness of such a table. Don't speak about the main issue of letting unnecessary nullable columns in a table.

2) A second solution, to create join tables in between for each kind of activity, called ActivityPayments i.e. LoanPayments etc., that holds the activity ID and the payment ID, like Many-To-Many table.

There aren’t the problems described above, each payment is related to its corresponding activity, there are no referential integrity loss, no Nullable columns. The problem is however that it enlarges the Database, and adds another layer between the tables, and needs more work when joining in queries.

Has someone any idea?

Answer 1

Another option is to create a supertype table, say Activity, with all of the common attributes:

This should keep the number of tables small, and still allow you to identify the activity type for a payment. Note that this assumes that common attributes exist between the different activities. If that is not the case, the second option you listed is probably the way to go.

Answer 2

Look up the following tags in SO.

single-table-inheritance class-table-inheritance shared-primary-key

The info tab on these tags gives you a brief explanation, and the questions grouped under the tag will give you some examples.

Single table inheritance is similar you the solution you presented, and that you are unhappy with. Yes, it does involve NULLS. Generally, user errors here are prevented by the application.

Class-table-inheritance is like the solution offered by AMS. Note that SalesID and LoanID are listed as both a PK and an FK. This hints at the technique of shared primary key. With this, SalesID and LoanID are copies of a value in ActivityID. Again, it's the application layer that does the necessary work to mke sure the copies are right.

Answer 3

in this specific case (not necessarily applicable in similiar situations), we usualy calculate dynamically, in a view/function, each payment for what it was (in chronological order)

in other instances we had one sale table where each product can be a physical product or service or any other for-pay offer. so that limits all debit transactions to one tbale HTA