One approach would be to use something similar to what they do in object oriented programming with base classes and derived classes.
In your case, you would have base "Products" table which has columns that are common to all product types - such as a productid (which likely would be the primary key), name, and perhaps productType (or productTypeId), and so on.
Then there would be other tables such as Product_Subscriptions, Product_Services, Product_Goods etc. Each of these tables would have a productid column which is a foreign key from the base Products table. Each would have columns that are specific to that type of "product".
You can use variations on this concept - for example, if 90% of your items are goods, then the table can have all the columns of that goods may need, and you wouldn't create a Product_Goods table. Or, if a good percentage of your products have a certain characteristic, but the remaining small percentage do not, you might choose to have that column in the base Products table.
I have used this pattern for some of my financial applications. There are different kinds of investment instruments such as Stocks, Bonds, Options, CDS, Swaps etc. There are many common properties (e.g. Ticker), but there are properties specific to each type as well (e.g. expiration date for options).
If I follow what you are saying, the relationship between the "base table" and "derived table" would be one-to-one, right?
How is this different from using supertype/subtype tables?
Yeah, that is sort of what I am facing.
Looking at this from a different angle, this is how I see things...
Currently, I have a "cluster" of tables (Can you think of a better name??) that relate to Subscriptions. And I have a second "cluster" of tables related to Online Subscriptions. And so on...
Each of these "clusters" really describes how these work. For example, how an online Subscription allows people with different Membership Plans to access different Featurs on my website.
These existing "clusters" have ]b]nothing[/b] to do with buying them!
So now I think I have things fairly well defined as to how things work, and am now switching to how I can allow a user to add a Subscription and an Online Book (really another type of subscription because it requires access) and a T-shirt and fit it into this common paradigm...
[quote="CatB, post:3, topic:16429"] You can use variations on this concept - for example, if 90% of your items are goods, then the table can have all the columns of that goods may need, and you wouldn't create a Product_Goods table. Or, if a good percentage of your products have a certain characteristic, but the remaining small percentage do not, you might choose to have that column in the base Products table.
How is this different from using supertype/subtype tables? [/quote]
It isn't, that is a supertype and subtype model.
I don't see how the supporting table clusters affect the main PRODUCTS table(s) design at all. You can still use the same tables to store the core product info. Of course make sure every product has a unique id, across all supertypes and subtypes.
Okay, let me reveal a bit more so it makes sense...
For a Subscription, I have...
MEMBER --||------|<-- SUBSCRIPTION -->O------||-- MEMBERSHP_PLAN_PROMOTION -->|------||-- MEMBERSHIP_PLAN
MEMBER
- id
- name
SUBSCRIPTION
- id
- member_id
- membership_plan_promotion_id
- purchase_price
- contract_start_date
- contract_end_date
MEMBERSHIP_PLAN_PROMOTION
- id
- membership_plan_id
- price
- contract_term
- start_date
- end_date
MEMBERSHIP_PLAN
- id
-name
Subscriptions are tricky because you have the MEMBERSHIP_PLAN_PROMOTION in there and I'm not sure how to link that into the above ecommerce checkout cluster.
A Member does purchase a Membership_Plan, but ultimately they purchase a Membership_Plan_Promotion (e.g. "One month of Basic Digital for $1" or "One year of Basic Digital for 1 year").
So how would I connect my "Subscription cluster" with my "E-commerce cluster"?
On a side note, would you agree with me that really what the PRODUCT table is really trying to accomplish is to link a unique Product (i.e. ProductID), plus a Description and Price to the ORDER_DETAILS table, and that everything else about the PRODUCT is immaterial to the e-commerce cluster??
No. The PRODUCT table stores core/"base" information on the product. Intersection data between a product and an order would be stored in a completely different table (called an "intersection table", for obvious reasons).
You're talking about an intersection table, but in my OP I posted an ERD with an ORDER_DETAILS table...
In my last post, maybe I should have said, "In the context of the e-commerce cluster, what is needed from the PRODUCT table is really just the ProductID and unit_price."