Create Custom Database Table

[devino]

Hi,
I have embarked on a online reporting system.
The only thing I am having a problem with is allowing signed in users to create reports (database tables).

I would like to have it setup so that a user clicks a "create new report" button that takes them to a form that allows them to do the following:
1. Name Database Table
2. State how many columns the table will have (Add/Remove Colum Buttons)
3. If the user chooses to have say, 5 columns for the table, then this form will reflect 5 input text fields that the user fills out to name each Colum.
4. Hit Save and creates database table that reflect the filled out information.

So again, user gets taken to a form that prompts them to create a custom database table.

User specifies the database table name in a input text field

Then the user can specify how many columns the database table will have and the form reflects this number of columns by displaying the specified amount in input text fields that will actually name the tables columns.

Any ideas?

[rulian]

You want to do quite the opposite of having them create their own database.

Rather, normalize your tables and design your relationships in such a way to allow users to have a dynamic system of rows.

So rather then creating a table for each user, your master table creates a single row for the user's preferences.
Using those preferences you create the corresponding rows in another table, and so on and so on.

So your database structure doenst change, but your users will experience a dynamic and robust system.

So the basic rule here is, create rows not tables.
It just requires thought, trial and error and testing on your part.

[YAOMK]

I'd first gather all the data into an array, then I'd built the query to create/insert the user defined table, I'd also create an additional table to keep track of all the user created tables. More importantly I'd not endorse this practice as users most likely wont know anything about relational databases and how to conform their tables to the rules of database normalization.

I agree, rulian's idea seems more robust.

[devino]

I am just a little confused.

I just dont see how a master table can have predefined preferences that dictate how a users datatable will function (colum names and all).

Might their be an example or something related to rulian's method I could study?

[rulian]

Well, lets take the example of having to create a "website builder"
So people can create a website and each person can have a different number of pages. So lets say a user wants to create a website with 7 pages.
If you were doing this in the structure of the database itself to have 7 pages, your table might look like this:

User Table Name | Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7|

This presents two basic problems, 1, to add a page you have to change the sturcture of the database which can break your SQL statements among other things. and 2, your table will only have 1 row which defeats having a database.

So instead, break the User Table, and Table contents into different tables.
Example:

User's and Tables
Table ID [Unique to each row ] | User ID [ Assigns ownership ] | Title

okay so now I have a table that creates "Tables" for each user. So now creating a table simply add's a row to the "User's and TAbles' table rather then an additional item in the database itself.

So now I want to Add pages to my table, so I have a master "Pages" Table

Page ID [ Unique to reach row] | Table Id [Relates page to specific table ] | Page Title | Page Content

So now adding pages to a table really means I'm just adding rows to the "Pages" table, so I can have as many as I want, add or delete pages and it wont effect my database.