Database design for ecommerce site with many product categories

Question

I've a requirement to design a database for an ecommerce app that has vast scope of product categories ranging from pin to plane. All products have different kinds of features. For example, a mobile phone has specific features like memory, camera mega pixel, screen size etc whilst a house has land size, number of storeys and rooms, garage size etc. Such specific features go on and on as much as we've products. Whist all have some common features, there are mostly very different and specific features of all. So, it has gotten bit confusing while designing its database. I'm doing it for the first time.

My query is about database design. Here is what I'm planning to do:

1. Create a master table with all fields, that tells if a field is common or specific and map them with respective category of the product. All products will have "common" fields but "specific" will be shown only for one category.

table: ALL_COLUMNS

columns: id, name, type(common or specific), category(phone, car, laptop etc.)

2. Fetch respective fields from all_columns table while showing the fields on the front.

3. Store the user data in another table along with mapped fields

table: ALL_USER_DATA

columns: id, columnid, value

I don't know what is the right way and how it is done with established apps and site. So, I'm looking forward if someone could tell if this is the right way of database architecture of an ecommerce app with highly comprehensive and sparse set of categories and features.

Thank you all.

Answer 1

There are many possible answers to this question - see the "related" questions alongside this one.

The design for your ALL_USER_DATA table is commonly known as "entity/attribute/value" (EAV). It's widely considered horrible (search SO for why) - it's theoretically flexible, but imagine finding "airplanes made by Boeing with a wingspan of at least 20 metres suitable for pilots with a new qualification" - your queries become almost unintelligible really fast.

The alternative is to create a schema that can store polymorphic data types - again, look on Stack Overflow for how that might work.

The simple answer is that the relational model is not a good fit for this - you don't want to make a schema change for each new product type your store uses, and you don't want to have hundreds of different tables/columns.

My recommendation is to store the core, common information, and all the relationships in SQL, and to store the extended information as XML or JSON. MySQL is pretty good at querying JSON, and it's a native data type.

Your data model would be something like:

Categories
---------
category_id
parent_category_id
name

Products
--------
product_id
price
valid_for_sale
added_date
extended_properties (JSON/XML)

Category_products
-----------------
category_id
product_id