Lionel van den Berg 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.

Lionel,

In database programming I always look at access time and how many trips
I have to make to the database to find what I want (not particularly in
that order). Storage should not be a concern these days as it is so
inexpensive.

So if the table is well indexed this is easily accomplished provided you
don't have any runaway sql statements. In my opinion your current design
is satisfactory. The other methods you've mentioned increase your trips
and/or increase the number and/or volume of queries you need to make to
find what you're looking for.

hope this helps.