0

Let's say I'm designing a database with books (using Django's ORM, but I'm interested in the general theory of database design here as well as Django-specific advice). This is how I've designed my database so far so that there aren't any useless fields in each model:

class Book(models.Model):
    author = models.ManyToManyField()

class PictureBook(Book):
    illustrator = models.ManyToManyField()

class MusicBook(Book):
    composer = models.ManyToManyField()

With this database design, I have an abstract model that has all universal fields stored in it, then sub-classes that have fields specific to them.

The problem is that as my project grows, I'm having to duplicate a lot of effort to handle each of the sub types rather than developing around one universal model. What I'm considering is reverting to something more like this:

class Book(models.Model):
     types = (("g", "General"), ("p", "Picture"), ("m", "Music"))
     type = models.CharField(max_length=10, choices=types)
     author = models.ManyToManyField()
     illustrator = models.ManyToManyField(blank=True)
     composer = models.ManyToManyField(blank=True)

Here I would just have one Book model, which has a limited-option field that has the user choose the sub-type of the book they are adding. The problem is that then picture books have composer fields and music books have illustrator fields, and even though I can leave them blank, it feels messy to have all these superfluous fields associated with each and every DB object.

On the other hand, this would make querying way easier, as I could get grouped results with one query, then filter based on the type attribute.

I'm not very experienced at database design. Is there a general consensus on which database design schema is preferable in this type of circumstance? I'm considering redesigning from the former to the latter style, but I'm curious on more experienced developer's thoughts on which will leave me with more long-term difficulties.

Danny
  • 408
  • 4
  • 16
  • 2
    You can also make an entity-attribute-value model (EAV): https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model – Willem Van Onsem Aug 03 '20 at 17:56
  • 2
    @WillemVanOnsem No. Just... no. – bbaird Aug 03 '20 at 18:13
  • @philipxy: well Django itself already handles that (as an ORM, that also migrates the database). So I think that is not part of the problem (Django makes a table for the parent and the child and uses a unique foreign key from the child to the parent). – Willem Van Onsem Aug 03 '20 at 19:22
  • There are many ways to choose tables when there are subtypes , as the link makes clear. "Inheritance" in the question & "polymorphism" are just special cases of subtyping, which the answers address. That fact that an ORM gives a particular way that it might or might not associate with inheritance or polymorphism doesn't stop other ways from being applicable. – philipxy Aug 03 '20 at 19:35
  • @philipxy: yes I fully agree :), but the way Django implements inheritance is through an "implicit" `OneToOneField` to the parent model(s). A `OneToOneFIeld` is basically a `ForeignKey` with a unique constraint. – Willem Van Onsem Aug 03 '20 at 19:43
  • "the way Django implements inheritance" is one way/idiom to implement one notion of inheritance & I just commented that that way existing doesn't stop you from using other designs/idioms to address subtyping. (Eg every time there is a FK there is subtyping.) – philipxy Aug 03 '20 at 19:50
  • @philipxy: well not entirely since Django for example does not allow composite primary keys, nor foreign keys with composite fields, etc. – Willem Van Onsem Aug 03 '20 at 20:01
  • Thanks for y'all's comments. Whoever suggested the now-linked post (I think it was @philipxy ? ) definitely linked to a good breakdown. I wouldn't say it answers my questions of *what I should do*, but it does break down the different solutions into pros and cons in a really good way, and if nothing else, shows me I'm not the first to run into this line of thought (and that there's no universal way to deal with it). – Danny Aug 04 '20 at 18:00
  • 1
    We cannot tell anyone what is "best" in an engineering situation unless they define it in enough detail & give enough relevant details that everyone would agree on a valuation. And unfortunately results are chaotically dependent on details. And even if that could be done it's too much for a question here. And you would need to explain where & why you were stuck evaluating that yourself. Such questions are too broad. Although nevertheless often faqs. [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461/266284) – philipxy Aug 04 '20 at 22:13
  • Thanks again. Just to be clear, I wasn't complaining about the link provided not telling me what I should do. I appreciated the breakdown and it helped me make my own decision. Cheers :) – Danny Aug 05 '20 at 02:16

0 Answers0