a beginners design headaches

hello

im rather new to database design and im building a sequence and lab management web database for a conservation genetics company.

the main design headache im encountering is modelling the data recorded for the automatic dna sequencer and pcr machine, these have a large number of lanes or positions, usually about 96, that each can store an individual sample that has a number of different fields associated with it e.g. tissue type, elution volume etc etc.

i need to record the data foreach particular run of these machines; so i have a table containing the id number of the run with other general information not specific to a lane/position, and then have alot of further one to one related tables that hold one piece of data foreach of the lanes/positions. consequently my table count is growing huge (is this a bad thing by the way?).

im considering combining data into single fields but from what i understand of db design this is a big no no - but might be the only solution. if anyone has the inclination to discuss this with me, offer comments or point me to some info sources on designing dbs that arent the usual business models or ecom sites, id be hugely appreciative.

cheers.

j

You didn't mention what database engine you are using. Based on what you are doing, I'll assume you talking in terms of millions of records.

If you are using MS Access then yes you will run into problems. I haven't used Access for anything useful in several years. But I remember it acted flakey once we got past a few hundred thousand rows. It would often lock up and the database would become corrupt. My recommendation is to switch to a better database.

If you are using a professional database (SQL Server, DB2, Oracle, MySQL, etc etc) then don't worry about it. Just make sure you back up the database often and have lots of HD space.

You can concatenate data if you want. The database police won't come and take you away. I have an NMR spectra database that has concatenated data. One record for each material. The scientist wrote some very creative code to parse the points quickly. Think about how often the data is accessed and how long it will take to parse it. Will you need to update it? Update code will be much more complex if data is concatenated.

Maybe you want to archive the data is some fashion. Either by concatenating old data or simply by creating a new table or database each month. Or maybe your inserts and updates work fine, but reports take too long. In this case consider archiving the reports so they don't need to be rebuilt all the time. There are lots of options. Experiment and find what works best for your situation.

Matt.