SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2003
    Location
    Calif.
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database design tip, duplicate data?

    I have a table named HORSE. It contains all data regarding every horse and I set the association's registration number as the primary key since there will never be 2 alike. (I believe this should be okay?) i.e.
    reg_num
    reg_name
    nickname


    Now I have a table that records regular vaccination schedules. I created a php page with drop down menus to select the horse and the vaccine given.

    Should I INSERT into vaccination_table the primary key (reg_num) or the more user friendly nickname?

    What are the advantages/disadvantages or doing one or the other?

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It contains all data regarding every horse and I set the association's registration number as the primary key since there will never be 2 alike. (I believe this should be okay?)
    Clarify on this a bit: is the reg_num never alike for any 2 associations, or any 2 horses? If it's the latter, then it's fine.

    Should I INSERT into vaccination_table the primary key (reg_num) or the more user friendly nickname?
    If reg_num is your primary key, you should use that instead because it would allow you to uniquely match a vaccination schedule to a unique horse - using a nickname would encounter the possibility of 2 horses having the same nickname. (This goes into a bit on normalization and functional dependencies, but there is no need to get too theoretical I think.)

  3. #3
    SitePoint Member
    Join Date
    Jun 2003
    Location
    Calif.
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the reg_num would never be alike for any 2 horses. And the way the association works, no 2 horses can have the same registered name either (living or dead) so while 2 horses with the same nickname are possible, it isn't with the reg_name.

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK thanks for clarifying. Then it is fine to use it as a primary key - in fact it is a prime candidate together with the reg_name field.

  5. #5
    SitePoint Member
    Join Date
    Jun 2003
    Location
    Calif.
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tips redemption!
    Now I need a little help with a query. I have my vaccinations table and it holds
    reg_num (horse registration number)
    vacdate (date vaccination was given)
    vacid (id number of vaccine from vaccines table)

    the vaccines table has:
    vacid (id number of vaccine)
    name (name of vaccine)
    month_interval (how many months until next vaccination)

    I want a query to show me which horse is due what vaccination this month or maybe next month or both. I'm really new to this all, but I think I have to hold each vacid in a $var and then?

  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm... I'm seeing a little problem with your tables as it is now. Is vacdate the date the vacination was given, or the date the vacination would be/was given? Also, in the vaccines table, you have vacid and month_interval, which doesn't really tell you which horse is the one taking this vaccine, because there is nothing to tie it back to the vaccinations table unless the horse has already taken the vaccination.

    I suggest tables like this:
    Code:
    vaccination
    -----------
    vacid
    vacname
    vacdate
    horse_reg_num
    This way, you can simply join the vaccination and horse table using the reg_num field, and then calculate the time to the next vaccination to qualify it as "this month" or "next month".

    It is generally bad form to save time intervals (your month_interval field is an example). It would mean having to periodically update your database to change the interval values (e.g., when July comes around, you have to either use a trigger to decrement month_interval by 1 or use a cronjob) - better to store the actual dates and calculate on the fly.

  7. #7
    SitePoint Member
    Join Date
    Jun 2003
    Location
    Calif.
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    vacdate is the date the vaccination was given. All horses get these 'common' vaccinations forever, so I don't think there would be any need for horse_reg_num in the vaccines table?
    The month_interval field stores simply a number i.e. 2 meaning this vacc. will be given every 2 months (forever) and I have that month_interval in there because people like to know 'how often' rather than when is "horsey" due his next WNV vaccine?

    I suppose I can still calculate next due vaccination by the vacdate still, but then I'll have to do a query for every vaccine in the vaccines table then right? i.e.
    PHP Code:
    if ($vacid =1) {
    $iver=mysql_query("SELECT * from vaccinations WHERE (MONTH(CURRENT_DATE)-(MONTH(vacdate))=2");
    } elseif (
    $vacid 2) {
    $strng=mysql_query("SELECT * FROM vaccinations WHERE (MONTH(CURRENT_DATE)-MONTH(vacdate)=6")
    } elseif (
    $vacid 3) {
    etc. , etc.

    Is this the way to do it?


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
  •