Databases
StackOverflow2013
Postsdbo
PODatabase normalization for shoes online shop
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase normalization for shoes online shop
    primarykey
    data
    text
    <p><strong>UPDATE</strong></p> <p>Here's my current database diagram, for online shoes that would sell</p> <ul> <li>shoes that will have different categories(heels, wedges, sandals, heels discount, etc) </li> <li>each shoes will have different color, different size. for each color and size, </li> <li>it will have different stock and maybe have different order type(Ready stock / Pre order)</li> <li>for each size and color, maybe it will have different price</li> <li>and each shoes with different size and color, they will(maybe) have different image rather than the generic ones</li> </ul> <p><img src="https://i.stack.imgur.com/pJFwJ.png" alt="enter image description here"></p> <p>am i doing it correctly? what should i do to improve this database? i really really appreciate any feedback/input/critics</p> <p><strong>END UPDATE</strong></p> <p>im newbie in database design and confused with database design for an online shop(MySQL database)</p> <p>Say that the online shop will sell shoes with different color, size, different stock and price for each color and size.</p> <p>i came up with these tables:</p> <p><strong>generic_shoes</strong></p> <pre><code>id name category_id details image </code></pre> <p><strong>colors</strong></p> <pre><code>id color </code></pre> <p><strong>sizes</strong></p> <pre><code>id size size_details </code></pre> <p><strong>specific_shoes</strong></p> <pre><code>id generic_shoes_id //references to generic_shoes name // if the owner wants to make another name, if not then use generic_shoes' name order_type //ready stock or pre order </code></pre> <p>then i come up with pivot colors_shoes and sizes_shoes with many to many rel: <strong>colors_specific_shoes</strong></p> <pre><code>id color_id specific_shoes_id </code></pre> <p><strong>sizes_specific_shoes</strong></p> <pre><code>id size_id specific_shoes_id </code></pre> <p>then im thinking that diff color and size will have diff stock and maybe different price, so i come up with:</p> <p><strong>shoes_product_details</strong></p> <pre><code>id //will be used in the orders table colors_shoes_id //reference to pivot colors_shoes sizes_shoes_id //reference to pivot sizes_shoes specific_shoes_id //reference to specific_shoes stock //each color and size will have diff stock price //each color and size maybe will have diff price weight //each size will have different weight </code></pre> <p>i tought these tables would do just fine, but then im thinking that, should i use this table instead? </p> <p><strong>remove</strong> :</p> <ol> <li>sizes_specific_shoes</li> <li>colors_specific_shoes</li> </ol> <p>and just use the color id and size id in the specific_shoes_details table: (in the table below, i just reference directly to colors and sizes table)</p> <p><strong>specific_product_details table</strong></p> <pre><code>id //will be used in the orders table color_id //reference to colors table size_id //reference to sizes table specific_shoes_id //reference to specific_shoes stock //each color and size will have diff stock price //each color and size maybe will have diff price weight //each size will have different weight </code></pre> <p>i think the problem is that with the last table is -> foreign key cascade if the color/size deleted then it will also delete the specific_shoes_details row.</p> <p>anyone can give me better approach? because i dont know which method should i use, the first one with color and size pivot table or the last one..</p> <p>thanks</p> <p>i would really appreciate any help/feedback</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmohur
    1. USmohur
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODatabase normalization for shoes online shop
      1. USmohur
      1. VTBountyStart
    2. VO
      singulars
      1. PODatabase normalization for shoes online shop
      1. USfagace
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload