Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Maximum columns problem

    Hi all,
    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.
    Many thanks,
    Eoinyp

  2. #2
    Join Date
    Apr 2010
    Posts
    2
    Hi,
    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.
    Thanks,

    Eoinyp

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I don't understand why the alternative is to have 30,000 tables?

    You need one table to store the object definition, and another table to store the attributes of the objects.

    Something like:
    Code:
    CREATE TABLE object_header
    (
       id integer not null primary key,
       object_name varchar(50) not null
    )
    ;
    
    CREATE TABLE object_attributes
    (
       object_id integer not null references object_header(id),
       attr_nr integer not null,
       attr_value decimal(15,6),
       primary key (object_id, attr_nr)
    );

  4. #4
    Join Date
    Apr 2010
    Posts
    16
    I agree with Shammat. Keeping So much attribute does not make any sense... Issue may come if you require to extend the Attribute size to meet future requirement

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "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


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •