Re: /!\ What's the best Database Design for such a problem ?

First, thank you very much for your fast answer. I didn't think about the Alan's suggestion. It's impressive and clear and allow possible update concerning the "other class". Kieran's solution is clear too, I tried to solve this by doing supertype/subtypes class (see my posts on http://dbforums.com - pyrahna as nickname) but ... well ... i have to say that ...

How do you query such things ? I mean, as you will easily understand, the database design is huger than this little part i exposed. A sample gonna have a lot of analyses on it and each analyses will have a lot of tests on it. When i will be working on a test i'll be able to retrieve the analyse id and thus, the corresponding sample id ... well that's ok and now how will i know if it's a human, an animal or something else ?

Do i have to run this first query to retrieve all informations of the sample except the "source's informations" and then, run another query to retrieve the samples'source informations ? An union simply can be done cause there are various fields on each source's type and not corresponding data type...

I can't find a way not to do 2 queries instead of one (and i will be more than 2 for the second one because it will be like "if it's human go and see in human and retrieve this and this, if it's animal i need that and that and so on)

Any Idea ?

Best Regards

Francois Verbeeck
Kieran <kieran_at_dunelm.org.uk> wrote in message news:<3D8A38B9.6020005_at_dunelm.org.uk>...
> Francois Verbeeck wrote:
> > Dear all,
> >
> > I'm facing a common problem but can't find the right solution.
> >
> > I'm working in a bio-medical center and everyday we receive sample to
> > analyse.
> >
> > Samples can come from
> > - human
> > - animals
> > - others (food, water, gaz, grass, and all you can think that isn't
> > human or animal)
> >
> > and can be of type : blood, plasma, pee, ...
> >
> > Since each source is totally different and correspond to a specialized
> > class, i create something like this
> >
> > HUMAN (id_human, firstname, lastname, ... )
> > ANIMALS (tatoo_number, category, ... )
> > OTHERS (id_others, category, description, notes, ... )
> >
> > SAMPLES (id_sample, volume, id_type, ID_HUMAN_SOURCE,
> > ID_ANIMAL_SOURCE, ID_OTHER_SOURCE)
> >
>
> Inheritance / sub-typing can be somewhat tricky to model effectively in
> a relational database.
>
> Alan's suggestion is one good way of doing this, but one other
> suggestion is:
>
> -- Store general info. about a sample
> SAMPLE(id_sample, volume, ...)
>
> -- Store info. about the possible sample sources
> HUMAN(human_id, firstname, ...)
> ANIMAL(tatoo_num, category, ...)
> OTHER(Other_id, category, desc, notes, ...)
>
> -- Record the fact that you have a sample from a source
> -- TODO: might want to think up better names for these tables
> HUMAN_HAS_SAMPLE(sample_id, human_id)
> ANIMAL_HAS_SAMPLE(sample_id, animal_id)
> OTHER_HAS_SAMPLE(sample_id, other_id)
>
> You can then create views from this - e.g.
>
> -- Info about a human sample
> create view HUMAN_SAMPLE
> select
> s.id_sample, s.volume,
> h.human_id, h.firstname, h. ...
> from
> sample s,
> human h,
> human_sample hs
> where
> s.sample_id = hs.sample_id
> and h.human_id = hs.human_id;
>
> Your RDMS may or may not allow you to update and insert into such views,
> however.
>
> Presumably, you're also going to need to use a similar technique to
> model different sample types.
>
> Regards,
> Kieran Elby
Received on Fri Sep 20 2002 - 09:49:01 CEST