We are using a entity-attribute-value schema to store objects in our project. It's basically like this:
entity- dummy storage of all entries prototypes (car, house etc.)
entry (representation of single entity object)
parameter (all entity attributes color, name, brand etc.)
value (single value for one entry and parameter)
These tables represent dynamic tables stored in MySQL database.
The only problem with this scheme is sorting over 2 (or more) parameters. For example sort all cars by manufacturing_year DESC and brand ASC.
The final result we need is the sorted list of entry ids.
For these objects:
entity
id | name
------------------------------
1 | Car
------------------------------
entry
id| entity_id
-------------
1 | 1
-------------
2 | 1
-------------
3 | 1
-------------
parameter
id| entity_id | name
-----------------------
1 | 1 | Brand
-----------------------
2 | 1 | Year
-----------------------
value
id | entry_id | parameter_id | value
----------------------------------------------
1 | 1 | 1 | Tatra
----------------------------------------------
2 | 1 | 2 | 2005
----------------------------------------------
3 | 2 | 1 | Aston Martin
----------------------------------------------
4 | 2 | 2 | 1999
----------------------------------------------
5 | 3 | 1 | Man
----------------------------------------------
6 | 3 | 2 | 2005
----------------------------------------------
The correct order of these entries by given criteria shoud be 3, 1, 2.
You can join parameter table as many times as you have parameters to sort on, with parameter id's as join conditions, so you get a simple rowset with values as columns for sorting.
SELECT entry.id as entry_id,
v_brand.value as brand,
v_year.value as year
FROM entity
JOIN entry
ON entity.id = entry.entity_id
JOIN value v_brand
ON v_brand.parameter_id = 1
AND v_brand.entry_id = entry.id
JOIN value v_year
ON v_year.parameter_id = 2
AND v_year.entry_id = entry.id
WHERE entity.name = 'Car'
ORDER BY year DESC, brand ASC