Mysql – Database design for an E-commerce website

database-designMySQL

I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand whether I need specify all the specifications in a table or do I need to use different tables for different products?

For example, consider the products Mobile and book, each of these having unique specifications (such as color,size, cost, model for mobile and title, ISBN, author, cost, year_of_publication, etc. for book), if it is only less number of products then it is possible for me to design, but when there is a thousands of products, it takes a lot of time.

Can anyone tell me how to design my database to manage this situation?

Best Answer

If you know most of the attributes your products will have ahead of time, you could hard-code them into the tables for different product types, such as:


books
-----
  id
  price
  title
  author
  isbn
  etc....

mobile_device
-------------
  id
  price
  size
  colour
  model
  etc...

You could also try something like this:


base_product
------------
  id
  base_price
  product_type

book_product_attributes
-----------------------
  base_product_id (FK to base_product)
  title
  author

mobile_dev_product_attributes
-----------------------------
  base_product_id (FK to base_product)
  model
  colour

This way, you can group your common attributes in a base product table, and more specific attributes are stored in other tables when necessary.

This will work if your product types are mostly static. On the other hand, if you have no idea what product types or attributes you'll need in the future, and entity-attribute-value based system might work better. The idea behind that model is that you have a table for the different attributes you might have, a table for the different entities in your system, and a table for the attribute values that an entity has. Example:

entities
--------
  id

attributes
----------
  id
  name

values
------
  id
  entity_id
  attribute_id
  value

The data might look like this:


entities
ID
----
  1

attributes
ID |  name   
----------
1  |  title
2  |  author


values
ID  | attribute_id   | entity_id   | value
---------------------------------------------------------
1   | 1              | 1           | "Great Expectations"
2   | 2              | 1           | "Charles Dickens"

This data very briefly describes a product which has two attributes: title, and author, with the valus "Great Expectations" and "Charles Dickens", respectively.

Be aware that using an EAV database can make queries very awkward. With enough data and the wrong model design, it's also possible to run into performance problems. The example I gave was very simple but more realistic designs tend to be more complicated than that. It can take time to get this kind of database correct and it's not always the best solution.

Related Solutions

Database Design – Same Products in Inventory with Different Cost

So, should I relate the sale of each product with the identifier of the table PURCHASE instead of the identifier in PRODUCT table?

I think it is better choice to have of purchase_id as FK in SALE_ITEM table in oppose to product_id, because sell is something related to PURCHASE and PRODUCT both and this option offer you this relation, you can get the corresponding product_id and information related to PRODUCT by making a join in sql query.

Or, should I force the client to sell the old lot before selling the new ones?

Don't add restrictions with your design, let them give flexibility to choose the way they want to. Even this part can be handled with sql query incase needed.

MySQL Database Design – How to Design a Website Database Structure

What you could do is a design like this:

Product(id,name,description,price,productType)
ProductAttribute(productId,attributeName,value)

Then you would have the possibility to filter for all attributes that are currently used in your database.

If you want to be able to filter for all attributes, even if they are not used in any product at the moment, you'll have to add a table

AttributesForProductType(productType,attributeName)

which contains all possible attributes, and then set up referential integrity (foreign keys) between AttributesForProductType and ProductAttribute. This way you lose the risk of misspelling an attribute name in the ProductAttribute table.

edit: If you take this design this also solves your second question: How to display a details page for every possible product type?

All possible attributes for this product are

select attributeName
from AttributesForProductType
where productType=?

all real attributes for a product with their values are

select attributeName, value
from ProductAttribute
where id=?

and those combined are (off of my head, untested)

select apt.attributeName, pa.value
from Product as p
left outer join AttributesForProductType as apt on p.productType=apt.ProductType
left outer join ProductAttribute as pa on apt.attributeName=pa.attributeName
where p.id=?

Best Answer

Related Solutions

Database Design – Same Products in Inventory with Different Cost

MySQL Database Design – How to Design a Website Database Structure

Related Question