Professional Web Applications Themes

Database design - sorry can't think of a better title :( - MySQL

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

  1. #1

    Default Database design - sorry can't think of a better title :(

    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 Guest

  2. #2

    Default Re: Database design - sorry can't think of a better title :(

    On Apr 30, 1:00 pm, Lionel van den Berg <com> 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. 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.

    strawberry Guest

  3. #3

    Default Re: Database design - sorry can't think of a better title :(

    Lionel van den Berg wrote: 

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

  4. #4

    Default Re: Database design - sorry can't think of a better title :(

    >I'm not exactly an expert at database design so I thought I would ask 

    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?
     

    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.

     

    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.


    Gordon Guest

  5. #5

    Default Re: Database design - sorry can't think of a better title :(

    strawberry wrote: 


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

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

Similar Threads

  1. Database design
    By Ba in forum Dreamweaver AppDev
    Replies: 9
    Last Post: September 28th, 08:31 PM
  2. Changing the database name on the Title Bar
    By Craig in forum Microsoft Access
    Replies: 2
    Last Post: September 18th, 02:51 PM
  3. Central Database Design
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 11th, 10:13 PM
  4. Database Design
    By Mohan in forum Oracle Server
    Replies: 10
    Last Post: July 15th, 10:05 PM
  5. Database Design
    By Sampangi in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 13th, 09:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139