Better way to do MySQL Object layer

Question

I am not a pro in MySQL, but want to do something like Object Layer above relational MySQL tables.

I want to have very many "structures" with a fields of type "bigint", "longtext", "datetime", "double" stored in just 7 tables.

entity_types (et_id, et_name) - list of "structures";

entity_types_fields (etf_id, parent_et_id, ....., etf_ident, etf_type) - list of structure properties stored in one table for ALL structures; etf_type contains int value (0,1,2,3) which referenced to one of 4 tables described below.

entities (e_id, et_id) - list of all available entities (id and type id of entity)

and 4 data tables (containing all data for entities) -

entities_props_bigint (parent_e_id, parent_etf_id, ep_data) - for BIGINT data properties entities_props_longtext (parent_e_id, parent_etf_id, ep_data) - for LONGTEXT data properties entities_props_datetime (parent_e_id, parent_etf_id, ep_data) - for DATETIME data properties entities_props_double (parent_e_id, parent_etf_id, ep_data) - for DOUBLE data properties

What the best way to do selection from such data layer ?

Let I have list of e_id (id of entities), each entity can have any type. I want to get predefined list of properties. If some of entities don't have such property, I want to have it equal to NULL.

Do you have some info about how to do it ? May be you have some links or have already deal with such things.

Thanks!

Answer 1

You're reinventing the wheel by implementing a whole metadata system on top of a relational database. Many developers have tried to do what you're doing and then use SQL to query it, as if it is relational data. But implementing a system of non-relational data and metadata in SQL is harder than you expect.

I've changed the relational tag of your question to eav, because your design is a variation of the Entity-Attribute-Value design. There's a limit of five tags in Stack Overflow. But you should be aware that your design is not relational.

A relational design necessarily has a fixed set of attributes for all instances of an entity. The right way to represent this in a relational database is with columns of a table. This allows you to give a name and a data type to each attribute, and to ensure that the same set of names and their data types apply to every row of the table.

What the best way to do selection from such data layer ?

The only scalable way to query your design is to fetch the attribute data and metadata as rows, and reconstruct your object in application code.

SELECT e.e_id, f.etf_ident, f.etf_type, 
    p0.ep_data AS data0, 
    p1.ep_data AS data1, 
    p2.ep_data AS data2,
    p3.ep_data AS data3
FROM entities AS e
INNER JOIN entity_type_fields AS f ON e.et_id = f.parent_et_id
LEFT OUTER JOIN entities_props_bigint   AS p0 ON (p0.parent_e_id,p0.parent_etf_id) = (e.e_id,f.etf_id) 
LEFT OUTER JOIN entities_props_longtext AS p1 ON (p1.parent_e_id,p1.parent_etf_id) = (e.e_id,f.etf_id) 
LEFT OUTER JOIN entities_props_datetime AS p2 ON (p2.parent_e_id,p2.parent_etf_id) = (e.e_id,f.etf_id) 
LEFT OUTER JOIN entities_props_double   AS p3 ON (p3.parent_e_id,p3.parent_etf_id) = (e.e_id,f.etf_id) 

In the query above, each entity field should match at most one property, and the other data columns will be null. If all four data columns are null, then the entity field is missing.

---

Re your comment, okay now I understand better what you are trying to do. You have a collection of entity instances in a tree, but each instance may be a different type.

Here's how I would design it:

• Store any attributes that all your entity subtypes have in common in a sort of super-type table.

entities(e_id,entity_type,name,date_created,creator,sku, etc.)

• Store any attributes specific to an entity sub-type in their own table, as in Martin Fowler's Class Table Inheritance design.

entity_books(e_id,isbn,pages,publisher,volumes, etc.)

entity_videos(e_id,format,region,discs, etc.)

entity_socks(e_id,fabric,size,color, etc.)

• Use the Closure Table design to model the hierarchy of objects.

entity_paths(ancestor_e_id, descendant_e_id, path_length)

For more information on Class Table Inheritance and Closure Table, see my presentations Practical Object-Oriented Models in SQL and Models for Hierarchical Data in SQL, or my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming, or Martin Fowler's book Patterns of Enterprise Application Architecture.