On Feb 18, 2010, at 11:47 PM, Timothy Kinney wrote:
Here's the system I'm currently using that doesn't work too well...
Samurai (id, name)
Room (id, name, ForeignKey(Samurai), ForeignKey(Province))
Province (id, name)
Can someone suggest which relationships to use to get the results that
I want and explain why the relationship helps to accomplish that?
If a given samurai must be in a room, and a room can hold multiple samurai,
your models should instead be:
Samurai (id, name, ForeignKey(Room))
Room (id, name, ForeignKey(Province))
Province (id, name)
With this set of models, a single Samurai has an FK reference to the room in which he finds himself. The
Room, very similarly, has a reference to the Province in which it is located. A Province can hold many
Rooms, and a Room can hold many Samurai. In Django's ORM models, a given instance of the Room model (e.g.,
"room = Room.objects.get(pk=1)") will have a method that allows access to all Samurais residing in
that Room. So, "room.samurai_set" will give you the Room's Samurais. Similarly, for a given
province, you can retrieve all rooms via "province.room_set". Relevant Django docs are here:
http://docs.djangoproject.com/en/1.1/ref/models/relations/#ref-models-relations
In a view, assuming you are given a Samurai, you can get to the room name via
samurai.room.name <http://samurai.room.name>, and the province name by
samurai.room.province.name <http://samurai.room.province.name>. If, in your view,
you pass the Samurai object to the template, your template can perform that same
dereference and thus you can present to the user the room name and province name in your
HTML. Relevant Django docs here:
http://docs.djangoproject.com/en/1.1/topics/templates/#variables
For the canonical book on relational database theory, I'd suggest "Introduction
to Database Systems", by Chris Date. Not for the faint of heart, the
math-challenged, the practical or those short of time, but it will explain relational
theory in detail. Great detail. Ye be warned. ;)
I hope this helps, though the Date book probably won't, at least not
without long and sustained study. There are likely other books on RDBMSs that
folks can recommend. Other than that, my apologies, it's late and I no longer
have a functioning brain.
---Peter Herndon