I am using a client that has a bit of custom website software which has something I've not seen before. It features a MySQL Database after sales, but the majority of the tables are auto-produced through the php code. This enables finish-customers to produce tables and fields because they think fit. Therefore it is a database inside a database, but clearly without full functionalities obtainable in the 'outermost' database. You will find a few tables which are essentially mappings of auto-produced table names and fields to user-friendly table names and fields.* This will make queries feel totally unintuitive :P
They're searching for some capabilities, ones which are immediately available if you use the database directly, for example data type enforcement, foreign secrets, unique indexes, etc. Consider mtss is a database inside a database, all individuals features need to be added in to the php code that runs the database. The very first factor that found my thoughts is Inner Platform Effect* -- however i aren't seeing a means to get away from database emulation but still give them the characteristics they require!
I am wondering, could I produce a system that provides customers nerfed capability to create 'real' tables, thus attaining all of the relational features free of charge? Previously, it certainly is been the developer/admin who made the tables, and so the customers did CRUD procedures with the application. I simply come with an uncomfortable feeling about giving customers use of schema procedures, even when it's with the application. I am in uncharted territory.
It is possible to reputation for this type of system? Internally, within the code, this really is known as a 'collection' system. The title of 'virtual' tables and fields inside the database is known as a 'taxonomy'. Is similiar to CCK or even the taxonomy modules in Drupal? I am searching for types of software which do this type of this, in order to see exactly what the issues and benefits are. Essentially I am searching for more outdoors details about this type of system.
- Note this isn't an easy key-value mapping, because the wikipedia article on inner-platform effect references. These work like actual tuples of multiple cells -- like simple database tables.
I have carried this out, you may make it really quite simple or go completely nuts by using it. You need to do encounter problems though when putting it into customers' hands, shall we be likely to request them to determine primary secrets, unique constraints and foreign secrets?
So presuming you need to proceed knowing that, you'll need some form of data dictionary, also known as meta-data repository. You've got a start, but you have to add the minds that posts are collected into tables, then specify primary and foreign secrets.
Next, producing DDL is rather trivial. Loop through tables, loop through posts, develop a CREATE TABLE command. The only real hitch is you have to sequence the tables to ensure that parents are produced before children. That's easy, implement a http://en.wikipedia.org/wiki/Topological_ordering
In the second level, you initially need to examine the present database after which sometimes only problem ALTER TABLE ADD COLUMN... instructions. Therefore it begins to obtain complicated.
Then things continue to obtain more complicated while you consider permitting DEFAULTS, indicating indexes, and so forth. The job is finite, but could considerably bigger of computer appears.
You may decide to consider the amount of this you want to aid, and then suggest something judgment about coding up.
My triangulum project performs this: http://code.google.com/p/triangulum-db/ but it's limited to Alpha 2 and I wouldn't recommend utilizing it inside a Production situation at this time.
You may even take a look at Doctrine, http://www.doctrine-project.org/, they've some kind of text-based dictionary to construct databases from, but I am unsure what lengths they have gone by using it.