Optional structure sql

Question

DB world is not mine, so perhaps the question is trivial

Imagine designing a DB that store data from different kinds of items (like datatypes)

I have no idea how I should do it, but this is how I'm making it

id | quantity | price ... Kind | ID_k_foreing |

then for every kind of items would be a table with properties of each type

so via software I could compare the kind and then make the joins to the appropriate table

like this pseudocode

switch(kind)
{
case chess_game:
      the join is made with a table like this:
      id_k| material | length | weigth ..
case car_toy:
      the join is made with a table like this:
      id_k| color | velocity | remote_control ...
case doll:
      the join is made with a table like this:
      id_k| name | height | clothes ..

...

}

There is some standar way to solve this "data type" problem in structure without adding tricky software functions?

thanks

score 4 · Accepted Answer · edited May 23 '17 at 12:04

You might want to take a look at this question.

Bill Karwin's answer breaks it down this way.

You have at least these five options for modeling the type hierarchy you describe:

Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types. This means a lot of columns, most of which are NULL on any given row.

Class Table Inheritance: one table for Products, storing attributes common to all product types. Then one table per product type, storing attributes specific to that product type.

Concrete Table Inheritance: no table for common Products attributes. Instead, one table per product type, storing both common product attributes, and product-specific attributes.

Serialized LOB: One table for Products, storing attributes common to all product types. One extra column stores a BLOB of semi-structured data, in XML, YAML, JSON, or some other format. This BLOB allows you to store the attributes specific to each product type. You can use fancy Design Patterns to describe this, such as Facade and Memento. But regardless you have a blob of attributes that can't be easily queried within SQL; you have to fetch the whole blob back to the application and sort it out there.

Entity-Attribute-Value: One table for Products, and one table that pivots attributes to rows, instead of columns. EAV is not a valid design with respect to the relational paradigm, but many people use it anyway. This is the "Properties Pattern" mentioned by another answer. See other questions with the eav tag on StackOverflow for some of the pitfalls.

You'll have to sort out which one works for you.

Optional structure sql

1 Answers1