Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2013
    Posts
    4

    Designing a database for a charity.

    I'm not sure this is the right section but here it is.

    I'm creating a database for an equine charity, it's designed to store details of all the horses, the farrier, vet, physio treatment they have, location of horses on loan, and contact details for vets, farrier etc.

    So far I have come up with this design in mysql workbench, but I don't know how correct it is. I'm not very experienced with this kind of thing, I've created working mysql databses before for websites, but nothing as large as this.

    I would really appreciate any advice you guys could give me here, thanks.
    Attached Thumbnails Attached Thumbnails Untitled.jpg  

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    These are not comments about your entities, but your attributes...

    Do your fields really need to be named staff_staffid and horse_horseid? Seems a bit repetitive and redundant...

    Does Loaner.Title really need to be 45 characters in length?
    Similarly, the email field is too short. The maximum length of a valid email address is 255 characters.
    Repeat for most of your character fields..

    Phone numbers are not numbers. Numbers cannot have leading zeroes but my mobile number does



    Is Sex really a boolean? If Sex=True what does that mean? I don'
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2013
    Posts
    4
    Thanks for your advice, regarding sex, since there are only two genders I thought using a boolean would use less data, for example true can be considered male and false female.

    The horse_horseid thing is what the mysql program created when I told it to make a one to many relationship, is that not the correct convention?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I guess what GVee is rerferring to is not to repeat the table name in the definition of a column

    eg use IS instead of HorseID in table Horse. it helps to qualify the parent table when you use that column as a foreign key in other tables.

    there is nothing semantically wrong in what you have done, just that its looks ugly.

    on your current approach
    select my,column,list from Horse
    join workprogramme on workprogramme.horse_horseID = Horse.HorseID
    as opposed to
    select my,column,list from Horse
    join workprogramme on workprogramme.horseID = Horse.ID

    it easier to read makes more sense linguistically.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by Energize View Post
    since there are only two genders
    You might want to consult your show manuals on this! There are always three, in the US there can be five, and in Europe there can be more.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Aug 2013
    Posts
    4
    Yeah I suppose your right, it's just that they only have mares and geldings there, but it is more correct, and I've been advised that although the front end may interpret the boolean as male or female, the data stored in the db should be unambiguous.

    I've revised it as you can see, but still a couple of questions.

    Currently they have vet records, physio records, farrier records, worming records. I don't know how best to represent this, currently I have one treatment record where the type represents the type of record, but that doesn't seem very elegant to me?

    Also the same situation exists with contacts, they have vets, farriers etc as contact types, surely there is a better way to represent this than a type field?
    Attached Thumbnails Attached Thumbnails Untitled.jpg  

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Without understanding what your users need or want, all I can do is guess.

    Since I've done a fair number of livestock shows my head is littered with a bazillion details that probably won't matter to your users... Start from the reports that they need, use those to determine what you need to store, then work out how you want to store it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Aug 2013
    Posts
    4
    I think I've finished the initial database design now.

    Can someone please look over this ERD and tell me if everything checks out?

    Not entirely sure about the naming conventions, does "title" for example need to be named table_title in each individual table, in order to differentiate it from other instances of "title" or does it not matter since it's a foreign key?
    Attached Thumbnails Attached Thumbnails erd.jpg  
    Last edited by Energize; 10-11-13 at 12:02.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Have you worked through the requests that your users gave you?
    1. If you can't answer all of those requests, then the diagram needs more work.
    2. If you can answer all of those requests, then this diagram is sufficient.
    3. If you can answer all of those requests, and there are no entities or attributes that you didn't use in some request, then the answer is correct.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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