I know this is an old topic, but I think that it never loses relevance. I'm developing something now. Here is my approach. I use server setup with MySQL, Apache, PHP and Zend Framework 2 as an application platform, but it should work with any other settings as well.
Here is a simple implementation guide, you can further develop it.
You will need to implement your own query language interpreter because efficient SQL will be too complex.
Example:
select id, password from user where email_address = "xyz@xyz.com"
Layout of the physical database:
Table "specs": (should be cached at your data access level)
- id: int
- parent_id: int
- Name: varchar (255)
The table 'items':
- id: int
- parent_id: int
- spec_id: int
- data: varchar (20,000)
Table of Contents Specifications:
- 1, 0, 'user'
- 2, 1, 'email_address'
- 3, 1, 'password'
Table of Contents:
- 1, 0, 1, ''
- 2, 1, 2, 'xyz@xyz.com'
- 3, 1, 3, 'my password'
Translation of the example into our own query language:
select id, password from user where email_address = "xyz@xyz.com"
for standard SQL it will look like this:
select parent_id, -- user id data -- password from items where spec_id = 3 -- make sure this is a 'password' item and parent_id in ( -- get the 'user' item to which this 'password' item belongs select id from items where spec_id = 1 -- make sure this is a 'user' item and id in ( -- fetch all item id with the desired 'email_address' child item select parent_id -- id of the parent item of the 'email_address' item from items where spec_id = 2 -- make sure this is a 'email_address' item and data = "xyz@xyz.com" -- with the desired data value ) )
You will need to have a specification table cached in an associative array or hash table or something similar to get the spec_id from the specification names. Otherwise, you will need to add some more SQL overhead to get the spec_id from the names, for example, in this snippet:
Bad example, don’t use this, avoid it, cache the specification table instead!
select parent_id, data from items where spec_id = (select id from specs where name = "password") and parent_id in ( select id from items where spec_id = (select id from specs where name = "user") and id in ( select parent_id from items where spec_id = (select id from specs where name = "email_address") and data = "xyz@xyz.com" ) )
I hope you understand this idea and you can decide for yourself whether this approach suits you.
Enjoy !:-)