title: Designing an E-Commerce Database - MySQL
body: <p>I'm doing an e-commerce project and am confused about the database design for storing products. There are 3 ways I've speculated the database can be made:</p>
  
  <p>1.
  There can be separate tables for each product category.</p>
  
  <pre><code>Table: Categories
  ------------------
  cat_ID
  cat_name
  
  Table: Sub_Categories
  ---------------------
  sub_cat_ID
  categories_cat_ID
  sub_cat_name
  
  Table: Books
  -------------
  book_ID
  sub_categories_sub_cat_ID
  book_title
  book_author
  book_ISBN
  book_price
  etc
  
  Table: Clothes
  ---------------
  clothes_ID
  sub_categories_sub_cat_ID
  clothes_name
  clothes_color
  clothes_size
  clothes_description
  clothes_price
  etc
  
  Table: Perfumes
  ----------------
  perfumes_ID
  sub_categories_sub_cat_ID
  perfume_name
  perfume_size
  perfume_weight
  perfume_description
  perfume_price
  etc
  </code></pre>
  
  <p>2.
  Group all products together in one table and allow some values to be null</p>
  
  <pre><code>Table: Categories
  ------------------
  cat_ID
  cat_name
  
  Table: Sub_Categories
  ---------------------
  sub_cat_ID
  categories_cat_ID
  sub_cat_name
  
  Table: Products
  ---------------
  product_ID
  sub_categories_sub_cat_ID
  title
  description
  price
  author (can be null for everything except books)
  size
  weight (can be null for everything except perfumes)
  ISBN (can be null for everything except books)
  color (can be null for everything except clothes)
  etc
  </code></pre>
  
  <p>3.
  Group similar column fields together in a table called products, and provide separate tables for specific data.</p>
  
  <pre><code>Table: Categories
  ------------------
  cat_ID
  cat_name
  
  Table: Sub_Categories
  ---------------------
  sub_cat_ID
  categories_cat_ID
  sub_cat_name
  
  Table: Products
  ----------------
  product_ID
  sub_categories_sub_cat_ID
  title
  description
  price
  
  Table: Books
  -------------
  products_product_id
  sub_categories_sub_cat_ID
  author
  publisher
  ISBN
  
  Table: Perfumes
  ----------------
  products_product_id
  sub_categories_sub_cat_ID
  size
  weight
  
  Table: Clothes
  --------------
  products_product_id
  sub_categories_sub_cat_ID
  color
  size (this can be a one to many relationship to cater to multiple sizes of one product?)
  </code></pre>
  
  <p>I would really appreciate enlightenment, thank you</p>