469,090 Members | 1,187 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

db/table design question

G
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
Jul 23 '05 #1
5 1235
how many columns do you need per table ?
"G" <no****@nospam.org> wrote in message
news:pa***************************@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

Jul 23 '05 #2
G
i'm working with genetic data and it's composed of
marker1 allele1_1 allele1_2 .... markerN alleleN_1 alleleN_2
they tell me there can be upto 1400 markers per individual. I could
make the markers a 1 to many table and add a way to associate them to the
alleles, but that still leaves me at about 2800+ column. hope this sheds
some light on what you asked? thanks.
On Thu, 31 Mar 2005 12:59:58 +1000, Speedy LJ wrote:
how many columns do you need per table ?
"G" <no****@nospam.org> wrote in message
news:pa***************************@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


Jul 23 '05 #3
On Wed, 30 Mar 2005 20:08:33 -0800, in mailing.database.mysql G
<no****@nospam.org> wrote:
| i'm working with genetic data and it's composed of
| marker1 allele1_1 allele1_2 .... markerN alleleN_1 alleleN_2
| they tell me there can be upto 1400 markers per individual. I could
| make the markers a 1 to many table and add a way to associate them to the
| alleles, but that still leaves me at about 2800+ column. hope this sheds
| some light on what you asked? thanks.


How about:
table Marker
PKMarkerID int(11) auto-increment
<other data fields>
Table Alleles
fkMarkerID int(11)
alleleNbr int(10) unsigned
alleleValue int(11)

This would store the data in the most efficient method.
Later on you could retrieve selective information like:
SELECT *
FROM Alleles
WHERE alleleNbr = 10 and alleleValue between 50 and 100;
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #4
G
yes, the more I think about this the better it sounds.
when i start developing with it it may get trickier.
having it all horizontal made for easy 'order by's for
the web, but you have to give up somethings. thanks, g.

On Thu, 31 Mar 2005 16:00:52 +0000, Jeff North wrote:
On Wed, 30 Mar 2005 20:08:33 -0800, in mailing.database.mysql G
<no****@nospam.org> wrote:
| i'm working with genetic data and it's composed of
| marker1 allele1_1 allele1_2 .... markerN alleleN_1 alleleN_2
| they tell me there can be upto 1400 markers per individual. I could
| make the markers a 1 to many table and add a way to associate them to the
| alleles, but that still leaves me at about 2800+ column. hope this sheds
| some light on what you asked? thanks.


How about:
table Marker
PKMarkerID int(11) auto-increment
<other data fields>
Table Alleles
fkMarkerID int(11)
alleleNbr int(10) unsigned
alleleValue int(11)

This would store the data in the most efficient method.
Later on you could retrieve selective information like:
SELECT *
FROM Alleles
WHERE alleleNbr = 10 and alleleValue between 50 and 100;
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------


Jul 23 '05 #5
On Thu, 31 Mar 2005 10:20:45 -0800, in mailing.database.mysql G
<no****@nospam.org> wrote:
| yes, the more I think about this the better it sounds.
| when i start developing with it it may get trickier.
| having it all horizontal made for easy 'order by's for
| the web, but you have to give up somethings. thanks, g.
Not really, you need to create a cross-tab query.
http://dev.mysql.com/tech-resources/...ard/index.html

| On Thu, 31 Mar 2005 16:00:52 +0000, Jeff North wrote:
|
| > On Wed, 30 Mar 2005 20:08:33 -0800, in mailing.database.mysql G
| > <no****@nospam.org> wrote:
| >
| >>| i'm working with genetic data and it's composed of
| >>| marker1 allele1_1 allele1_2 .... markerN alleleN_1 alleleN_2
| >>| they tell me there can be upto 1400 markers per individual. I could
| >>| make the markers a 1 to many table and add a way to associate them to the
| >>| alleles, but that still leaves me at about 2800+ column. hope this sheds
| >>| some light on what you asked? thanks.
| >
| > How about:
| > table Marker
| > PKMarkerID int(11) auto-increment
| > <other data fields>
| >
| >
| > Table Alleles
| > fkMarkerID int(11)
| > alleleNbr int(10) unsigned
| > alleleValue int(11)
| >
| > This would store the data in the most efficient method.
| > Later on you could retrieve selective information like:
| > SELECT *
| > FROM Alleles
| > WHERE alleleNbr = 10 and alleleValue between 50 and 100;
| > ---------------------------------------------------------------
| > jn******@yourpantsyahoo.com.au : Remove your pants to reply
| > ---------------------------------------------------------------


---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by mr_burns | last post: by
reply views Thread by 2trax | last post: by
15 posts views Thread by kimi | last post: by
4 posts views Thread by yanjie.ma | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.