Take the 2-minute tour ×

I have a question about table design and performance. I have a number of analytical machines that produce varying amounts of data (which have been stored in text files up to this point via the dos programs which run the machines). I have decided to modernise and create a new database to store all the machine results in.

I have created separate tables to store results by type e.g. all results from the balance machine get stored in the balance results table etc.

I have a common results table format for each machine which is as follows:

ClientRequestID  PK
SampleNumber     PK

A typical ClientRequest might have 50 samples which need to tested by various machines. Each machine records only 1 line per sample, so there are apprx 50 rows per table associated with any given ClientRequest.

This is fine for all machines except one!

It measures 20-30 analytes per sample (and just spits them out in one long row), whereas all the other machines, I am only ever measuring 1 analyte per RequestID/SampleNumber. If I stick to this format, this machine will generate over a miliion rows per year, because every sample can have as many as 30 measurements. My other tables will only grow at a rate of 3000-5000 rows per year.

So after all that, my question is this:

Am I better to stick to the common format for this table, and have bucket loads of rows, or is it better to just add extra columns to represent each Analyte, such that it would generate only 1 row per sample (like the other tables). The machine can only ever measure a max of 30 analytes (and a $250k per machine, I won;t be getting another in my lifetime).

All I am worried about is reporting performance and online editing. In both cases, the PK: RequestID and SampleNumber remain the same, so I guess it's just a matter of what would load quicker. I know the multiple column approach is considered woeful from a design perspective, but would it yield better performance in this instance?

BTW the database is MS Jet / Access 2010

Any help would be greatly appreciated!

share|improve this question
"BTW the database is MS Jet / Access 2010" That's 80% of your problem. Use almost any ODBC database and you'll be happier with performance and space utilization. –  S.Lott Jul 29 '10 at 23:41
add comment

2 Answers

Millions of rows in a Jet/ACE database are not a problem if the rows have few columns.

However, my concern is how these records are inserted -- is this real-time data collection? If so, I'd suggest this is probably more than Jet/ACE can handle reliably.

I'm an experienced Access developer who is a big fan of Jet/ACE, but from what I know about your project, if I was starting it out, I'd definitely choose a server database from the get go, not because Jet/ACE likely can't handle it right now, but because I'm thinking in terms of 10 years down the road when this app might still be in use (remember Y2K, which was mostly a problem of apps that were designed with planned obsolescence in mind, but were never replaced).

share|improve this answer
add comment

You can decouple the AnalyteName column from the 'common results' table:

-- Table Common Results

ClientRequestID PK SampleNumber PK MeasureDtTm Operator UnitOfMeasure Value

-- Table Results Analyte

ClientRequestID PK SampleNumber PK AnalyteName

You join on the PK (Request + Sample.) That way you don't duplicate all the rest of the rows needlessly, can avoid the join in the queries where you don't require the AnalyteName to be used, can support extra Analytes and is overall saner. Unless you really start having a performance problem, this is the approach I'd follow.

Heck, even if you start having performance problems, I'd first move to a real database to see if that fixes the problems before adding columns to the results table.

share|improve this answer
add comment

Your Answer


