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
