管理数据库中多种类型产品的付款

Question

I have multiple type of digital goods that can be bought from site.(Passes and tickets).

I want some suggestions regarding what should be the database structure of such payments .Table used for storing payments is purchase history

OPTION 1

1st option can be as shown below in which item id of each product will be in seperate column. For each payment only that column of that product is populated which is purchased .

OPTION 2 In second option Id of product will be store in 1 column and type of product in other.

Please suggest which option is better or can there be any other method which is better and efficient than these ?

Answer 1

Option 2 is scale-able, option 1 isn't.

If you went with option 1, then decided to add a dozen more types of products, you'll need a new table for each, a new column in purchase_history for each, and to rewrite most of your queries and reports to suit.

Option 2 allows you not to worry about this, even if you stick with just the two types of products forever - at least you've only got one column to deal with in your purchase history table.

---

If it were me, I'd be combining the tickets and passes tables into a "products" table, and have an ENUM or similar field to differentiate them. Sure, the tickets table has more fields which passes doesn't need - but passes doesn't have any that tickets doesn't need, and you can just make the extras nullable and leave them there. Then just use product_id in your purchase history and do away with the product type table and field completely.