It's funny how terminology can be so confusing! When I say "dimension", I mean engineering-wise as in the distance from a to b.
Also, my use of session probably wasn't a good word in this context. Our sessions are actually permanently stored, since they are associated with a particular engineering job. The user may want to recall a past job to review the inputs and/or recreate the design. That said, the temporary table information is useful... I may need that to manage the system once we change the user interface from a single page to a step-by-step approach. Thanks for all of the help! Mike Zelina Brent Baisley wrote: > 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 > > --------------------------------------------------------------------- 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
