The way you store session values, as you said, can be troublesome when new dimensions are added, or existing ones removed. Also it might be constrained by some column limit. I'd try something like this,
+--------------+--------+-------------+-------+ | SESSIONID | UserID | DimensionID | Value | +--------------+--------+-------------+-------+ | session_abcd | 100 | 1 | 1 | | session_abcd | 100 | 2 | 1 | | large_design | 200 | 1 | 7.25 | | small_design | 100 | 1 | 8.0 | | ... | ... | ... | ... | +--------------+--------+-------------+-------+ This might help you expand/shrink dimensions a little easier. One obvious drawback with this approach is that we are fixing all dimension values to a single type, say double. In order for this to work, you'll need to define IDs for each possible character values, which might or might not work for your situation. Hope it helps. James -----Original Message----- From: Michael Zelina [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 01, 2002 10:40 AM To: Brent Baisley Cc: [EMAIL PROTECTED] Subject: Re: maximum number of columns & max row size Hi Brent, Thanks for the response. Currently, I have the variables listed out in a table with one variable per row... something similar to the following: +----+------------------+- | ID | DimensionName | +----+------------------+- | 1 | units_length | | 2 | units_temp | | 3 | dim_positionX | | 4 | dim_width | | 5 | dim_length | +----+------------------+- So, I can easily add dimensions or remove them as needed. The problem I have concern about is storing the dimension values a user inputs for a given "session". For this I now have a table defined as follows that maps to the ID of the variable list table: +--------------+------+------+------+------+ | SESSIONID | ID1 | ID2 | ID3 | ... | +--------------+------+------+------+------+ | session_abcd | in | F | 5.5 | .... | | tesfile1 | in | F | 6.0 | .... | | large_design | in | F | 7.25 | .... | | small_design | in | F | 8.0 | .... | +--------------+------+------+------+------+ 4 rows in set (0.04 sec) This way, I can have the values stored as a single row and easily delete and add session file values from the database. Using the ID value as the column provides some abstraction so I can rename dimensions and the sessions are still valid. Any thoughts on how better to store the session data? Since I am coming over from Access, I currently have three tables with 255 columns each. Performance-wise it seems OK... and I'm wondering if I should consolidate the tables into one or leave them separated. Or if even with a 255 column table I might run into performance problems. Thanks again for the help, Mike Brent Baisley wrote: > A rule of thumb I always follow is to design the DB so that you don't > have to add columns (or modify the structure) unless something unforseen > comes up (like a new feature). In your case, you are expecting to add > "variables" in the future, so I would deign accordingly. > Use rows instead of columns. Using rows also allows you to index all > variable values and names, since there are only two columns. You > couldn't index all your columns if you expanded horizontally instead of > vertically. Besides, it's a heck of a lot easier to add a row than a > column. > > > On Thursday, August 1, 2002, at 01:09 AM, Michael Zelina wrote: > >> Can someone provide insight into what a good maximum number of >> columns is in a MySQL database? I have an extensive list of variables >> for an engineering application which number in the 700+ range with >> more additions possible down the road. My question is: should I break >> up the variables into several tables for storage, or can I use a >> single table with this many columns. I cannot find any good rules of >> thumb for how much the number of columns effects performance. >> >> In trying to consolidate my existing data from Access (which limits >> columns to 255 >> per table) into a single table, I received the following error: >> >> mysql> create table tblsessions select * from tblsessionfiles, >> tblsessionfiles2, >> tblsessionfiles3 WHERE tblsessionfiles2.sessionid_2 = >> tblsessionfiles.id38 >> AND tblsessionfiles3.sessionid_3 = tblsessionfiles.id38; >> ERROR 1118: Too big row size. The maximum row size, not counting >> BLOBs, is 65535. You have to change some fields to BLOB >> >> I assumed that the maximum row size is the total bytes for the entire >> row. My >> questions are: (1) can I change this without recompling MySQL and (2) >> should >> I really do this or just stick with three or more separate tables for >> performance >> reasons. >> >> FYI, I am running this on a well-equipped server and will probably >> only have a >> maximum # of concurrent users doing DELETE and INSERT in the range of >> 150 or so. >> >> Thanks, >> Mike >> >> --------------------------------------------------------------------- >> Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail <mysql-unsubscribe- >> [EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> >> > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
