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

How can you tell this sample is coming from Human, Animal or other by giving you a SampleID ?

You need to search every tables: Human, Animal, others,....

"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 Oct 18 2002 - 04:11:57 CEST