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
