Re: What's the worst that can happen with below database design? Immediately or in the future?
Key-Value (EAV) schema does not scale.
EAV discussions:
bad design pattern -
http://forums.mysql.com/read.php?10,388917 (select top x with a union)
EAV, key value -
http://forums.mysql.com/read.php?24,367895 (How to speed up Query with WHERE AND/OR, with Subqueries using IN)
kv, union -
http://forums.mysql.com/read.php?10,248799 (Opinions on this schema please =))
http://forums.mysql.com/read.php?10,612778 (Query for finding keys (IDs) that only have one of two values associated with it)
JOINs -
http://forums.mysql.com/read.php?52,604044 (Problem with SQL query :()
http://forums.mysql.com/read.php?10,611673 (Query whith self join?)
http://forums.mysql.com/read.php?125,607976 (Dynamically create tables vs key-value which is best?)
http://forums.mysql.com/read.php?10,602688 (Very Large Database with multiple tables question)
http://forums.mysql.com/read.php?10,601624 (EAV table vs one table for group)
http://forums.mysql.com/read.php?24,598686 (Query degrading the performance)
http://forums.mysql.com/read.php?10,583572 (Too many tables; MySQL can only use 61 tables in a join)
http://forums.mysql.com/read.php?144,581393 (Can clustered be applied to my case)
http://forums.mysql.com/read.php?10,578652 (Query not functional)
http://forums.mysql.com/read.php?20,563147 (How to realize hierarchical database design?)
http://forums.mysql.com/read.php?115,561617 (Index for Query)
http://forums.mysql.com/read.php?24,558387 (How to allow incremental versions of the data in a BD)
http://forums.mysql.com/read.php?24,552949 (mysql + xmpp server +test loading - did I reach any physical limit?)
http://forums.mysql.com/read.php?10,516705 (Re: Get value from one table to retrieve data from other table?)
http://forums.mysql.com/read.php?10,498673 (Query Performance, comparing rows from the same table.)
http://forums.mysql.com/read.php?10,494851 (Can you help me with this query?)
http://forums.mysql.com/read.php?125,430969 (Design Conundrum - Data Types)
http://forums.mysql.com/read.php?24,421377 (user settings table - normalize or not?)
http://forums.mysql.com/read.php?115,416609 (Query that allows for missing rows)
http://forums.mysql.com/read.php?115,413629 (Optimize Query with multiple joins)
http://forums.mysql.com/read.php?10,411985 (Is it better to use a JOIN or two separate queries?)
> Attribute keys of each table introduced to each other to allow for an inter-relationship in between tables.
Ouch. That compounds the complexity when generating queries.
> Field types can be spread out in any format appropriate; Maybe two of them longtext, one of them bigint or one of them tinyint one of them varchar.
Ouch. That compounds the complexity when generating queries.
> Naturally there will be numerous empty fields in tables for data models which dont use all of the fields.
That's the least of your problems.
> I dont know how i should distribute the fields though. How many Longtexts in a given table, or how many INTs or any bigint etc. I dont know a statistic to make such an estimation for covering most used field combinations.
Ouch. Someday you will need _another_ INT -- ALTER TABLE will be needed.
> data_type is the unique identifier of the data type. Say, Blog posts. primary_key is the key for the main identity (on top of the tree). Attributes are related to the main identity, and attached to the identity through primary_key.
Maybe we can discuss this after you run far away from the other issues.
Some advice...
1. All access should go through a layer. It looks like you are versed in PHP; good.
2. Think hard about what queries will be needed, and which fields will need to be indexed to make the queries efficient.
3. Throw the rest of the fields into a JSON structure.
4. Think of "Entities" (id + a few indexed fields + plus JSON for open-endedness) and "Relations" (1-many, many-many; extra tables with rarely more than 2 fields).
That will take you a long way, and will be simpler than what you have designed. You will still be plagued with the need to add another index, but at least much of the rest is gone (how many INTs, etc).