SEO

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.

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.

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.

>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.

When you do queries, how often do you want to treat the EV doses and IV
doses together for the purpose of the query? How often do you do queries
that must retrieve only an EV dose or only an IV dose?

>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.

If you're that concerned about a tiny waste of disk space, you
really will need to take blood pressure medication when you hear
about how much disk space indexes waste. Or extra tables.

Normalizing databases is often an exercise in wasting disk space
by repeating the primary key in multiple places. Plus, you often
optimize queries by wasting huge amounts of disk space on indexes.

I see nothing particularly wrong with an EV dose having a null
infusionTime.

A much more important consideration may be whether you can pull up
information on a dose fast enough that the next dose isn't already
due.


>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.

How many EV doses are there? How many IV doses are there? Let's
assume 50,000 EV doses and 50,000 IV doses. Your original design
had 300,000 fields (50,000 of them null). Your new design has
300,000 fields, none of them null. And another index. This was
going to save disk space? Well, it might if there are a lot fewer
IV doses than EV doses. You also need to do a join to get the
infusionTime or check if it's EV or IV.

strawberry wrote:
> 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.


Thanks to both responses. All I really need to do is just set the
infusion field to null for EV doses.

> 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

This doesn't pose a problem as long it is only my software accessng the
database. My EV dose class will throw an exception if you try to get the
infusion time from it, and my IV class has a default infusionTime of 0.

Thanks again.

Lionel.