Database Design Strategy : Product Table on E-Commerce System (EAV, Class Table Inheritance or Concrete Table Inheritance)

Question

Hello guys!

My E-Commerce System will have 5 bases type of products:

1. Cell Phones
2. Computers
3. Shoes
4. Shirts
5. Default (No specific attribute)

Each one have specific attributes associated...

What I done (Class Table Inheritance):

Product
   Id
   Name
   Sku
   Price
   ...   

Shoe
   ProductId 
   Size
   Color
   ...

Computer
   ProductId
   Memory
   Processor
   ...

What you think about that design? I think Its not a good idea, because it will make the code I bit complicated, example :

-The system shows all Products in a page

-Now a customer select a Shoe

-I got the ProductID and have to check if it is a Cell Phone or Computer Or Shoe ...

I don´t know, maybe I´m thinking wrong...

And I think EAV is not a good option at all...

What you guys think?

Thanks

Answer 1

There are at least three approaches to representing class inheritance in a database

• Single Table Inheritance: All attributes in the whole inheritance tree are stored in a single table and there is a dedicated column which describes the type of the row. This means that there will be many unused columns, so this approach makes sense only when subclasses share most of the attributes.
• Class Table Inheritance: Every class in the tree is persisted in a separate database table which stores only attributes specific to the class. This means that in order to fetch an object your have to join across tables representing ancestors in the inheritance tree.
• Concrete Table Inheritance: There is a separate database table for each concrete class, but all attributes needed by a given class are stored, including the inherited ones. This means you don't have to join, but on the other hand you cannot upcast (eg. when you query for products you won't see any shoes).

Each of these approaches has pros and cons, it's a trade-off that you have to make.

On the other hand using inheritance to represent the product type means that you'll have to modify both the code and the database schema every time you introduce a new product type. If the types are going to change a lot and there is not much logic associated with each type, then it might be better to use a single (producId, key, value) table of product properties. It's not a beautiful database design, but it's going to be much more practical in such case.

Answer 2

Your design is the correct standard for today. It's called Normalization, breaking up objects into smaller object tables. That's the whole purpose of a relational database. I would stay on the path you are on

Answer 3

Your object design is good.

However, I suggest using single table inheritance (as mentioned by Adam) and steer clear away from EAV. If your RDBMS supports XML types and you're worried about storage of all the NULLable fields, put all the optional data in a single XML column. Schema validation will then ensure that you don't try to store a processor type with a pair of shoes, etc.

Adding extra fields per product type is then as simple as updating the schema for that particular product.