Storing different info for different kinds of products

G’day mates,

I have a database schema problem I’m not sure how to solve. I have several very distinct things to manage: trainings, consultations, products, courses… The problem is this: all of these things are “products”, in that they can be ordered and can be line items. But not all of them are the same in terms of the kind of information that needs to be associated with them.

For instance, for trainings I would want a table that has the date and time of the training, the location, the instructor, etc., whereas for ordinary products there is no such need. Obviously there needs to be only one “products” table, but with all these different kinds of products, it’s not as simple as your typical ID, name, description, price, and so on. How can I gracefully accommodate the differing types of information associated with all these things and still maintain them all as “products” that can be ordered?

Any help is greatly appreciated!!!

Hi Rudy,

Thanks so much, that looks like exactly what I needed!

do a search for supertype/subtype

the products table should be your supertype table, holding all the columns that all “products” have in common, and the trainings table would be one of the subtype tables, which has only those columns unique to that subtype