MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community ForumsDatabasesMySQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
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  
Old February 4th, 2005, 09:49 PM
aduroris aduroris is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2005
Posts: 7 aduroris User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 18 m 29 sec
Reputation Power: 0
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

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMySQL Development > Help with database structure...


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway