Table with more than 100 columns

Question

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?

Masoud · Accepted Answer · 2015-05-08T02:59:42.317

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)
);

Table with more than 100 columns

1 Answers1