How to handle dynamically columned tables in rails

Rails
1

I want to build an application that lets me (the end user accessing a
web page) design a simple list with columns and add records. E.g., if
the end user says I want a table that has a list of songs, he can create
a list (title, date, artist, label) and then make another list of, e.g.,
books on his bookshelf with the necessary columns (title, author,
pub_date, shelf).

The end user shouldn’t have to do any database programming, schema
designs, etc. Just select the columns and types (from a very limited
list – date, text, longtext).

Of course in PHP I could just have the program create tables and keep
another table of the lists that has each column in a particular list in
a row (list_num, name_of_list, column_name, column_type).

Another way to do it that doesn’t result in the end user having to
create new tables is to have a table that has core columns for ALL lists
(id, title, description) and then join that table to columns by type of
data on the fly.

So, select * from core_list_items where list_num=X and then join it with
columns in the Date_items table and the text_items table and then the
date_items table. I have to do multiple join statements, sometimes from
the same table.

ID TITLE DESCRIPTION SONG_NAME (from text table) PUB_DATE (from date
table) RECORD_LABEL (also from text table).

I have made this work in PHP, but is there a way to do this in rails? I
posted this under a different topic, and I’ve seen suggestions that
require migrations and other back-end work to make this happen. But an
end-user won’t be able to do that, so i want to not have to do any
back-end work.

2

Hey Todd,

I’ve been working on something which i’ve briefly discussed here:
http://stephenbartholomew.wordpress.com/.

This is based on having field definition/values tables with forms to add
new fields. I’ve written a mixin that automatically maps these fields to
a model.

Have a look and let me know if this is something that you’d be
interested in. As i say in the article, it’s very much a work in
progress and is kinda an investigation of the idea, but hopefully it’ll
grow into something quite useful.

Cheers,

Steve

3

On 7/8/06, Todd [email protected] wrote:

columns in the Date_items table and the text_items table and then the
date_items table. I have to do multiple join statements, sometimes from
the same table.

Depending on how many possible columns there might be it seems like
you might be able to use Single Table Inheritance or a serialized
column object. STI would work if the columns will largely be the same.
To be truly dynamic you could have a table with columns: id, user_id,
column_name, serialized_column_data which would be a hash/array of all
the information in their column. Wouldn’t be pretty or particularly
space effective but it could work if you really need dynamism. Either
way though, I’d much rather trade space concerns for speed since joins
can be some of the slowest operations in complex queries.

Maybe an even better way would be to have: id, user_id, column_name,
column_data (which only contains one entry instead of a hash) and just
query for all rows where column_name = ‘monkey’ when loading the
monkey column for user_id 16. This would allow a multi-column index on
user_id, column_name, column_data which would speed things up so much
it would be incredible.

From my experience, joins are pretty evil, creating tables is pretty
awful too. I would try to have as flat a structure as possible as long
as you aren’t expecting millions of users in which case I have no idea
what to do. I’ve dealt with 6mil entries, but over that poses some
problems that I just cannot fathom yet.

Regardless, to answer your actual question, you can certainly do what
you used to do in PHP in Rails. You can use an optimistic include when
you use find() or just use find_by_sql() and manually create your
array with whatever joins you need.

Hope that helps, if nothing it was entertaining to think about.
Cheers,
Chuck V.

4

Stephen B. wrote:

Hey Todd,

I’ve been working on something which i’ve briefly discussed here:
http://stephenbartholomew.wordpress.com/.

This is based on having field definition/values tables with forms to add
new fields. I’ve written a mixin that automatically maps these fields to
a model.

Have a look and let me know if this is something that you’d be
interested in. As i say in the article, it’s very much a work in
progress and is kinda an investigation of the idea, but hopefully it’ll
grow into something quite useful.

Cheers,

Steve

This looks interesting, though I can’t tell if it can let the end user
add fields or if they can be added so that they apply only to some and
not other items in a table. I’d be interested in hearing more.

5

On 7/8/06, Todd [email protected] wrote:

Maybe an even better way would be to have: id, user_id, column_name,
problems that I just cannot fathom yet.
simply be supressed.

Problem is it requires me to rewrite STI somewhat and the user has a
fixed number of potentially addable fields. Two dates, three texts.

There must be a way to do this efficiently.

True, STI most likely isn’t the answer, however the other two
suggestions seem like they would work nicely.

Ta,
Chuck V.

6

Problem is it requires me to rewrite STI somewhat and the user has a
fixed number of potentially addable fields. Two dates, three texts.

There must be a way to do this efficiently.

you might want to check out josh susser’s blog, and the wiki pages on
polymorphic ‘linking’ associations… im using a table called links,
where the type is either another node, or a primitive (textfield,
numfield, date, etc)… the schema is like

table node

id name

table node_links

id node_id link_id link_type

table textfields

id data

just one of many solutions (still thinking about switching to activeRDF)

7

Chuck V. wrote:

