table/database design question

Hi,

I have table/db I need to create that has a rectangle structure even
when normalized, but the difficulty is that the rows can have a large
number of column - over the max of what mysql can handle. If I put the
data in vertical format I lose a lot of functionality. If I split the
tables into n number of tables and keep the data on the horizontal it make
for some combersomeness. I'm sure this is a common problem but I have
never need more that 5-20 columns per table.

any input on this is greatly appreciated!

I also am considering writing code that may compress/decompress the
columns into say a blob, but this again leaves me in the cold, with out
the power of sql...


Gary

Hi G,
I don't quite understand the question, the table length is defined by you
just like in C/C++ programming. You can have one cell handling as many
character as you wanted. The text will be wrapping around when displaying
it. Please take a look at my sample to see if this is what you are trying to
do:

http://67.171.3.18/isapi.dll?Query=U...OM+Suppli ers

Main page:
http://67.171.3.18/websql.htm

If my server is not on, then try again later.

Dr. 2-lips



"G" <nospam@nospam.org> wrote in message
newsan.2005.03.31.00.55.12.50577@nospam.org...
: Hi,
:
: I have table/db I need to create that has a rectangle structure even
: when normalized, but the difficulty is that the rows can have a large
: number of column - over the max of what mysql can handle. If I put the
: data in vertical format I lose a lot of functionality. If I split the
: tables into n number of tables and keep the data on the horizontal it make
: for some combersomeness. I'm sure this is a common problem but I have
: never need more that 5-20 columns per table.
:
: any input on this is greatly appreciated!
:
: I also am considering writing code that may compress/decompress the
: columns into say a blob, but this again leaves me in the cold, with out
: the power of sql...
:
:
: Gary

Maybe I wasn't clear. I was referring to the number of columns mysql will
allow you to define per table. which i think is around 3000+/- . my
data is genetic data for individuals in a study/project. they tell
me that one individual could have up 4500 markers. i could have the
markers defined like:

id marker geneticpiece1 geneticpiece2
....
id marker geneticpieceN geneticpieceN
....

or

id marker1 gene1 gene2 marker2 gene1 gene2 ....
id marker1 gene1 ....

the latter format, although not complete normalized yet is more
appealing. I can do sorts/selects with more ease than the former
way. i could not access your site will try later. hope this helps
explain my question.

the application is web based and having selects that return a large row
of genetic data is partly what i was aiming for and also keeping it as
relational as possible for ease of maintainance..

g.

On Wed, 30 Mar 2005 20:01:03 -0800, Dr.Tulip wrote:

> Hi G,
> I don't quite understand the question, the table length is defined by you
> just like in C/C++ programming. You can have one cell handling as many
> character as you wanted. The text will be wrapping around when displaying
> it. Please take a look at my sample to see if this is what you are trying to
> do:
>
> http://67.171.3.18/isapi.dll?Query=U...OM+Suppli ers
>
> Main page:
> http://67.171.3.18/websql.htm
>
> If my server is not on, then try again later.
>
> Dr. 2-lips
>
>
>
> "G" <nospam@nospam.org> wrote in message
> newsan.2005.03.31.00.55.12.50577@nospam.org...
> : Hi,
> :
> : I have table/db I need to create that has a rectangle structure even
> : when normalized, but the difficulty is that the rows can have a large
> : number of column - over the max of what mysql can handle. If I put the
> : data in vertical format I lose a lot of functionality. If I split the
> : tables into n number of tables and keep the data on the horizontal it make
> : for some combersomeness. I'm sure this is a common problem but I have
> : never need more that 5-20 columns per table.
> :
> : any input on this is greatly appreciated!
> :
> : I also am considering writing code that may compress/decompress the
> : columns into say a blob, but this again leaves me in the cold, with out
> : the power of sql...
> :
> :
> : Gary

You should be able to expand your database size. This could be done through
your administration tools. One thing you can do to find out if you have
reached the maximum database size, is to look at the actual db file size.
I don't know about MySql limit, but I can tell you this with Microsoft SQL
server, if your db file size is over or close to 10gb then you are reaching
the maximum for that table. You can still chain the database together but I
never find any need to reach the maximum any how.

Here is the spec of SQL 7.0 small Business model:
------------

SQL Server Small Business Server Edition
SQL Server 7.0, Small Business Server Edition ships on the same compact disk
with Microsoft Small Business Server version 4.5. It is not available as a
stand-alone product. It is designed for installation on servers running Small
Business Server 4.5. SQL Server 7.0 Small Business Server Edition is limited
to 10 GB of storage per database, a single processor, fifty concurrent
queries, and ships with a full complement of graphical administration tools.

---------------


Dr. 2-lips.

