What to do when a Product is many things?

[SaranacLake]

I am trying to lay out table for an ecommerce site that sells both products and services.

In a simple ecommerce site, you would likely have tables which look like this...

CUSTOMER --||-----0<-- ORDERS --||-----|<-- ORDER_DETAILS -->0-----||-- PRODUCTS

 

My problems is that for PRODUCTS, I have to connect to things like Online Subscriptions, Online Books, and then traditional items like Paper Books and T-Shirts.

I am thinking that I almost need to use a supertype/subtype for PRODUCTS, but that seems rather complicated to do in MySQL or any database for that matter.

Good designs should be simple, but it seems that the nature of what I am selling requires a more complex solutions and I'm not quite sure how to tie in my own table structures to the PRODUCT table so people can purchase products and services all at once?!

 

 

[requinix]

23 minutes ago, SaranacLake said:

I am thinking that I almost need to use a supertype/subtype for PRODUCTS, but that seems rather complicated to do in MySQL or any database for that matter.

Not that complicated. You store the main "product" information like normal, and if the product needs anything extra then you can store a product "type" and use that to know what/when other tables are relevant.

[SaranacLake]

8 minutes ago, requinix said:

Not that complicated. You store the main "product" information like normal, and if the product needs anything extra then you can store a product "type" and use that to know what/when other tables are relevant.

So you aren't linking the tables with keys?

 

[requinix]

Yeah, there's a key. Products table has a type and key. Those two act as signals to your application('s code) telling it (a) the type of product and (b) where whatever other information the application needs is stored.

[SaranacLake]

17 minutes ago, requinix said:

Yeah, there's a key. Products table has a type and key. Those two act as signals to your application('s code) telling it (a) the type of product and (b) where whatever other information the application needs is stored.

So the "type" helps your application to know where to do an INSERT?

And are you saying that there would be a one-to-one mapping between the PRODUCT TABLE and say a THING_1 table or a THING_2 table?

 

 

[requinix]

28 minutes ago, SaranacLake said:

So the "type" helps your application to know where to do an INSERT?

Basically. When you create a new product there'll be a record in the main products table, obviously, then according to the product type there will be... whatever additional work needs to happen. Like a record stored in another table somewhere.

28 minutes ago, SaranacLake said:

And are you saying that there would be a one-to-one mapping between the PRODUCT TABLE and say a THING_1 table or a THING_2 table?

Yes.

[SaranacLake]

20 minutes ago, requinix said:

Basically. When you create a new product there'll be a record in the main products table, obviously, then according to the product type there will be... whatever additional work needs to happen. Like a record stored in another table somewhere.

Yes.

In the tables below...

	CUSTOMER --||-----0<-- ORDERS --||-----|<-- ORDER_DETAILS -->0-----||-- PRODUCTS
	

 

Would you agree that what is really important in the PRODUCT table is a way to uniquely identify the product (ProductD) and to have a description and current price for it?

 

 

[requinix]

2 minutes ago, SaranacLake said:

Would you agree that what is really important in the PRODUCT table is a way to uniquely identify the product (ProductD) and to have a description and current price for it?

Product ID obviously, description maybe, price maybe. Depends on your requirements.

[SaranacLake]

2 minutes ago, requinix said:

Product ID obviously, description maybe, price maybe. Depends on your requirements.

 

My point was that in the context of my e-commerce tables (i.e. what I listed above) the goal is to identify the ProductID and the unit_price for each product the user wants and to put that info into the ORDER_DETAILS table.

User is buying...  1 @ Premium Subscription, 2 @ "How to become Rich" Books, and 3 @ T-shirts.

And that info comes from your "main" PRODUCT table as you described above.  All of the other things related to different product types can be stored in the related product-type  tables.

[Barand]

Where you have various product categories it is useful to use an EAV data model (Entity attribute value)

Define the attribute for each category. Categories inherit attributes of their ancestors EG

+-----------------------------+---------------------+
| Category                    | Attribute           |
+-----------------------------+---------------------+
|  ALL                        | Price               |
+-----------------------------+---------------------+
|        Electrical           | Wattage             |
|                             | Brand name          |
+-----------------------------+---------------------+
|                      TV     | Screen size         | 
|                             | Resolution          | 
+-----------------------------+---------------------+
|                      Kettle | Capacity            | 
+-----------------------------+---------------------+

 

Edited September 11, 2019 by Barand

[Barand]

