I'm working with a client who has a piece of custom website software
that has something I haven't seen before. It has a MySQL Database
backend, but most of the tables are auto-generated by the php code.
This allows end-users to create tables and fields as they see fit. So
it's a database within a database, but obviously without all the
features available in the 'outermost' database. There are a couple
tables that are basically mappings of auto-generated table names and
fields to user-friendly table names and fields*. (This makes queries
feel very unintuitive :P )

They are looking for some additional features, ones that are
immediately available when you use the database directly, such as data
type enforcement, foreign keys, unique indexes, etc. But since this a
database within a database, all those features have to be added into
the php code that runs the database. The first thing that came to my
mind is Inner Platform Effect -- but I don't see a way to get out of
database emulation in code and still provide them with the features
they need!

I'm wondering, could I create a system that gives users nerfed ability
to create 'real' tables, thus gaining all the relational features for
free? In the past, it's always been the developer/admin who made the
tables, and then the users did CRUD operations through the
application. I just have an uncomfortable feeling about giving users
access to schema operations, even when it is through the application.
I'm in uncharted territory, and I'm looking for maps, or advice/
stories from experienced sailors.

Is there a name for this kind of system? Internally, in the code, this
is called a 'collection' system. The name of 'virtual' tables and
fields within the database is called a 'taxonomy'. Is this similiar to
CCK or the taxonomy modules in Drupal? I'm looking for models of
software that do this kind of this, so I can see what the pitfalls and
benefits are. Basically I'm looking for more outside information about
this kind of system.

* Note this is not a simple key-value mapping, as the wikipedia
article on inner-platform effect references. These work like actual
tuples of multiple cells -- like simple database tables.

On Jan 14, 12:47*pm, Steven Lefevre <steven.m.lefe... (AT) gmail (DOT) com>
wrote:

I have worked on one of these 12+ years ago and was a home-grown
app. Essentially the row data is a pivot of the actual table where
the column name was a column in a row, but other columns were the data
associated with that table. Doing selects against it to get the actual
data was interesting to say the least. Unfortunately (or maybe
fortunately) I have erased much of my knowledge base (or just plan
forgotten) on that system. It was one method of being able to generate
a lot of smaller databases dynamically. The drawback was always
performance and was generally dependent upon the volume of data.

example - not exact, but enough for you to get the idea
col1 col2 col3 col4 col5
tablea col1name datatype rownum1 tablea.col1.data
tablea col2name datatype rownum1 tablea.col2.data
tablea col3name datatype rownum1 tablea.col3.data
tablea col4name datatype rownum1 tablea.col4.data
tableb col1name datatype rownum1 tablea.col1.data
tableb col2name datatype rownum1 tablea.col2.data
tableb col3name datatype rownum1 tablea.col3.data

There are probably a hundred easier ways to achieve the end-goal of
user-designed/defined schemas and let the database do what the
database does best. Trying to write your own database system is a
losing proposition as you may not be the one to maintain it forever --
well maybe you are, but still makes upgrades very difficult.

If you need users to create their own schemas, that can be done
through an ADMIN sort-of app where you can control things like storage
placement on the system (see the docs for placing tables and/or
indices on different/multiple storage devices).

Bottom line, it would seem to me that you will put in far more time
trying to maintain this sort of system with little to no return on
your time investment.