I have a table in my MySQL database which contains data about XRF analyses.

There are about 50 chemical elements and for each element there are two values to be stored (Area and Count). Users need to perform queries on a specific element. The idea is that when they upload a new record, they provide a csv file and the fields are automatically filled.

My question is: is it ok to have a table with more than 100 columns? Is there a smarter way to implement this?

  • Why not have CREATE TABLE XRF_Data(element CHAR(2), Area float, X_Count int); Note, I didn't use "COUNT" as a field name, it being a reserved word in SQL (and maybe you should use X_Area - some GIS systems have it as a word). That way, you don't have a huge number of columns. Is there an X_time field or something to distinguish between different measurements of the same element? – Vérace May 7 '15 at 10:25
  • Even though maximum limit is 4096, but that will be bad design to have 100 in one table as it will definitely effect performance. Refer : Too many columns – Koustuv Chatterjee May 7 '15 at 13:32

I would suggest using two tables, one to store the records uploaded and make an id for it, and another to hold the elements for the record.data types are just for illustration purpose only

create table UploadedRecords
    id int not null auto_increament,
    uploadtime datetime not null,
    primary key (uploaded_record_id)

create table RecordElements
    uploaded_record_id int not null,
    element_symbol varchar(2) not null,
    area varchar(20) not null,
    cnt int not null,
    primary key 
        (uploaded_record_id, element_symbol),
    foreign key (uploaded_record_id) 
        references UploadedRecords (uploaded_record_id)
| improve this answer | |
  • 1
    Adding a unique (or primary key) constraint on (recordid, elementName) would be the point here. Right? – ypercubeᵀᴹ May 7 '15 at 11:03
  • Hi, thank you for your answer. I think I'm going this way. But what is the advantage of having a lot of records rather than a lot of columns? – Francesco Setragno May 7 '15 at 11:49
  • Try to write an insert statement for a table with 100 columns then you knw what is the different :) ... MySQL and many other relational databases are made to handle lot's of rows but not lot's of columns. @ypercube that constraints could be added too for data integrity as well as performance. – Masoud May 7 '15 at 11:55
  • Masoud, then edit your answer ;) – ypercubeᵀᴹ May 7 '15 at 12:03
  • 1
    @Masoud. Welcome. My edit was just a suggestion. Any edit can be reversed or modified, anyway. – ypercubeᵀᴹ May 8 '15 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.