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

I think you pretty much have it right. I can think of one alteration that may make your life a bit simpler: id_human always begins with an H, id_animal with an A, and id_others with an O. This makes your SAMPLES table easier to deal with, as there would only be one FK id column:

SAMPLES(sample_id(PK), source_id(FK), volume, other info...)

You then know by looking at the (first character of the) source_id what type of source it is, and the values will always be unique across the source types. This also allows you to identify the types of "others" if you wish to, simply by coding the 1st character as desired.

Relational purists will probably disagree with this design, but pure relational isn't always the best way to go.

"Francois Verbeeck" <francois_verbeeck_at_hotmail.com> wrote in message news:747f2a2e.0209190648.1fdf02c1_at_posting.google.com...
> 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)
>
> I can't make a category and subcategory like saying :
> it's human blood or dog pee to analyse, thus a simple field added
> related to a category and sub category can't be enough. If it's coming
> from a human i need to know all the things related to the human class,
> from an animal, all the things too and from "others" all infos i can
> gather.
>
> Do you see the problem ? It's moreover an exclusive relationships. I
> added some constraint to prevent from having more than one source ID
> but ... well ... i'm not proud of that design and i thing there should
> be a better solution.
>
> I tried to solve this with a supertypes / subtypes design but ...
> these 3 categories have nothing in common (well, yeah a dog and a
> human share the date of birth and the sex but the others class don't
> ...) and the primary key for the HUMANS, ANIMALS or OTHERS table
> simply can't be the foreign key of that supertype ... (because i used
> the class human for much more than knowing the sample source, it's
> also used to store biologist / doctor / customers / patients
> informations)
>
> I'm getting headaches with this problem. If anyone could help me
> (sorry for my english but i do my best ...)
>
> Thanks a lot.
> Regards
>
> Francois Verbeeck, Belgium
Received on Thu Sep 19 2002 - 20:31:45 CEST