StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODjango DB Design - Maintaining common and historical data
text
Body
copied!<p>This is more a database design question than a specific Django one.</p> <p>We have a small Django app to manage an annual conference.</p> <p>There are certain models that are common to each year of the conference. For example, workshops often repeat each year, and we often use the same rooms (seminar or accommodation rooms) as well.</p> <p>For these models, some of their fields are common from year to year, whilst others will vary.</p> <p>For example, each <code>AccomodationRoom</code> has a name, a building, and features which would be common from year to year. However, other things like the actual bed availability will vary from year to year.</p> <p>There is a requirement to preserve the historical data from year to year, but we also want to reduce redundant duplication if possible, and save having to retype it every year (e.g. the names of the rooms, their sites, and their features. Likewise for workshops)</p> <p>My initial approach was just to create an AccomodationRoom that stored the common data, then have for example a BedAvailability that stored the transient year-to-year information, and also provided the link to each year's conference. For example:</p> <pre><code>class AccommodationRoom(models.Model): name = models.CharField(max_length=50) site = models.ForeignKey(Site) features = models.ManyToManyField(AccommodationFeature, null=True, blank=True) class BedAvailability(models.Model): number_of_single_beds = models.IntegerField() number_of_double_beds = models.IntegerField() conference = models.ForeignKey(Conference) accommodation_room = models.ForeignKey(AccommodationRoom) class Conference(models.Model): year = models.CharField(max_length=4) # Example </code></pre> <p>However, another way would be simply to do away with the two models, and have a single AccomodationRoom model, which contained everything, link this directly to the Conference model, and then enforce uniqueness on AccomodationRoom.name and AccomodationRoom.Conference.</p> <pre><code>class AccommodationRoom(models.Model): name = models.CharField(max_length=50) site = models.ForeignKey(Site) features = models.ManyToManyField(AccommodationFeature, null=True, blank=True) conference = models.ForeignKey(Conference) number_of_single_beds = models.IntegerField() number_of_double_beds = models.IntegerField() class Meta: ordering = ['conference', 'name'] unique_together = (("name", "conference"),) </code></pre> <p>Or perhaps there's a better way of doing this that I haven't thought of? Open to suggestions here.</p> <p>Cheers, Victor</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload