I thought you were coming from some other database. I haven't heard "dimension" since my FoxPro (on the Mac) days. Since session data is temporary, you should use temporary tables to store you session variables. Just use the "real" table to hold you default values. You want to do something like this at the start of a session: create table SessionVars SELECT * from SessionDefaultsTable
Then you just read or update the SessionVars table as needed. MySQL will handle the creation and management of the table and that temp table will be unique to each user, so you don't need to worry about table name conflicts, MySQL handles it. Also, MySQL handles dropping the table when the user "disappears", so you don't have to worry about cleanup. Read up on temporary tables, I think they would help you considerably in this situation. On Thursday, August 1, 2002, at 01:39 PM, Michael Zelina wrote: > 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 > > -- 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
