2

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?

2
  • 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
    Commented May 7, 2015 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 Commented May 7, 2015 at 13:32

1 Answer 1

2

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)
);
6
  • 1
    Adding a unique (or primary key) constraint on (recordid, elementName) would be the point here. Right? Commented May 7, 2015 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? Commented May 7, 2015 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
    Commented May 7, 2015 at 11:55
  • Masoud, then edit your answer ;) Commented May 7, 2015 at 12:03
  • 1
    @Masoud. Welcome. My edit was just a suggestion. Any edit can be reversed or modified, anyway. Commented May 8, 2015 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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