Win a copy of Spring in Action (5th edition) this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

What foreign keys are necessary for this mySQL?  RSS feed

 
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm creating a summer camp database for summer camps to use in a new registration/cabin assignment service I'm going to be creating over the next year or two.  

I'm getting the database started now.

So far, one table I have has the camp ID number and camp name.  One table has the session information, such as the date and references the camp.

Now, for the cabins, I want to create a cabin that references the camp session, but do I need to also make a foreign key for the camp too, or is just matching it with the session ID enough that it won't cause messy results if about 20-30 camps are using the same database?
 
Saloon Keeper
Posts: 5038
134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if the cabin is associated with a session more than a camp, then it doesn't need an foreign key referencing the camp. The results wouldn't be messy, because you would use clauses in your SELECT statements to narrow down the results to just those in the right camp.
 
Rancher
Posts: 3748
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Isn't a cabin part of a camp?

Cabin assignment would be part of a session.
 
Kenneth Milota
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Isn't a cabin part of a camp?

Cabin assignment would be part of a session.



Cabin is more of a term for a group in this sense, not as in a physical building.   I guess the problem is more that one camper could be there for multiple sessions, and if you assign them to a cabin in one session, it will change it for the current session the way I'm doing it.  

Would I make a separate table for cabin assignment, and then a list of campers?  I'm still new to all of this.
 
Dave Tolls
Rancher
Posts: 3748
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah, OK.
Ignore that then.

I think then you'd be right in that you have a Session that has a bunch of Cabins, into which are assigned Campers.
Since a Camper can be in multiple Sessions (and hence multiple Cabins) then Cabin -> Camper would be many to many, so you would need a mapping table of Camper to Cabin.

So you are onthe right track with your last sentence, assuming I have understood the model.
 
Kenneth Milota
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay, so would this work?

 
Dave Tolls
Rancher
Posts: 3748
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The Camper doesn't need a CampId as that is a product of a Camper being assigned a Cabin, which is part of a Session, which is associated with a Camp.
Unless a Camper can only ever be associated with one Camp, of course.
 
Kenneth Milota
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:The Camper doesn't need a CampId as that is a product of a Camper being assigned a Cabin, which is part of a Session, which is associated with a Camp.
Unless a Camper can only ever be associated with one Camp, of course.



Well, multiple camps may be using the same database, but I think if another camp unaffiliated ends up having the same camper, they probably should have another entry due to confidentiality reasons.
 
Dave Tolls
Rancher
Posts: 3748
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, that sounds fair enough again.
 
All of life is a contant education - Eleanor Roosevelt. Tiny ad:
Download Free Java APIs to Work with Office Files and PDF
htttp://www.e-iceblue.com/free-apis.html
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!