Initial schema problems

Hi folks-
I am building my first database for the proteomic facility where I work.

Basically we have Clients who on a certain date submit 1 to n unique samples for analysis. The analysis protocol will be the same for each sample, which comprises an "experiment". Unfortunately, tracking the samples through a very complicated multi-step analysis has proven to be pretty difficult. The analysis protocol may not be unique in and of itself, there are some pretty common parameters we use on everybody's samples, but sometimes they are altered. If we have enough sample leftover to do another analysis (with slightly different parameters) we will.
Can somebody check this small prototype and tell me if I'm on the right track? I'm not sure I like the FK used in the tblExperiment.

Any and all help will be appreciated, we are a not-for-profit university core facility under the national cancer institute (US).

Heath

Which version of Access are you using. Could you post a Jpeg of your database desgin?

S-

Hi SBaxter
me again. I don't have an e-layout of the DB, just lots and lots of scratch paper...LOL. Maybe a bit more info might shed some light on how I envision this. I'm using Access 2003.

Client submits batch of samples. samples in batches undergo identical analysis procedures. one client, many samples. One procedure, many samples. let's define experiment as all the samples from client I analyzed as a group. so, one client, many experiments (either on new samples or re-runs with different protocols).

the goal is a form that allows selection of client (if present), input of samples (if new), single input of analysis parameters, attaching the just defined analysis parameters to all samples in experiment.

I'm can't decide on how to link analysis parameters to samples, while still retaining the option of re-running the same samples differently. I can see how to do it with lots of redundant data entry, but that's the whole point of this. I need to eat lunch, head is once again spinning.

Heath

Here is what I am going to do, based on the info you gave me I will tell you how I would setup your tables (and there Primary Key) and how they will relate to each other (foreign Key) and a couple of assumptions that I would include into the system.


Client Table
- ClientID (PK)

AnalysisProcedures Table
- AnaProd (PK)

Experiment Table
- ExperID (PK)
- ClientID (FK)
- AnaProd (FK)

Sample Table
- SampleID (PK)

SampleForExperiments Table
- ExperID (PK/FK)
- SampleID (PK/FK)

Assumption
-Only one analysis procedure can belong to an experiment. IF I need to do additional types of analysis of sample, this is preformed under the umbrella of a new experiement (which is way the SampleForExperiments Table exists (sample can belong to more then one experiment and experiements could have more then one sample)

There is a lot to know when designing your database, and you have to take in consideration you skills to create this DB. You talke about redundant data entry, There doens't need to be very much, but this depends on the skills to design and then develop this project.

Let me know fi you have any other questions.

S-

You have assumed correctly

A Hearty Thanks- I'm sure I'll be checking in again