Allow users to create new categories and fields on ASP.NET website

We have a db driven asp.net /sql server website and would like to investigate how we can allow users to create a new database category and fields - is this crazy?. Is there any examples of s开发者_如何学JAVAuch organic websites out there - the fact that I havent seen any maybe suggest i am? Interested in the best approach which would allow some level of control by Admin.

I've implemented things along these lines with a dictionary table, rather than a more traditional table.

The dictionary table might look something like this:

create table tblDictionary
(id      uniqueidentifier, --Surrogate Key (PK)
 itemid  uniqueidentifier, --Think PK in a traditional database
 colmn   uniqueidentifier, --Think "column name" in a traditional database
 value   nvarchar,         --Can hold either string or number
 sortby  integer)          --Sorting columns may or may not be needed. 

So, then, what would have been one row in a traditional table would become multiple rows:

Traditional Way (of course I'm not making up GUIDs):

ID     Type  Make      Model     Year    Color
1      Car   Ford      Festiva   2010    Lime

...would become multiple rows in the dictionary:

ID    ITEMID    COLUMN     VALUE    
0     1         Type       Car
1     1         CarMake    Ford
2     1         CarModel   Festiva
3     1         CarYear    2010
4     1         CarColor   Lime

Your GUI can search for all records where itemid=1 and get all of the columns it needs. Or it can search for all records where itemid in (select itemid from tblDictionary where column='Type' and value='Car' to get all columns for all cars.

In theory, you can put the user-defined types into the same table (Type='Type') as well as the user-defined columns that that Type has (Type='Column', Column='ColumnName'). This is where the sortby column comes into it - to help build the the GUI in the correct order, if you don't want to rely on something else.

A number of times, though, I have felt that storing the user-defined dictionary elements in the dictionary was a bit too much drinking-the-kool-aid. Those can be separate tables because you already know what structure they need at design time. :)

This method will never have the speed or quality of reporting that a traditional table would have. Those generally require the developer to have pre-knowledge of the structures. But if the requirement is flexibility, this can do the job.

Often enough, what starts out as a user-defined area of my sites has had a later project to normalize the data for reporting, etc. But this allows users to get started in a limited way and work out their requirements before engaging the developers.

After all that, I just want to mention a few more options which may or may not work for you:

1. If you have SharePoint, users already have the ability to create their own lists in this way.
2. Excel documents in a shared folder that are saved in such a way to allow multiple simultaneous edits would also serve the purpose.
3. Excel documents, stored on the webserver and accessed via ODBC would also serve as single-table databases like this.