订阅，单一购买产品和可变服务的数据模型

Question

I am designing a database for a system that will handle subscription based products, standard one off set price purchase products, and billing of variable services.

A customer can be related to many domains and a domain may have many subscriptions, set priced products or billed variable services related to it.

I am unsure whether to seperate each of these categories into their own 'orders' table or figure out a solution to compile them all into a single orders table.

Certain information about subscriptions is required such as start date or expiry date which is irrelevant for stand alone products. Variable services could be any price so having a single products table would mean I would have to add a new product which may be never used again or might be at a different cost.

What would be the best way to tackle this, and is splitting each into seperate order tables the best way?

Answer 1

As with any data model, the "best" way depends on your needs. Since you don't specify a lot of details regarding your specific needs, it's difficult to say what the bets model is.

However, in general you need to consider what level of detail is necessary. For example if all subscriptions cost the same and are billed on the 1st of the month, it may be sufficient to have a field like is_subscription ENUM ('Y', 'N') in your orders table. If billing dates and prices for subscriptions can vary however, you need to store that information too. In that case it may be better to have a separate table for subscriptions.

Another consideration is exactly what an "order" represents in your model. One interpretation is that an order includes all the purchases included in one transaction, including both one-off purchases, variable services and subscriptions. A completed order would then result in a bill, and subscriptions would be automatically billed on the proper day of the month without a new order being made.