/!\ What's the best Database Design for such a problem ?
Date: 19 Sep 2002 07:48:52 -0700
Message-ID: <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 - 16:48:52 CEST