MySQL Database Design – Managing Tables with More Than 100 Columns

database-designMySQL

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?

Best Answer

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

Related Solutions

How to model data from arbitrary CSV’s in a database

The question is so wide open that it is hard to say much.

And why are you using a database at all? Excel does everything you've described so far.

Seriously though, avoid making an inner system in your database that can store any kind of data. You have one already, called a DATABASE. Make code that creates the tables you need. What's so bad about that? I say option 1.

You know, if you want infinite flexibility how about a square-mile white board?

Classifieds Database Design

You need to decide if your categories are hierarchical (one parent) or graphical (multiple parents). For example, are "cell phones" in the category "electronics" as well as "communications"?

What kinds of fields might your categories have? Use Table Inheritance to allow categories that are similar but not identical.

Let's say you go with hierarchical categories. It should look like this:

-- hierarchical relationships usually have a single table with a field with a self-referencing foreign key:
create table category (
  id bigserial primary key,
  name text not null unique,
  parent_id bigint null references category(id) --if I'm null then I am a top-level category
);

create table ad (
  id bigserial primary key,
  subject text not null
  ...
);

-- this pattern is called a junction table:
create table ad_category (
  ad_id bigint references ad(id),
  category_id bigint references category(id),
  primary key (ad_id, category_id)
);

I would suggest Postgres over MySql, since it supports WITH queries, which make hierarchical querying easier.

Regarding images, consider storing them in Amazon S3 / Cloudfront. You would associated them with the ad via the HTML in the ad. For example, in your UI, the user would upload an image, get the URL back, and this would get plugged in to an <img> tag.

Though you could also create a junction table similar to the ad_category table, and remove orphan images from S3 on a schedule:

create table ad_picture (
  ad_id bigint not null references ad(id),
  picture_url text not null,
  unique (ad_id, picture_url)
);

Best Answer

Related Solutions

How to model data from arbitrary CSV’s in a database

Classifieds Database Design

Related Question