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

Background information:
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.



Query requirements:

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).

So ideally a result structure would be:
My attempts/thougts so far:

1)
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


2)
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



3)
What i ended up for now, is a three table structure:

tbl_entities
fields: eid, name

tbl_properties
fields: pid, property, datatype

tbl_values
fields: vid, pid, eid, dbl_value, txt_value, note_of_value, date_of_value

However, however at this point of have absolutly no idia for an query to achieve something that comes close to the desired result.



So what i am after, is an DB design that meets requirement, but stores the data as effezient and normalized as possible :/

Thanks =D

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.

mhm intresting point but i wanted to take advantage of mysql for doing statistics for example :/

however i just learned something huge in mysql.. using subqueries ^^

so.. attempt 3) is allright.. as i can get an result that joins the entity table

thanks! =]


(might be closed as problem is solved

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).