title: Database schema design for products depending on the variants and prices
body: <p>We are in the process of designing a database for an e-commerce application and website. We reached an obstacle that has set us back in searching and experimenting but none of the solutions worked because there will be redundancy in the data.</p>
  <p>We have made some designs that we rejected. I will not show all the designs but I will show some of them here and one of the last designs that we were about to implement.</p>
  <ol>
  <li>This is the first design we rejected because it was over-normalized and it was over complicated.</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/SmxbH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SmxbH.png" alt="ERD" /></a></p>
  <ol start="2">
  <li>The second design was the same thing.</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/GVZ9U.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GVZ9U.png" alt="ERD" /></a></p>
  <ol start="3">
  <li>This is the last design we tried but it was over simplified and the redundancy will be a lot.</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/QIVqb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QIVqb.png" alt="ERD" /></a></p>
  <p>After that we tried searching and looking for a design that will work and suit our previous design to the other tables. We found one in here we did some modification to the design but also it didn't suit us because the redundancy that will happen with the color_id and size_id and it is not a foreign key.</p>
  <p><a href="https://i.stack.imgur.com/8hqZz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8hqZz.png" alt="ERD" /></a></p>
  <p>We want to design the tables that accept these cases:</p>
  <ol>
  <li><p>I have a product that have different colors and different sizes also the size doesn't depend on the color or vice-versa and they all have the same price.</p>
  </li>
  <li><p>I have a product where it has different colors and sizes but the sizes depends on the color for example (Color: Red) has (Sizes: S,M,L) and (Color: Black) has (Sizes: M,L) and they have different prices.</p>
  </li>
  <li><p>I have a product Where it has different sizes and the price change depends on the sizes for example product table (Size: S) and the (Price: 50$) but the (Size: L) has the (Price: 100$). But it doesn't have the color variant.</p>
  </li>
  </ol>
  <p>The products will not be inserted by us but by some sellers. The colors and sizes will be all choices in a dropdown-menu. And the sizes will be showed only depending on the categories. For example, Categories Tops will have only the (S,M,L,...) and doesn't have the other sizes</p>