Data model for subscriptions, single purchase products and variable services

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

Have you looked at sub-typing - it might work for you. By this I mean a central "order" table containing the common attributes and optional "is a"/one-to-one relationships to specific order tables. The specific order tables contain the attributes specific only to that type of order, and a foreign key back to the central order table.

This is one way of trying to get the best of "both" worlds, by which I mean (a) the appropriate level of centralisation for reporting on things common and (b) the right level of specialisation. The added nuance comes at the cost of an extra join some times but gives you flexibility and reporting.

Although I am a little biased in favour of subtypes, some people feel that unless your order subtypes are very different, it may not be worth the effort of separating them out. There is some seemingly good discussion here on dba.stackexchange as well as here. That being said, books (or chapters at least) have been written on the subject!