Professional Web Applications Themes

Spreadsheet-like database - MySQL

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 ...

  1. #1

    Default Spreadsheet-like database

    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.

    Alternately I could just create a Cell table that has two fields:
    cellid, position, data. But this seems like it would take up an
    unnecessary space.

    Any suggestions about how to handle this?

    Travis Guest

  2. #2

    Default Re: Spreadsheet-like database

    On 19 Jun 2006 13:31:20 -0700, "Travis" <travisp>
    wrote:
    >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.
    Right. Imagine the code to handle that...
    >Alternately I could just create a Cell table that has two fields:
    >cellid, position, data. But this seems like it would take up an
    >unnecessary space.
    I wouldn't worry about space too much nowadays.
    >Any suggestions about how to handle this?
    I'd use ([cellid autoincrement if needed], rownr, colnr, data).
    If you need cellid for SELECT ... WHERE ... IN () purposes:
    primary key on cellid, indexes on both rownr, and colnr.
    otherwise
    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
    solve that.
    --
    ( Kees
    )
    c[_] Giving power and money to government is like giving
    whiskey and car-keys to teenage boys. (PJ O'Rourke) (#181)
    Kees Nuyt Guest

  3. #3

    Default Re: Spreadsheet-like database

    Travis wrote:
    > Alternately I could just create a Cell table that has two fields:
    > cellid, position, data. But this seems like it would take up an
    > unnecessary space.
    It depends on if the "spreadsheet" is densely populated or sply
    populated. The design above is good if the sheet is sp.

    There's no need for a cellid, according to the requirements you've
    described so far. Use the row, col as a compound primary key instead.

    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Re: Spreadsheet-like database

    Thank you both. I think this should help me out some.

    Travis Guest

Similar Threads

  1. Merge Excel spreadsheet database into Indesign template
    By Janis_Rough@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 0
    Last Post: September 18th, 11:18 PM
  2. Spreadsheet::WriteExcel
    By Raj in forum PERL Modules
    Replies: 3
    Last Post: February 6th, 10:00 AM
  3. EXCEL spreadsheet
    By susan_sicora@adobeforums.com in forum Adobe Indesign Windows
    Replies: 13
    Last Post: November 14th, 02:58 AM
  4. Spreadsheet output
    By comp in forum PERL Modules
    Replies: 3
    Last Post: January 16th, 04:15 PM
  5. asp.net and spreadsheet...
    By Raphaël Désalbres in forum ASP.NET General
    Replies: 1
    Last Post: July 5th, 10:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139