Jump to content
SaranacLake

What to do when a Product is many things?

Recommended Posts

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?!

 

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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?

 

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

 

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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            | 
+-----------------------------+---------------------+

image.png.1f5b475171dde4809fd13357f7bf0077.png

 

Edited by Barand

Share this post


Link to post
Share on other sites

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 |
+------------+-----------+------+-------+-----------+-------+

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.