The above is a generic model. In your case, with only three categories and no hierarchy it can be simplified.

Keep the attribute, category and attribute tables as you need to know which attributes to define for each product.

CATEGORY                            CATEGORY_ATTRIBUTE                  ATTRIBUTE
+-------------+-------------+       +-------------+--------------+      +--------------+-----------+
| category_id | cat_name    |       | category_id | attribute_id |      | attribute_id | attr_name |
+-------------+-------------+       +-------------+--------------+      +--------------+-----------+
|           1 | Subscrption |       |           1 |            2 |      |            2 | Type      |
|           2 | Book        |       |           1 |            3 |      |            3 | Duration  |
|           3 | T-shirt     |       |           2 |            4 |      |            4 | Title     |
+-------------+-------------+       |           2 |            5 |      |            5 | Author    |
                                    |           3 |            6 |      |            6 | Size      |
                                    |           3 |            7 |      |            7 | Color     |
                                    |           3 |            8 |      |            8 | Style     |
                                    +-------------+--------------+      +--------------+-----------+

And then your product table using option #2 (JSON attributes column)

+------------+---------------------------------+-------------+------------------------------------------------------------------------------+
| product_id | prod_name                       | category_id | attributes                                                                   |
+------------+---------------------------------+-------------+------------------------------------------------------------------------------+
|          1 | Standard Membership (12 months) |           1 | {"Type": "S", "Duration": 12}                                                |
|          2 | Premium Membership (3 months)   |           1 | {"Type": "P", "Duration": 3}                                                 |
|          3 | Dystopian novel                 |           2 | {"Title": "Brave New World", "Author": "Aldus Huxley"}                       |
|          4 | Harry Potter Book               |           2 | {"Title": "Harry Potter and the Philosophers Stone", "Author": "JK Rowling"} |
|          5 | T-Shirt                         |           3 | {"Size": "M", "Color": "Red", "Style": "V-neck"}                             |
|          6 | T-Shirt                         |           3 | {"Size": "L", "Color": "White", "Style": "V-neck"}                           |
|          7 | T-Shirt                         |           3 | {"Size": "L", "Color": "Red", "Style": "V-neck"}                             |
|          8 | T-Shirt                         |           3 | {"Size": "L", "Color": "Black", "Style": "crew-neck"}                        |
|          9 | Harry Potter Book               |           2 | {"Title": "Harry Potter and the Goblet of Fire", "Author": "JK Rowling"}     |
+------------+---------------------------------+-------------+------------------------------------------------------------------------------+

As your only common attribute is price, have separate price table

+------------------+------------+-------+------------+-------------+
| product_price_id | product_id | price | valid_from | valid_until |
+------------------+------------+-------+------------+-------------+
|                1 |          1 | 86.10 | 2000-01-01 | 9999-12-31  |
|                2 |          2 | 50.72 | 2000-01-01 | 9999-12-31  |
|                3 |          3 | 95.31 | 2000-01-01 | 9999-12-31  |
|                4 |          4 | 24.38 | 2000-01-01 | 9999-12-31  |
|                5 |          5 | 35.99 | 2000-01-01 | 9999-12-31  |
|                6 |          6 |  6.80 | 2000-01-01 | 9999-12-31  |
|                7 |          7 | 26.03 | 2000-01-01 | 9999-12-31  |
|                8 |          8 |  9.77 | 2000-01-01 | 9999-12-31  |
|                9 |          9 | 70.75 | 2000-01-01 | 9999-12-31  |
+------------------+------------+-------+------------+-------------+

As an example query, list all T-Shirts size "L"

SELECT product_id
     , prod_name
     , attributes->>'$.Size' as size
     , attributes->>'$.Color' as color
     , attributes->>'$.Style' as style
     , price
FROM product_j p 
     JOIN
     product_price pp USING (product_id)
WHERE attributes->>'$.Size' = 'L' 
     AND category_id = 3
     AND CURDATE() BETWEEN valid_from AND valid_until;

+------------+-----------+------+-------+-----------+-------+
| product_id | prod_name | size | color | style     | price |
+------------+-----------+------+-------+-----------+-------+
|          6 | T-Shirt   | L    | White | v-neck    |  6.80 |
|          7 | T-Shirt   | L    | Red   | v-neck    | 26.03 |
|          8 | T-Shirt   | L    | Black | crew-neck |  9.77 |
+------------+-----------+------+-------+-----------+-------+