On 19 Jun 2006 13:31:20 -0700, "Travis" <travisp>
Right. Imagine the code to handle that...>I'm creating an application that has data for each user in a basically
>unlimited number of rows and columns. As a result the data itself is a
>bit like a spreadsheet, but it is important that the data can be stored
>in a mysql database for several reasons.
>Designing a database like this runs counter to every way I've done them
>before, and I can't seem to come up with a good design to handle this.
>I suppose I could just create a database table where each row has 10
>columns, and create a second table that links the user to each row
>entry that is necessary. But this seems horrible and very poor design.
I wouldn't worry about space too much nowadays.>Alternately I could just create a Cell table that has two fields:
>cellid, position, data. But this seems like it would take up an
I'd use ([cellid autoincrement if needed], rownr, colnr, data).>Any suggestions about how to handle this?
If you need cellid for SELECT ... WHERE ... IN () purposes:
primary key on cellid, indexes on both rownr, and colnr.
primary key on (rownr,colnr)
This schema would't solve the polymorphism spreadsheets usually
handle: the data in the cells could potentially be of different
types. It depends on the rest of the requirements how to best
c[_] Giving power and money to government is like giving
whiskey and car-keys to teenage boys. (PJ O'Rourke) (#181)