"G" <nospam@nospam.org> wrote in message
newsan.2005.03.31.04.44.36.820769@nospam.org...
: Maybe I wasn't clear. I was referring to the number of columns mysql will
: allow you to define per table. which i think is around 3000+/- . my
: data is genetic data for individuals in a study/project. they tell
: me that one individual could have up 4500 markers. i could have the
: markers defined like:
:
: id marker geneticpiece1 geneticpiece2
: ...
: id marker geneticpieceN geneticpieceN
: ...
:
: or
:
: id marker1 gene1 gene2 marker2 gene1 gene2 ....
: id marker1 gene1 ....
:
: the latter format, although not complete normalized yet is more
: appealing. I can do sorts/selects with more ease than the former
: way. i could not access your site will try later. hope this helps
: explain my question.
:
: the application is web based and having selects that return a large row
: of genetic data is partly what i was aiming for and also keeping it as
: relational as possible for ease of maintainance..
:
: g.
:
: On Wed, 30 Mar 2005 20:01:03 -0800, Dr.Tulip wrote:
:
: > Hi G,
: > I don't quite understand the question, the table length is defined by you
: > just like in C/C++ programming. You can have one cell handling as many
: > character as you wanted. The text will be wrapping around when
displaying
: > it. Please take a look at my sample to see if this is what you are
trying to
: > do:
: >
: > http://67.171.3.18/isapi.dll?Query=U...OM+Suppli ers
: >
: > Main page:
: > http://67.171.3.18/websql.htm
: >
: > If my server is not on, then try again later.
: >
: > Dr. 2-lips
: >
: >
: >
: > "G" <nospam@nospam.org> wrote in message
: > newsan.2005.03.31.00.55.12.50577@nospam.org...
: > : Hi,
: > :
: > : I have table/db I need to create that has a rectangle structure even
: > : when normalized, but the difficulty is that the rows can have a large
: > : number of column - over the max of what mysql can handle. If I put the
: > : data in vertical format I lose a lot of functionality. If I split the
: > : tables into n number of tables and keep the data on the horizontal it
make
: > : for some combersomeness. I'm sure this is a common problem but I have
: > : never need more that 5-20 columns per table.
: > :
: > : any input on this is greatly appreciated!
: > :
: > : I also am considering writing code that may compress/decompress the
: > : columns into say a blob, but this again leaves me in the cold, with out
: > : the power of sql...
: > :
: > :
: > : Gary
:

i'm more of an opensrc guy, but thanks for the input. mysql does have
a 3k limit on column number. i'll probably just do a more vertical
table on this data, instead of horizontal. thanks, g.

On Wed, 30 Mar 2005 22:29:54 -0800, Dr.Tulip wrote:

> You should be able to expand your database size. This could be done through
> your administration tools. One thing you can do to find out if you have
> reached the maximum database size, is to look at the actual db file size.
> I don't know about MySql limit, but I can tell you this with Microsoft SQL
> server, if your db file size is over or close to 10gb then you are reaching
> the maximum for that table. You can still chain the database together but I
> never find any need to reach the maximum any how.
>
> Here is the spec of SQL 7.0 small Business model:
> ------------
>
> SQL Server Small Business Server Edition
> SQL Server 7.0, Small Business Server Edition ships on the same compact disk
> with Microsoft Small Business Server version 4.5. It is not available as a
> stand-alone product. It is designed for installation on servers running Small
> Business Server 4.5. SQL Server 7.0 Small Business Server Edition is limited
> to 10 GB of storage per database, a single processor, fifty concurrent
> queries, and ships with a full complement of graphical administration tools.
>
> ---------------
>
>
> Dr. 2-lips.
>
> "G" <nospam@nospam.org> wrote in message
> newsan.2005.03.31.04.44.36.820769@nospam.org...
> : Maybe I wasn't clear. I was referring to the number of columns mysql will
> : allow you to define per table. which i think is around 3000+/- . my
> : data is genetic data for individuals in a study/project. they tell
> : me that one individual could have up 4500 markers. i could have the
> : markers defined like:
> :
> : id marker geneticpiece1 geneticpiece2
> : ...
> : id marker geneticpieceN geneticpieceN
> : ...
> :
> : or
> :
> : id marker1 gene1 gene2 marker2 gene1 gene2 ....
> : id marker1 gene1 ....
> :
> : the latter format, although not complete normalized yet is more
> : appealing. I can do sorts/selects with more ease than the former
> : way. i could not access your site will try later. hope this helps
> : explain my question.
> :
> : the application is web based and having selects that return a large row
> : of genetic data is partly what i was aiming for and also keeping it as
> : relational as possible for ease of maintainance..
> :
> : g.
> :
> : On Wed, 30 Mar 2005 20:01:03 -0800, Dr.Tulip wrote:
> :
> : > Hi G,
> : > I don't quite understand the question, the table length is defined by you
> : > just like in C/C++ programming. You can have one cell handling as many
> : > character as you wanted. The text will be wrapping around when
> displaying
> : > it. Please take a look at my sample to see if this is what you are
> trying to
> : > do:
> : >
> : > http://67.171.3.18/isapi.dll?Query=U...OM+Suppli ers
> : >
> : > Main page:
> : > http://67.171.3.18/websql.htm
> : >
> : > If my server is not on, then try again later.
> : >
> : > Dr. 2-lips
> : >
> : >
> : >
> : > "G" <nospam@nospam.org> wrote in message
> : > newsan.2005.03.31.00.55.12.50577@nospam.org...
> : > : Hi,
> : > :
> : > : I have table/db I need to create that has a rectangle structure even
> : > : when normalized, but the difficulty is that the rows can have a large
> : > : number of column - over the max of what mysql can handle. If I put the
> : > : data in vertical format I lose a lot of functionality. If I split the
> : > : tables into n number of tables and keep the data on the horizontal it
> make
> : > : for some combersomeness. I'm sure this is a common problem but I have
> : > : never need more that 5-20 columns per table.
> : > :
> : > : any input on this is greatly appreciated!
> : > :
> : > : I also am considering writing code that may compress/decompress the
> : > : columns into say a blob, but this again leaves me in the cold, with out
> : > : the power of sql...
> : > :
> : > :
> : > : Gary
> :