how to implement one to one relationship from one table to two tables?

Question

I have Mysql database with two tables:
One is table of payments_by_check and the other is payments_by_credit_card.

There is an option to cancel everyone of them, so I created a new table for cancellations.
every check payment or credit card payment may have record in cancellation table, and may not have it.

I don't know what is the correct way to build it, the options are:

1. Adding column of cancellation id in everyone of the payments table.
2. Adding in cancellation table one column for payment by check id, and another one for payment by credit card id, and every record will have one of them empty.

The payments tables are very large, so I'd rather avoid adding column to those table.

My question is:
Is it correct to take the second option?
Does it make any effect on performances?

Answer 1

As @Walter Mitty is mentioned, a normal solution to the problem will be something like:

In the case that such a restructure is not available, then:

Is it correct to take the second option?

Both 1 & 2 will have some problems, yet both can be applied based on your needs.

Solution No.1: Adding column of cancellation id to payments tables

Design problem:

1. A cancellation record can exist, without no related record in payment tables.
2. A cancellation record can exist, with more than one related record in payment tables.

Performance problem:
Creation of a cancellation record will need one insert inside cancellation and one update in related payment record.

Solution No.2: Having payments FK inside Cancellation table.

Haveing two null-able foreign key columns, yet one must be filled, just a check constraint is needed to achieve this.

Design problem:

Performance problem:
Detecting if a payment record is cancelled will need a query form payment joined to cancellation table.

In case of read performance No.1 is preferred.
In case of data consistency + write performance No.2 is preferred.

Another mixed solution that I will prefer is using No.2 solution plus having a column called is-cancelled in payment tables(to overcome read performance)

Answer 2

Payments by check and payments by credit card is a classic case of what is called "generalization/specialization". This is the equivalent, roughly, of classes and subclasses in object modeling. You can find some good articles on how to include gen-spec in an ER model by searching the web.

Things get interesting when you go to implement this design with relational tables. There are two widely used approaches: Single-table-inheritance and Class-table-inheritance. There are two tabs with these names in StackOverflow. If you check the info under these tabs you'll get an overview. You can also look these up on the web. I particularly like Martin Fowler's treatment. Each alternative has its benefits and drawbacks.

In your case, I would use a Single-Table-Inheritance approach, with just one Payments table for both kinds. You'll have to have a column to say what kind each payment is, plus a few columns that only pertain to Credit card payments, and a few that only pertain to check payments.

But it's your call. If you decide to go with Class-table-inheritance instead, and you use Shared-Primary-Key to Share Ids across all three tables, you'll find that wotks pretty well,too.

Answer 3

Payments table is large. Cancellations table is much smaller, correct? That is, Cancellations would have rows only for cancellations, not non-cancelled payments.

Cancellations has a column for JOINing to Payments, correct? So, it does not really need to include the payment_type or amount. Just cancellation_date and some admin stuff.

With two tables, LEFT JOIN or UNION can put them together when you need to see both bits of info. So, that is not a 'real' problem, just a coding nuisance.