Django DB Design - Maintaining General and Historical Data

Question

This is more a database design issue than a specific Django.

We have a small Django application for managing the annual conference.

There are certain models that are common to each year of the conference. For example, seminars are often repeated every year, and we often use the same rooms (seminar or accommodation rooms).

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

For example, each AccomodationRoom has a name, building, and features that will be distributed from year to year. However, other things, such as the actual availability of the bed, will vary with the year.

There is a requirement to save historical data from year to year, but we also want to reduce duplicate duplication, if possible, and to keep the need to repeat it every year (for example, the names of rooms, their sites and their functions As well as for seminars)

My initial approach was to create an AccomodationRoom, which stores general data, and then, for example, BedAvailability, which stores information on transitional years per year, and also provided a link to the annual conference. For instance:

 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 to simply end the two models and have one AccomodationRoom model containing everything, link it directly to the conference model, and then apply uniqueness in 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 maybe there is a better way to do this that I have not thought about? Open offers here.

Cheers, Victor

Answer 1

A slight modification to your first solution (I think this is a better solution because it is more normal than the second)

 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 a BedAvailability object with a bed specification. You can then create an AccomodationRoom and associate a BedAvailability with it. Then you can finally create a conference object and associate the AccommodationRoom with it.

If you need a new BedAvailability set for the same LivingRoom for one more year, you can create a new BedAvailability with new specifications and associate it with LivingRoom. You will not need to re-enter LivingRoom data for the next conference, even if BedAvailability specifications change.