What is the preferred way of saving dynamic lists in database?

Question

In our application user can create different lists (like sharepoint) for example a user can create a list of cars (name, model, brand) and a list of students (name, dob, address, nationality), e.t.c.

Our application should be able to query on different columns of the list so we can't just serialize each row and save it in one row.

Should I create a new table at runtime for each newly created list? If this was the best solution then probably Microsoft SharePoint would have done it as well I suppose?

Should I use the following schema

Lists (Id, Name)
ListColumns (Id, ListId, Name)
ListRows (Id, ListId)
ListData(RowId, ColumnId, Value)

Though a single row will create as many rows in list data table as there are columns in the list, this just doesn't feel right.

Have you dealt with this situation? How did you handle it in database?

Answer 1

Perhaps a potential solution would be the creating of lists can involve CREATE TABLE statements for those entities/lists?

It sounds like the db structure or schema can change at runtime, or at the user's command, so perhaps something like this might help?

• User wants to create a new list of an entity never seen before. Call it Computer.
• User defines the attributes (screensize, CpuSpeed, AmountRAM, NumberOfCores)
• System allows user to create in the UI
• system generally lets them all be strings, unless can tell when all supplied values are indeed dates or numbers.
• build the CREATE scripts, execute them against the DB.
• insert the data that the user defined into that new table.

Properly coded, we're working with the requirements given: let users create new entities. There was no mention of scale here. Of course, this requires all input to be sanitized, queries parameterized, actions logged, etc.

The negative comment below doesn't actually give any good reasons, but creates a bit of FUD. I'd be interested in addressing any concerns with this potential solution. We haven't heard about scale, security, performance, or usage (internal LAN vs. internet).

Answer 2

hi,

what you did is called EAV (Entity-Attribute-Value Model) http://en.wikipedia.org/wiki/Entity-attribute-value_model

For a list with 3 columns and 1000 entries:

1 record in Lists 3 records in ListColumns and 3000 Entries in ListData

This is fine. I'm not a fan of creating tables on-the-fly because it could mess up your database and you would have to "generate" your SQL queries dynamically. I would get a strange feeling when users could CREATE/DROP/ALTER Tables in my database!

Another nice feature of the EAV model is that you could merge two lists easily without droping and altering a table.

Edit:

I think you need another table called ListRows that tells you which ListData records belong together in a row!

Answer 3

It sounds like you might have Sharepoint already deployed in your environment.

Consider integrating your application with Sharepoint, and have it be your datastore. No need to recreate all the things you like about Sharepoint, when you could leverage it.

It'd take a bit of configuring, but you could call SP web services to CRUD your list data for you.

• inserting list data into Sharepoint via web services
• reading SP lists via web services

Sharepoint 2010 can also expose lists via OData, which would be simple to consume from any application.