I have roughly 30,000 objects (tissue samples) for which I would like to store 1500 attributes for each sample. Each attribute is a numerical score eg. 3.4567.
I set up a table with 1501 columns (an id column + attributes 1 - 1500) with each attribute column set to be a numeric type.
I began to insert my data, but got the error "ERROR: row is too big: size 8172, maximum size 8164" when inserting the data of the very first object.
I altered the columns to be of type text and retried. same error.
Is there a datatype I could use to allow me to enter 1500 values similar to "3.4567" in characteristic? The official line is "Maximum Columns per Table 250 - 1600 depending on column types"
What column type can I use to achieve this maximum?
It would be nice if the values were stored as decimals, but text or other would suffice.
I realize that this many columns is far from ideal, but the other option of 30,000 tables with 1500 rows in each doesn't seem right either. There is always the possibility I could add more samples, meaning adding many more tables - not exactly a manageable situation!
I would really appreciate any advice on this.
Just figured out the answer...
The size was too big because I was using datatype "numeric" without setting the scale and precision, thus more space than was required was being set aside for each attribute. -D'oh!
The datatype "real" also works.
For more info on datatypes and their space requirements see here.
Hope this helps somebody else.
I agree with shammat/vobor.aim, eoinyp. Relational databases tend to work more effectively when the table data is stored in a 'tall, narrow" format. i.e. fewer columns, more rows.
The one thing I would consider adding to shammat's design is an ordering sequence (order_seq) column in the 'object_attributes' table. (the value of which would be 0-N, resetting for each separate sample number.) Without an explicit sort order, no database can guarantee the order of the data retrieved. Unless there's a natural order in the attribute values, a surrogate ordering mechanism can be useful.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert