Storing dynamic data models in a database

I’m not quite sure if that subject is on the money or not but here’s
my question.

If you want to give the users of your app the ability to dynamically
create tables… how do you effectively store that in a database?
Sounds simple right? Maybe I can try to clarify a little better.

I’m going to try to give a very application inspecific example here.
Please note - this is not for a “spreadsheet application”.

A user opens your application and chooses “create table”. They are
then given the option to add and remove columns from that table. Each
column will allow them to select a set of restrains for that column.
Say, they add Column A and its data can only be numbers between 1 and
10. Then they add Column B and it can only be True or False.

If many users are simultaneously creating tables and columns for these
fields, I imagine the schema would have to be something along the
lines of…

id - id of the table
desc - description of the table.

id - id of the column
const - regex containing the constraint of its data
name - string name for display purposes.

tables_id - id of the table
columns_id - id of the column

But my real question comes in as - how do you ‘effectively’ implement
‘rows’ for your data?

Do you create another tuple called tables_rows with an id for the
table and an id for the rows? How far do you have to take this to have
a dynamic, yet normalized data model?

Any sort of help on this subject will be greatly appreciated. I’m
implementing this in rails as part of a larger project which is why
I’m posting in this forum instead of a mysql forum. People here may
have a few tricks up their sleeve as far as data modeling for pleasant
rails implementations.


  • FJM