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

what you did is called EAV (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 2

Well I've experienced something like this before - I don't want to share the actual table schema so lets do some thought exercises using some of the suggested table structures:

• Lets have a lists table containing a list of all my lists
• Lets also have a columns table containing the metadata (column names)
• Now we need a values table which contains the column values
• We also need a rows table which contains a list of all the rows, otherwise it gets very difficult to work out how many rows there actually are

To keep things simple lets just make everything a string (VARCAHR) and have a go at coming up with some queries:

Counting all the rows in a table

SELECT COUNT(*) FROM [rows]
JOIN [lists]
    ON [rows].list_id = [Lists].id
WHERE [Lists].name = 'Cars'

Hmm, not too bad, compared to:

SELECT * FROM [Cars]

Inserting a row into a table

BEGIN TRANSACTION

DECLARE @row_id INT
DECLARE @list_id INT

SELECT @list_id = id FROM [lists] WHERE name = 'Cars'

INSERT INTO [rows] (list_id) VALUES (@list_id)
SELECT @row_id = @@IDENTITY

DECLARE @column_id INT

-- === Need one of these for each column ===
SELECT @column_id = id FROM [columns]
WHERE name = 'Make'
AND list_id = @list_id

INSERT INTO [values] (column_id, row_id, value)
VALUES (@column_id, @row_id, 'Rover')

-- === Need one of these for each column ===
SELECT @column_id = id FROM [columns]
WHERE name = 'Model'
AND list_id = @list_id

INSERT INTO [values] (column_id, row_id, value)
VALUES (@column_id, @row_id, 'Metro')
COMMIT TRANSACTION

Um, starting to get a little bit hairy compared to:

INSERT INTO [Cars] ([Make], [Model}) VALUES ('Rover', 'Metro')

Simple queries

I'm now getting bored of constructing tediously complex SQL statements so maybe you can have a go at coming up with equivalent queries for the followng statements:

SELECT [Model] FROM [Cars] WHRE [Make] = 'Rover'

SELECT [Cars].[Make], [Cars].[Model], [Owners].[Name] FROM [Cars]
JOIN [Owners] ON [Owners].id = [Cars].owner_id
WHERE [Owners].Age > 50

SELECT [Cars].[Make], [Cars].[Model], [Owners].[Name] FROM [Cars]
JOIN [Owners] ON [Owners].id = [Cars].owner_id
JOIN [Addresses] ON [Addresses].id = [Owners].address_id
WHERE [Addresses].City = 'London'

I hope you are beginning to get the idea...

In short - I've experienced this before and I can assure you that creating a database inside a database in this way is definitely a Bad Thing.

If you need to do anything but the most basic querying on these lists (and literally I mean "Can I have all the items in this list please?"), you should try and find an alternative.

As long as each user pretty much has their own database I'll definitely recommend the CREATE TABLE approach. Even if they don't I'd still recommend that you at least consider it.