Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    5

    Am I going about this the right way?

    Hello everybody, this is my first real project I'm working on that I'm designing to be implemented by multiple users. The project designed for breeders of leopard geckos to be able to manage and display online their available geckos.

    Since I'm just getting started, I'm not looking to implement a whole lot of features and a complex database. However, I want to start it off right so that, if need be, I can add more and more features and fields to the database.

    To start off, I need a system to hold all of the information pertaining to each gecko. For now, I only want to deal with geckos for sale, on hold, or recently sold. I want to keep the following information about each gecko in the database:

    - ID Number
    - Name
    - Gender
    - Price
    - Status (For Sale/On Hold/Sold)
    - Notes (related to Status)
    - Details (related to each gecko)
    - Weight
    - Date of Weigh-in
    - Morph (basically, type of gecko)

    At first I was just going to be "lazy" about it and throw everything into one table. But because this isn't a project for my own personal use, a quick/temporary solution like that just will not do... especially since I plan on going have MySQL developer certification at some point.

    I'm definitely a novice when it comes to the design of an actual database. From stuff that I have read on database design, I have come up with the following idea of keeping everything organized. (See the attached Excel file.)

    As you see in the file, I have the morphs stored in a separate table for the time being. The geckos will be able to be of more than one morph... so from what I read about normalization, it would be ineffective to store the "morph" attribute in a column (or columns) in the animals table.

    There are lot of questions in my head, but I think that's a good enough start for now. I don't wanna confuse myself.

    Any and all help is greatly appreciated!
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by C14H22N2O
    As you see in the file...
    actually, no i don't

    i don't have any software capable of deciphering an XLSX file

    if you want us to take a look at a diagram (if this is what it is), use a GIF or JPG

    if it's text, paste it right into your post

    if it's formatted text, use [code]...[/code] tags


    Quote Originally Posted by C14H22N2O
    I have the morphs stored in a separate table for the time being. The geckos will be able to be of more than one morph... so from what I read about normalization, it would be ineffective to store the "morph" attribute in a column (or columns) in the animals table.
    that is correct

    to implement a many-to-many relationship, you need an intervening table between the tables of the two entities


    p.s. your username is a carbohydrate? which one?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    5
    I'm sorry, I forgot that the newest MS Office has different file formats, and I didn't save in a backwards-compatible form. Here's an updated .zip file with both the .xls and .xlsx files.

    EDIT: Ah, my username is Lidocaine.
    Attached Files Attached Files
    Last edited by C14H22N2O; 01-25-08 at 02:00.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how much effort would it have been to post the following:
    Code:
    animals
    ID  Name   Gender  Price Hatch Date  Details
     1  Jane   female   100  DATESTAMP   She's crazy.
     2  Bill   male     120  DATESTAMP   Sometimes he acts like he gets lost in his cage.
     3  Cerano male     120  DATESTAMP   Likes to dig.
     4  Foxxy  female   250  DATESTAMP   Possible Het RAPTOR
     5  Kohan  female   500  DATESTAMP   Possible Het Eclispe
    
    status
    geckoID  Status    Notes
     2       For Sale  
     1       For Sale  
     4       On Hold   AK
     5       Sold      LN
     3       On Hold   ZP
    
    morphs
    Name
    
    weight
    geckoID  Weight Update Date
      1        14   DATESTAMP
      5        25   DATESTAMP
      4        19   DATESTAMP
      2        22   DATESTAMP
      3        18   DATESTAMP
    that effort should have been yours, not mine

    you're lucky i have a warez version of a program that can read XLS -- not everyone is willing to fork over a couple hundred bucks to bill gates just for the privilege of running excel once in a while

    my point is that if there is effort involved in getting your information into a form that will help someone understand your design, the onus should be entirely on you to do it to make it easier for us

    now, returning to your sample data...

    a couple of those tables are in one-to-many relationships with the animals table, so the data should reflect this by showing more than one row for the same gecko

    your design is fine, except for the missing animals_morphs table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2008
    Posts
    5
    First I want to thank you for helping me out so readily. And secondly I do want to apologize. In hind site, I should have typed it out from the beginning; it was late and I probably should have waiting until (now) to properly post the database model in-line with the rest of the post body... but in my exhausted stupor, I found it easier to drag an .xls file into the already made .zip and attach it to my post.

    I do have another question, in terms of the weight table: as I currently have it set up, it only stores one weight/date for each gecko. Whenever the breeder updates the information to a more current weight, the date is also replaced. (Is that a one-to-one relationship?) Would it also be feasible to offer "weight tracking" as a feature--in that a new row is added with the updated weight and date as opposed to replacing the old row? (And wouldn't that be the one-to-many relationship?)

    I'm sorry if these questions seem really basic and retarded, but I go greatly appreciate all the help you (and anyone else) takes the time to give me.

    (Again, I'd like to apologize for not going through the motions and posting my database in-text; there's really no excuse as it's not like my project has a trillion tables.)
    Last edited by C14H22N2O; 01-25-08 at 23:41.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    weight tracking should be easy, just add a new row for the gecko with the new weight and date -- yes, it's a one-to-many relationship

    in fact, you must've done something weird to force a new weight to overwrite an existing one

    what dbms are you using for this? some make it harder than others to display the actual table layout, which is what i'd like to see for the weight table (to see how you managed to create that auto-update feature)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2008
    Posts
    5
    Oh I was gonna code the update function in PHP so that it would replace a table row instead of adding a new one. But after reading your response, it really hit me that that would be rather stupid... especially since weight tracking in and of itself could be a very nice feature for breeders to have.

    Honestly, the only tools I have at my disposal right now are PHP5, a MySQL5 database on my webserver, and PHPmyAdmin. (And a handful of PHP books and a MySQL certification book.) All of these tables and relationships that I've come up with I sketched out yesterday on graph paper before bringing everything here.

    Here's an updated database model with missing animals_morphs table and a weight-tracking weight table.

    Code:
    animals
    ID  Name   Gender  Price Hatch Date  Details
     1  Jane   female   100  DATESTAMP   She's crazy.
     2  Bill   male     120  DATESTAMP   Sometimes he acts like he gets lost...
     3  Cerano male     120  DATESTAMP   Likes to dig.
     4  Foxxy  female   250  DATESTAMP   Possible Het RAPTOR
     5  Kohan  female   500  DATESTAMP   Possible Het Eclispe
    
    status
    geckoID  Status    Notes
     2       For Sale  
     1       For Sale  
     4       On Hold   AK
     5       Sold      LN
     3       On Hold   ZP
    
    morphs
    Name
      Tremper Albino
      Bell Albino
      Rainwater Albino
      Mack Snow
      Mack Super Snow
      Tangerine
      RAPTOR
      APTOR
      Eclipse
      Enigma
      Patternless
      Hypomelaneasic
      Carrot Tail
    
    animals_morphs
    geckoID  morphName
      1     Tangerine
      1     Enigma
      2     Bell Albino
      3     Rainwater Albino
      5     Hypomelaneasic
      5     Tangerine
      5     Carrot Tail
      4     Mack Super Snow
    
    weight
    geckoID  Weight Update Date
      1        14   DATESTAMP1_02
      5        25   DATESTAMP5_02
      4        19   DATESTAMP4_02
      2        22   DATESTAMP2_02
      3        18   DATESTAMP3_04
      1        8    DATESTAMP1_01
      2        19   DATESTAMP2_01
      4        18   DATESTAMP4_01
      5        20   DATESTAMP5_01
      3        10   DATESTAMP3_01

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you want to track changes in status the same as changes in weight?

    if not, i would simply put the status and notes columns into the animals table

    interesting that you did not use an auto_increment for morphs -- and i am not suggesting that you should

    as for phpmyadmin, have a look at HeidiSQL, you might like it a whole lot more (i am not affiliated with them, just a very happy user)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2008
    Posts
    5
    Thanks, I'll definitely check HeidiSQL out!

    Nah, I don't think I'd need to track status changes. The only reason I thought it would be good to move the status to a separate table was I thought that if, in the future, I wanted to update the application to not only manage geckos for sale/hold/etc but to also keep track of breeder geckos and other ones part of a breeder's collection.

    In essence, every gecko in the gecko table wouldn't necessarily be for sale. Only the ones for sale would need a price and status. The other's might actually need a status (instead of For Sale, it would be Breeder or Collection). And the notes only pertain to the status... noticed on a lot of breeder pages they have the initial of the person who has the gecko on hold along with the Hold message.

    (Although, Price should also be in a separate table if I go by that logic, right?)

    Hmmm...

    EDIT: I did think about using an auto_increment for the morphs or a code like TRALB for Tremper Albino... but I though it just added something that didn't really need to be there. I do want to make it possible for breeders to edit the list of morphs themselves. The application will come with morph data already loaded into it, but a breeder and delete/modify/add data if they so choose.

    You know, as I wrote those previous 3 sentences I thought of a reason why I should use some sort of key, preferably an auto_increment: if they do, in fact, modify an existing entry (for example, the Rainwater morph is also called Las Vegas by some) not only would I have to update the morphs table, but also the animals_morphs table. Though I don't think there would be many instances where a user would notice a different is speed, but it would seem more efficient to only have to update one table instead of two, wouldn't it?

    I think I'm doing pretty good since I just started delving into real database development 2 days ago.
    Last edited by C14H22N2O; 01-26-08 at 15:49.

Posting Permissions

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