How to have a one-to-one relation to either one of two other tables

database-design

Suppose I have three tables

orders table, fields: name, address, country, paid, shipped, amount
paypal payment table, fields: order_id
stripe payment table, fields: customer_id, cvc_valid, address_valid, zip_valid

Each order should be associated with only one payment table, paypal or stripe

Question is: How do I associate an Order with its payment when it is possible that the payment is either in the Paypal table or in the Stripe table?

I thought of adding an enum field to the Orders table to decide whether to look up the payment in the Paypal table or to look up in the Stripe table. Are there better methods than this?

I would like to use either MariaDB, MySQL, or SQLite.

Best Answer

There are several options:

Combine Paypal and Stripe tables into a single Payment table, with fields to hold either type of payment;
Have both a paypal_id and a stripe_id in the Order table; use a condition on the table to ensure that at least one of them is NULL.
Have a single Payment_ID with a flag to indicate whether it's an id from Paypal or Stripe as you propose.

The first two options allow for the payment ID(s) to be foreign keys to the payment table(s), the third does not.

I would lean towards a single table with all payment data, but that may be a personal preference. Which of the first two options is better would depend on whether there are any common columns between Paypal and Stripe, and just how large the distinct columns are.

UPDATE: joanolo's answer lays out how to actually implement my option 2; take a look!

Related Solutions

Sql-server – Relationship that are optionally more specific

Instead of:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(LocationID) REFERENCES Locations(ID)

I would do this:

ALTER TABLE Donations ADD FOREIGN KEY(EventID) REFERENCES Events(ID)
ALTER TABLE Donations ADD FOREIGN KEY(EventID, LocationID) REFERENCES Locations(EventID, ID)

That would guarantee that "if the LocationID is specified, then that LocationID should be a Location that belongs to the specified EventID".

That done, your approach makes sense.

How to link a customer to a country when the city/state isn’t known

Use a variation of number 2 - a location table with a involuted (self-referencing)relationship, but add something to make the hierarchy management easier. For example, use nested sets/visitation numbers (see here) or an adjacency list.

The advantages of this approach are that:

You have a single foreign key from your transaction to your location table. This keeps your queries simple insofar as you don't need multiple joins and either/or logic.
The foreign key can point to as vague or as specific a location as you need.
You can easily traverse the hierarchy (since you'd be using a technique like nested sets) to get the level of geography of interest.
You can accommodate countries that have more or less than one geographical division between the country and the city, without having to create dummy entries or duplicating data.

I don't know what your ORM is, but it should be able to handle a self-referencing relationship and a partitioning attribute (i.e. GeographyLevel = { country, state, city })

Best Answer

Related Solutions

Sql-server – Relationship that are optionally more specific

How to link a customer to a country when the city/state isn’t known

Related Question