Geek Answers Handbook

What is the preferred way to store dynamic lists in a database?

In our application, the user can create different lists (for example, sharepoint), for example, the user can create a list of cars (name, model, brand) and a list of students (name, ext, address, nationality), etc.

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

Should I create a new table at runtime for each newly created list? If this was the best solution, maybe Microsoft SharePoint would do it, I suppose?

Should I use the following scheme

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

Although a single row will create as many rows in the list data table as there are columns in the list, it just doesn't seem right.

? ?

+5
5

, , CREATE TABLE /?

, db , , - ?

  • , . .
  • (screensize, CpuSpeed, AmountRAM, NumberOfCores)
  • , , .
  • CREATE, .
  • , .

, : . . , , , , ..

, FUD. . , , ( ).

+1

, , EAV ( -).

3 1000 :

1 3 ListColumns 3000 ListData​​p >

. " ", , "" SQL- . , CREATE/DROP/ALTER Tables !

EAV , .

:

, ListRows, , ListData !

+3

, - - , , :

  • lists,
  • columns, ( )
  • values,
  • rows, , ,

, (VARCAHR) :

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

, , :

SELECT * FROM [Cars]


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, :

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

SQL-, , , followng:

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'

, ...

- , , .

-, ( " ?" ), .

, CREATE TABLE. , - .

+2

, . , .

, , , , lists, list_columns, list_rows list_data.

, rows columns, . /, , : , . / /, ( ) , list_cells , - , .

:

create table lists (
  id int primary key,
  name varchar(25) not null
);

create table list_cells (
  id int primary key,
  list_id int not null references lists(id)
    on delete cascade on update cascade,
  row int not null,
  col int not null,
  data varchar(25) not null
);
+1

, Sharepoint .

Think about how to integrate your application with Sharepoint and be its data warehouse. You don’t need to recreate everything you like about Sharepoint when you can use it.

This will require a bit of customization, but you can call the SP web services for CRUD for your list data.

Sharepoint 2010 can also list through OData, which could just be used from any application.

0
source

More articles:


All Articles