I meant avoid having the most commom (and accessed) entity attributes as metadata, because it also implies in SQL joins (of elgg_metadata with elgg_entities) and far more records in the result set (one for each metadata).

By default there is no join, but we preload all metadata.

For example a blog listing of 10 blogs

• 1 query to get the 10 blogs from the entities table
• than 1 query to load all metadata for those 10 entities

Beyond that, an attribute as metadata can't be indexed properly nor be used as a constraint for a foreign key.

True, but currently we don't use foreign keys are they conflict with internal events in Elgg.

The metadata approach is undoubtedly flexible, but is far more onerous in terms of database workload and storage area.

If you have some numbers on this, we'd love to hear so maybe we can optimize. So far we've only seen performance gains from the change on a fairly large database (1.6M entities, 11M metadata) with 100-200 concurrent users.

If you have some numbers on this, we'd love to hear so maybe we can optimize.

Of course there are some kind of queries that will benefit from the full metadata model (like the ones in your example), but there are several others which will not.

For example: in the data structure below, queries would be more expensive if executed over the metadata table than over an specific group entity table.

A group entity about music bands with the following attributes:
group_name
description
gender_id
location_id

where, gender_id is the foreign key from the music gender table
location_id is the foreign key from the location table.

To sum up,  there are advantages and drawbacks of both data models, but we, as developers, would love if we had back the choice of choosing over both approaches on Elgg 6 and above.

For example: in the data structure below, queries would be more expensive if executed over the metadata table than over an specific group entity table.

A group entity about music bands with the following attributes:
group_name
description
gender_id
location_id

where, gender_id is the foreign key from the music gender table
location_id is the foreign key from the location table.

The groups_entity table only ever had the columns: guid, name and description. If you added gender_id and location_id (and the corresponding tables) that means you already modified the datamodel.

To sum up,  there are advantages and drawbacks of both data models, but we, as developers, would love if we had back the choice of choosing over both approaches on Elgg 6 and above.

In my 15 years of experience with Elgg I never had to modify the datamodel to suit a need I (or my customers) had.