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!

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


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?

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.

how much effort would it have been to post the following:
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

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

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)

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.

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)

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.