users creating custom lists

[atomicrabbit]

Ok so I want users of a web app to be able to create lists of whatever with custom column headings. So they would make a list like

Name Colour Number Year

 

where the above four words are column headings for a custom list they make. The number of headings should be variable. And the user would be able to add rows to the columns they just made.

 

How would I structure something like that in a database? If you have any questions just ask. I tried to explain it as best as possible.

[fenway]

Well, if you're happy placing some reasonable restrictions, then it's easy -- thought not efficient.

 

Just define a table with a slew of different data types, and then store the heading elsewhere.

[atomicrabbit]

Just define a table with a slew of different data types, and then store the heading elsewhere.

what do you mean, can you elaborate?

[fenway]

Just define a table with a slew of different data types, and then store the heading elsewhere.

what do you mean, can you elaborate?

No need for a PM.  I meant just a generic EAV table.

[atomicrabbit]

Sorry for the PM, got a little anxious.

 

So you're saying just store the data types in one table, and make another table that contains the column headers?

 

Then what? How would you suggest each user's columns be stored? This is what I'm asking. I would like each user to be able to create multiple lists that only they have access to.

 

This is what I was originally thinking before coming here, I just wasn't sure if this is the most efficient way to do it.

 

Tables:

• lists: id, user_id, name
  
• users: id, name, etc
  
• column_types: id, type
  
• list_headers: id, list_id, column_type_id, name
  
• list_contents: id, list_header_id, content
  

 

So then when I user goes to create a list

1) the list gets added to the lists table

2) then they make the column headings using the options available from the column_types table. Those headings get inputted to list_headers as separate rows identified by the list_id

3) They they enter the contents of the list which get inputted as separate rows to list_contents, identified by list_header_id.

 

So for the sake of examples, if the user had a table like this:

 

People
=========================================
Name    Colour     Number    Birthday
------------------------------------------------
Bob     Blue       2         1909/12/12
Frank   Red        16        1970/10/20
Jane    Pink       7         1962/1/29

 

The tables would look like this

 

=======================================
lists
id    user_id     name
---------------------------------------
1     1           People
---------------------------------------

=======================================
users
id    name
---------------------------------------
1     Someone
---------------------------------------

=======================================
column_types
id    type
---------------------------------------
1     string
2     number
3     date
---------------------------------------

=======================================
list_headers
id    list_id   column_type_id   name
---------------------------------------
1     1         1                Name
2     1         1                Colour
3     1         2                Number
4     1         3                Birthday
---------------------------------------

=======================================
list_contents
id    list_header_id   contents
---------------------------------------
1     1                Bob
2     2                Blue
3     3                2
4     4                1909/12/12
5     1                Franke
6     2                Red
7     3                16
8     4                1970/10/20
9     1                Jane
10    2                Pink
11    3                7
12    4                1962/1/29
---------------------------------------

 

Is this the best way of doing it?

[fenway]

It really depends what you're trying to do with this.

 

If you're never going to query individual values, just delimit the values somehow, and store it in one giant TEXT field.

 

Otherwise, yes.

[atomicrabbit]

Yea, I was thinking about throwing each entire row of info into 1 entry with delimiters, but, I want to be able to order the lists by any of the custom columns, so I don't know if that would work. The problem with separate rows for each column of each row is the table will get exponentially large, depending on the number of custom lists made.

 

I also want to be able to order the list by a custom order. The structure of the database is extremely important for the functionality I want to add. That's why I want to put enough thought into it before even thinking about starting coding.

 

Here is the functionlaity I want to add:

- create custom lists with custom headings & datatypes

- order the rows by column or custom order

- be able to re-order the column headings

 

Maybe I could potentially store each row with delimiters and programatically order them (in php) depending on the users preference.

 

If I were to do that, and keeping the above points in mind, maybe I could do something like this for the list contents:

=======================================
list_contents
id    contents
---------------------------------------
1     1=Bob|2=Blue|3=2|4=1909/12/12
2     1=Franke|2=Red|3=16|4=1970/10/20
3     1=Jane|2=Pink|3=7|4=1962/1/29
---------------------------------------

where x= is the id of the column heading. I would just have to trap for "=" and "|" in my code.

 

What do you think?

[atomicrabbit]

Now that I read over my last post, I basically want to make a spreadsheet-like interface but definitely UNLIKE excel in terms of layout/style

 

[fenway]

Again, the only DB concern is speed -- and that only matters if you're trying to extract a single piece of information from this aggregate field.

 

If you aren't, then storing it all in one big field isn't a bad idea.  And it will be the same "size" regardless of how you store it -- in fact, probably larger if you normalize it unnecessarily.

[atomicrabbit]

I'm trying to think how I would be able to programatically order the results if each row is one entry, delimited. I could split them by the delimiter into an array and maybe make a multidimensional array, but still, ordering it may be complicated in php.

[fenway]

I'm trying to think how I would be able to programatically order the results if each row is one entry, delimited. I could split them by the delimiter into an array and maybe make a multidimensional array, but still, ordering it may be complicated in php.

Shouldn't be -- a few split() and you should be on your way.

[atomicrabbit]

I just found a user-made function in the comments of this page: http://php.net/manual/en/function.sort.php

 

It sorts multidimensional arrays by specified keys. This might help me.

 

 

[atomicrabbit]

Again, the only DB concern is speed -- and that only matters if you're trying to extract a single piece of information from this aggregate field.

 

If you aren't, then storing it all in one big field isn't a bad idea.

 

Is this the best solution even if there will be a large amount of traffic and users?

[fenway]

The only consideration is that the TEXT field may cause performance issues, depending on what else you put into that table, so you might have to break it out.  But if you're not querying its contents, why not have it all together? Alternatively, you can store it on disk, but that seems like overkill.

[atomicrabbit]

The thing I worry about is I will probably want to have a blob-type datatype, so if I string and entire row into one value, it may get quite big.

 

Assuming I would like to query individual values, how would I structure the db??

[fenway]

Don't worry about the size -- your users aren't uploading gigs of custom lists.

 

If you want to query individual values, you'll have to put each into it's own field -- which will be really ugly, and much, much slower.