MySQL: Entitie-List with up to unlimeted 'properties' of diffrent datatype
Hello all together,
i ran into a problem about database design i don't know to solve on my own anymore, as of this i'd like to consult you
I have a list of entities* wich may have an increasing (so up to unlimited) amount of properties.
* The list of entities may change as of addition and deletion of entities..
The properties have diffrent datatypes (basicly text or numbers) and in somecases there are additional information such as notes concerning the value or an date of the value. (~ "Multifield Properties" -.- )
In some cases there is no property value available for a specific entity.
Get a result of selected/all entities with selected/all properties. (the query itself might be generated by some script that knows the list of entities and all available properties).
I could put everything into one table, however due to the many properties i may run out of fields, and with the mentioned extra information it will get quite messy.
+ easy to query
+ no datatype issues
- limitation in fields
The second possibility would be to have an extra table for each property or group of properties, that would be kinda comfortable to query with simple joins, but gets messy on the tables side, and is basicly the first structure splitted into multiple tables :/
+ no datatype issues
= meets the "all entities with selected properties" requirement with almost plain sql
- lots of tables
What i ended up for now, is a three table structure:
Relational database are designed to enforce business rules and maintain relational integrity. They are not intended to allow any user to put any data anywhere they want and create schematic modifications as a function of transaction processing.
That is what Excel is for.
If you must have customizable data, add an XML column and load it up with whatever you want.
If it's not practically useful, then it's practically useless.
Rather than EPV it's normally called EAV though it's hated with a vengeance on here on here but you might want to look it up. It can be improved by adding relational integrity etc but any design using EAV will have inherent weaknesses. If you want to see an example system that has those type of features. There was a heated thread that covered all the cons and a few pros of using EAV. Everything that was raised probably applies to any method used to meet your requirement (including XML).