Rails - Jul 2009 - How to structure a database table with arbitrary labels for the columns?

I am starting to think about writing an RoR app to store some 2-D
data.  Most of the data are textual, although there could be a column
of dates or a column of numbers here and there.  The issue is:
different data sets have different columns.

Currently, the data are stored in spreadsheets.  Many of the sheets
share a common set of columns, but there are some differences (and
more differences could be added later -- after all, they are just
spreadsheets, and I can change the heading at the top of a column and
start filling in data for that column at will.)

Is this a common problem?  If so, how have folks solved it?

One solution might be to say, "You are an agile RoR developer (ok,
it''s a bit of a stretch, but for the purpose of this argument,
let''s
pretend it''s true), adding a column to a database is just as easy as
adding a column to a spreadsheet.  Design the application with the
columns you know about today.  Add more columns tomorrow when you
discover/decide you need them."

Another solution might be to create a table of headings for each
column and create a table with just text values associated with each
type of heading (and associated with the other table(s) to which the
data belongs).  Do the same thing for the numeric data and the date
data.  Design the application so it''s easy to add columns via the UI.

Another solution might be to do something completely different.  Any
ideas/suggestions?

--wpd

Hi Patrick,

On Thu, 2009-07-30 at 13:24 -0400, Patrick Doyle wrote:
> I am starting to think about writing an RoR app to store some 2-D
> data.  
>From your explanation / question below, I have to ask and do not mean to
put you off but, are you an experienced software developer?  If not,
I''d
strongly recommend you start slow.  While I haven''t read it personally,
I''ve heard lots of recommendations for Chris Pine''s
"Learn to Program"
which teaches basic programming concepts using Ruby.  As a second
course, you might try "Why''s (poignant) Guide to Ruby".  

After that, yes, the problem you discuss is very common.  When you''re
ready, go with your first proposed solution.

Best regards,
Bill

Adding columns via code isn''t hard. You''ll have to double
check that
the new columns are picked up in your objects without having to
restart the server when operating in production mode.

But the real question is, is there enough commonality between data
sets that whatever you are trying to do is well solved using a
database? The flexibility to just have data and not have it
constrained by a schema is what spreadsheets are good at.

On Thu, Jul 30, 2009 at 3:47 PM,
cnk<cynthia.kiser-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> database? The flexibility to just have data and not have it
> constrained by a schema is what spreadsheets are good at.
Ohhh... Great answer!

I suspect I''ll just gather the data into the table that I know exists
now.  I was asking because (as is typical of me), I was dreaming about
how I might bite off more than I could chew -- wanting to allow the
end user to create arbitrary data sets displayed in tabular form, with
arbitrary names and types for the different columns.

But, as I''ve thought about it more, it has occurred to me that I will
probably get 99.9% of the way where I want to be just by defining the
table I need, and creating the relationships necessary to extract and
edit specific data sets within that table.

But thanks for the great answer.

--wpd