I've been thinking about creating a database that, instead of having a table per object I want to represent, would have a series of generic tables that would allow me to represent anything I want and even modifying (that's actually my main interest) the data associated with any kind of object I represent.
As an example, let's say I'm creating a web application that would let people make appointments with hairdressers. What I would usually do is having the following tables in my database :
- clients
- hairdressers: FK: id of the company the hairdresser works for
- companies
- appointments: FK: id of the client and the hairdresser for that appointment
But what happens if we deal with scientific hairdressers that want to associate more data to an appointment (e.g. quantity of shampoo used, grams of hair cut, number of scissor's strokes,...) ?
I was thinking instead of that, I could use the following tables:
- entity: represents anything I want. PK(entity_id)
- group: is an entity (when I create a group, I first create an entity which id is then referred to by the FK of the group). PK(group_id), FK(entity_id)
- entity_group: each group can contain multiple entity (thus also other groups): PK(entity_id, group_id).
- role: e.g. Administrator, Client, HairDresser, Company. PK(role_id)
- entity_role: each entity can have multiple roles: PK(entity_id, role_id)
- metadata: contains the name and type of the metadata aswell as the associated role and a flag that describes if its mandatory or not. PK(metadata_id), FK(metadata_type_id, role_id)
- metadata_type: contains information about available metadata types. PK(metadata_type_id)
- metadata_value: PK(metadata_value_id), FK(metadata_id)
- metadata_: different tables for the different types e.g. char, text, integer, double, datetime, date. PK(metadata__id), FK(metadata_value_id) which contain the actual value of a metadata associated with an entity.
- entity_metadata: contains data associated with an entity e.g. name of a client, address of a company,... PK(entity_id, metadata_value_id). Using the type of the metadata, its possible to select the actual value of a metadata for this entity in the corresponding table.
This would allow me to have a completely flexible data structure but has a few drawbacks:
- Selecting the metadatas associated with an entity returns multiple rows that I have to process in my code to create the representation of the entity in my code.
- Selecting metadatas of multiple entities requires to loop over the same process as above.
- Selecting metadatas will also require me to do a select for each one of the metadata_* table that I have.
On the other hand, it has some advantages. For example, instead of having a client table with a lot of fields that will almost never be filled, I just use the exact number of rows that I need.
Is this a good idea at all?
I hope that I've expressed clearly what I'm trying to achieve. I guess that I'm not the first one who wonders how to achieve that but I was not able to find the right keywords to find an answer to that question :/
Thanks!