[Single Table Inheritance] would work if the columns will largely be the same.
To be truly dynamic you could have a table with columns: id, user_id,
column_name, serialized_column_data which would be a hash/array of all
the information in their column. Wouldn’t be pretty or particularly
space effective but it could work if you really need dynamism. Either
way though, I’d much rather trade space concerns for speed since joins
can be some of the slowest operations in complex queries.

Maybe an even better way would be to have: id, user_id, column_name,
column_data (which only contains one entry instead of a hash) and just
query for all rows where column_name = ‘monkey’ when loading the
monkey column for user_id 16. This would allow a multi-column index on
user_id, column_name, column_data which would speed things up so much
it would be incredible.

From my experience, joins are pretty evil, creating tables is pretty
awful too. I would try to have as flat a structure as possible as long
as you aren’t expecting millions of users in which case I have no idea
what to do. I’ve dealt with 6mil entries, but over that poses some
problems that I just cannot fathom yet.

To save real time, I could just have one table with a few placeholder
columns…

ID NAME DESCRIPTION DATE1 DATE2 TEXT1 TEXT2 TEXT3

Then the STI would have to be tweaked so that any subset of that table
corresponding to a defined group would relabel the diplay on say TEXT1
to be “Record label” as defined in a database record describing the
different subsets. For those that don’t use some of the info, it would
simply be supressed.

Problem is it requires me to rewrite STI somewhat and the user has a
fixed number of potentially addable fields. Two dates, three texts.

There must be a way to do this efficiently.

8

This looks interesting, though I can’t tell if it can let the end user
add fields or if they can be added so that they apply only to some and
not other items in a table. I’d be interested in hearing more.
The idea is that there will be one field definition and one definition
values table. The definition table will state which model the
definition applies to.

The fields will be added using a syntax something like:

Book.add_dynamic_field(‘Date Published’,‘text’)

You would just include somewhere in the system for clients to enter that
info. I have a separate section in the client admin to add new fields.
The form just has a ‘field’ field to enter the name and a select box
with types: Line of text, list of values (select one), list of values
(select multiple), date - that kind of thing.

The entry in the definitions table would then look like this:

id | name | field_type | field_text |applies_to |

1 date_published text Date Published Book

This is extracted from a client project that only has one dynamic record

  • so some of this functionality is not yet present. However, a lot of
    the core features are working so i’ve been scouting to see if there is
    interest enough to develop it further.

If only i could work on this stuff without the pressure of client
deadlines :0)

Steve

9

Stephen B. wrote:

This looks interesting, though I can’t tell if it can let the end user
add fields or if they can be added so that they apply only to some and
not other items in a table. I’d be interested in hearing more.
The idea is that there will be one field definition and one definition
values table. The definition table will state which model the
definition applies to.

The fields will be added using a syntax something like:

Book.add_dynamic_field(‘Date Published’,‘text’)

You would just include somewhere in the system for clients to enter that
info. I have a separate section in the client admin to add new fields.
The form just has a ‘field’ field to enter the name and a select box
with types: Line of text, list of values (select one), list of values
(select multiple), date - that kind of thing.

The entry in the definitions table would then look like this:

id | name | field_type | field_text |applies_to |

1 date_published text Date Published Book

This is extracted from a client project that only has one dynamic record

  • so some of this functionality is not yet present. However, a lot of
    the core features are working so i’ve been scouting to see if there is
    interest enough to develop it further.

If only i could work on this stuff without the pressure of client
deadlines :0)

Steve

You should keep working on it, and we should swap notes. Sounds like
we’re working on the same thing. The difficult thing for me is getting
an efficient database architecture and select statements. My select
statements for these monsters are ugly. I use a series of left joins
(one for each dynamically added field) and then a filter/where clause to
knock out the extra records. I know there’s a better way to write it.
Here’s an example – this is probably as clear as mud:

SELECT id, title, date, it1.it AS who, it2.it AS what
FROM list
LEFT JOIN dates ON ( id = dates.list_id )
AND (
dates.cat_id =1
)
LEFT JOIN who AS it1 ON ( id = it1.list_id )
AND (
it1.cat_id =1
)
LEFT JOIN who AS it2 ON ( id = it2.list_id )
AND (
it2.cat_id =2
)
WHERE (
dates.list_id = id
AND dates.cat_id =1
)
OR (
it1.list_id = id
AND it1.cat_id =1
)
OR (
it2.list_id = id
AND it2.cat_id =2
)
LIMIT 0 , 30

10

My select statements for these monsters are ugly.
I haven’t actually had to write complex searches for this particular
project, but i know the client is going to need reporting soon so i’ll
be tackling it then.

I made a similar system in PHP a while ago and faced the same issue.
What i did in the end was create something that required 2 selects but
avoided all the joins. Say we have a Book model with isbn and author as
dynamic fields and we want to search all values. First, i search the
records dynamic field values:

select book_id from book_field_values where (value = ‘1234’) group by
book_id

This gives me a list of the the books that contain ‘1234’.

I can then select all the books’ main data using this list:

select * from books where id in(1,2,3,4,5,6)

I’ll let you know how i get on with these reports. This is probably the
approach i’m going to try first.

Steve