Search code examples
laravellaravel-5database-designclass-design

Design data structure for products

I need to design the structure of the tables with the product data to meet the following requirements:

1. A product consists of the following fields: EAN, CN, description, pvp
2. There are several types of users that access the products. The user types are not stable, they can be added or deleted at any time.
3. Any of the fields of the products may vary depending on the type of user who views it. For example:

We have three users:

1 - John - guest
2 - David - client
3 - Vicent - vip

We have this product with this data by default:

8470001234567 - 123456 - Iphone X - $799

Guest users instead of seeing this data see the following:

8431239876547 - 987654 - Iphone X - $849

The client users see the data by default and the vip users see:

8470001234567 - 654321 - Iphone X Sale - $709

This means that a user sees the default data of a given product unless there is an exception for its type. The exception can affect any field (except the id).

I can think of this structure:

  • PRODUCTS: id, ean, cn, description, pvp
  • PRODUCT_EXCEPTION: product_id, user_type_id, ean, cn, description, pvp

I have verified that with this structure many queries are made, do you think of a way to optimize this so that it is not necessary to make so many queries?

Note 1: the products are contained within offers that have a certain number of products. Note 2: I use Laravel. The Offer model has a relationship with products, that is, I obtain the products in the following way: $offer->products()

Solution

  • Looking at just the problem description, I ended up with this, which is equivalent to what you are proposing with your Product_Exception:

    enter image description here

    Reasoning:

    • Since user types vary, you cannot put the values directly in the Product table. Ex. 3 user types == 3 prices. Not here. So you need a link table between Product and UserType.
    • The link table will contain the characteristics of 1 Product, for 1 UserType.
    • If you want to have a default value, you can put the characteristics in the Product table as well. But then your queries become bigger! Check if there is an exception value, if not use the default.

    So your solution makes sense to me.