On Apr 30, 1:00 pm, Lionel van den Berg <lione...@gmail.com> wrote:
> I'm not exactly an expert at database design so I thought I would ask
> this question here.
>
> I've got two objects one is an extravascular (EV) dose object and the
> other is an intravascular (IV) dose object. For all functional purposes
> an IV dose is the same as an EV dose but also has an infusion time.
>
> Current my database has a single table, as an example:
>
> Dose {
> id; //Primary key
> courseId;
> infusionTime;
>
> }
>
> Obviously this isn't acceptable since the infusionTime is a waste of
> space for an EV dose. I have three solutions but first I need to provide
> a little more info.
>
> A dose belongs to a Course, the courseId references the id of a course
> table:
>
> Course {
> id; //Primary key
> dugName;
>
> }
>
> and a Course has a drug with drugName being a unique name in the drugs
> table:
>
> Drug {
> drugName; //Primary key
> isIVDrug;
>
> }
>
> I can figure out if a Dose is an IV or EV dose by knowing the drug that
> is being dosed which can be figured out by looking up the course - a bit
> of a complex relationship. I also know at the time of saving if a dose
> is IV or EV by a simple single call in the programming language.
>
> Now some design ideas:
>
> The most obvious:
>
> Dose {
> id; //Primary key
> courseId;
> isIVDose;
>
> }
>
> IVDose {
> doseId; //Primary key references Dose.id
> infusionTime;
>
> }
>
> But when I look at this, I sort of realised that I've got more fields
> than I did before so I'm wondering if the following solution is acceptable?
>
> Dose {
> id; //Primary key
> courseId;
>
> }
>
> IVDose {
> doseId; //Primary key
> infusionTime;
>
> }
>
> Now when I'm reading a dose from the database I have two options, I can
> figure out if it is an IVDose by querying the IVDose table for an entry
> with doseId == Dose.id and see if an entry exists, or I can perform a
> complicated query to check what the drug is and then I will know that an
> entry must or must not exist in the IVDose table and can retrieve it
> accordingly.
>
> I would like to know if the second solution is acceptable and worth it
> to reduce the number of fields by 1.
>
> Thanks
>
> Lionel.

In some hierarchichal models a root parent is simply one without
ancestors, i.e. where `parent` is null. Similarly an EV dose can
simply be described as a dose with a NULL infusion time. The 'waste of
space' you describe is trivial. So, purely on the information
provided, I'd be tempted to go with this model. There is a danger that
an incomplete entry for an IV dose might be misinterpreted as an EV
dose, but I think this could be handled through the data input
interface and judicious use of the DEFAULT setting - and it seems to
me that this danger would exist regardless of which data model was
employed.