Database schema designing help - Mysql

Question

Ok need some help,

I have a system I'm creating that will allow users to purchase site credits and earn them, and with these credits order products, and access features that may be for sale. My issue is creating the orders and transactions tables. I have 2 separate order tables one for site credits (sitecredit_orders) and one for product orders (product_orders) and then the 1 transaction table that i want to be able to link to an order by my dilemma is i can't just make one field for orders_id because they may overlap in the 2 different tables. And can't merge the 2 orders table because they hold 2 different types of information. How should I go about creating and linking the transaction table to these orders tables? Thanks.

Answer 1

Just to expand on my comment:

An Orders table with OrderId and OrderType ("Product" or "Site Credit"; this is called the subtype discriminator).

A Sitecredit_Orders table with OrderId (foreign key to Orders.OrderId) and all the other fields needed for site credits.

A Product_Orders table with OrderId (foreign key to Orders.OrderId) and all the other fields needed for products.

A Transaction table with OrderId (foreign key to Orders.OrderId).

When you add a new site credit or product order, you will need to insert to two tables instead of one. Insert a new record in Orders, get the OrderId (assuming it's auto-generated), and then insert a record in one of the subtype tables using the same OrderId.