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

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 Thu Sep 19 2002 - 15:51:05 CDT