Date: 03/22/08 (MySQL Communtiy)    Keywords: database, asp

I need to design a database to store a large number of numeric markers and am interested in feedback on how best to do this.

There are currently 80+ markers that need measuring, and additional markers will likely be added in the future. Each set of markers is associated with a test_ID (containing date of collection, ect). Also, each marker will have meta-descriptive info stored in a separate table.

Reporting and performance are important aspects of this system. I need to be able to generate percentiles, max/min, standard deviation, and other statistics on a given date range. I will also need to query against certain markers with values above or below certain ranges.

An existing (old) database was created years ago with each marker as a separate column - we've worked with this but any time new markers are added I have to spend several days revising & testing scripts. I want to avoid this pitfall in the new system if possible.

There will be a LOT of data stored in the database, so performance and optimization are key objectives.

So, my initial thought was to store markers in a table like this:

CREATE TABLE results (
id int(20) NOT NULL auto_increment,
test_id int(10) NOT NULL,
marker_id int(3) default NULL,
marker_value decimal(8,3) default NULL,
marker_second_value decimal(8,3) default NULL,
PRIMARY KEY (id),
KEY test_id (screen_id),
KEY marker_id (analyteid)
)

Source: http://community.livejournal.com/mysql/126320.html