Django DB Design - Maintaining common and historical data

This is more a database design question than a specific Django one.

We have a small Django app to manage an annual conference.

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.

For these models, some of their fields are common from year to year, whilst others will vary.

For example, each AccomodationRoom 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.

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)

My initial approach was just to create an AccomodationR开发者_如何学Coom 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:

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

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.

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"),)

Or perhaps there's a better way of doing this that I haven't thought of? Open to suggestions here.

Cheers, Victor

A small modification to your first solution (I think it is better solution because it is more normalized than the second one)

class AccommodationRoom(models.Model):
    name = models.CharField(max_length=50)
    site = models.ForeignKey(Site)
    features = models.ManyToManyField(AccommodationFeature, null=True, blank=True)
    bed_availability = models.ForeignKey(BedAvailability)

class BedAvailability(models.Model):
    number_of_single_beds = models.IntegerField()
    number_of_double_beds = models.IntegerField()

class Conference(models.Model):
    year = models.CharField(max_length=4) # Example
    accommodation = models.ForeignKey(AccommodationRoom)

Using the Django admin backend, you can first create BedAvailability object with the specification of beds. Then you can create AccomodationRoom object and associate BedAvailability object with it. Then you can finally create a Conference object and associate AccommodationRoom object with this.

In case the you need a new set of BedAvailability for the same AccommodationRoom for another year, you can create a new BedAvailability object with new specifications and link it with AccommodationRoom. You would not need to re-enter AccommodationRoom data for the next conference even if the BedAvailability specifications change.