|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
|
|||
|
|||
|
Help with database structure...
Hi everyone,
I've been stressing about this big time, but I can't seem to come up with a good solution. Here is my situation... I'm a reptile breeder. I need to keep track of clutches (this is a nickname for when a female lays her eggs). I need to record which male and female copulate and when (mate), I need to keep track of the females' ovulation (when she swells up when all of her follicles release) and also the females' post ovulation shed. Don't forget, I need to keep track of the clutch as well (the number of eggs laid, the sire (dad), the dam (mom), how many slugs were laid (infertile eggs), the incubation temperature, etc. Here is a sample database setup that I had come up with: Table: clutch_info id: (autonumber) sire_id: (an autonumber that is found elsewhere in the database) dam_id: (an autonumber that is found elsewhere in the database) incubation_temp: (number or text) date_laid: (int) number_fertile_laid: (int) number_total_laid: (int) (we can determine the number of slugs by subtracting) number_success_hatch: (total number of eggs that hatch) (I may have left something out, but this isn't where I am having my troubles) Here comes the tricky part... Normally, I would have the clutch_info.id mark the copulations, ovulations, etc. BUT I don't know the clutch_info.id UNTIL the eggs are laid. Because copulation, ovulation and post-ovulation shed happens before the eggs are laid, I have to find some other way to keep track of them. I was thinking something along the lines of: Table: copulation id date sire dam Table: ovulation id date dam So on, so fourth... I could have the a page display the copulations, but how do I link the copulations to that clutch? Well, the sire_id and the dam_id will be the same... That is how I could link them. Uh oh, here comes another issue. Copulation can occur multiple times. Why is this an issue? Breeding season starts in November, December of one year, and can continue into February or later of the next year... The reason I keep this in mind is because if the same MOM and DAD have TWO clutches for two CONSECUTIVE years, then I will have problems displaying them without a further link by date, but I can't just link by year. The only solution I came up with is to amend the tables like so and add a "season" field to certain tables... clutch_info season: example- 2004 copulation id, date, sire_id, dam_id, season (ex, 2004) The way I have laid out this post may be confusing, but I am stumped. I can always record the information on a card and wait 'till eggs are laid, but that can be confusing with 250 snakes... And and all help will be GREATLY appreciated... Best regards, Dustin J. Cox |
![]() |
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > Help with database structure... |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|