0

I'm in the process of building a web app that takes user input and stores it for retrieval and data manipulation. There are essentially 100-200 static fields that the user needs to input to create the Company model.

I see how I could break the Company model into multiple 1-to-1 Django models that map back the a Company such as:

  • Company General
  • Company Notes
  • Company Finacials
  • Company Scores

But why would I not create a single Company model with 200 fields?

Are there noticeable performance tradeoffs when trying to load a Query Set?

Daniel Johnson
  • 193
  • 2
  • 14

2 Answers2

1

In my opinion, it would be wise for your codebase to have multiple models related to each other. This will give you better scalability opportunities and easier navigation to your model fields. Also, when you want to make a custom serializer, or custom views that will deal with some of your fields, but not all, it would be ideal to not have to retrieve 100+ fields every time.

Daniel Odicho
  • 113
  • 3
  • Yea that makes sense. I am pretty curious what the performance tradeoff is. I'll have to do an experiment. Any suggestions on additional metrics to test? I agree with you that relational models are most likely the way to go. I'm just curious how a large non relational model would compare – Daniel Johnson Aug 15 '22 at 00:38
  • 1
    you can use django-debug-toolbar to test out query speeds, and other relevant metrics! – Daniel Odicho Aug 15 '22 at 01:47
0

Turns out I wasn't asking the right question. This is the questions I was asking. It's more a database question than a Django question I believe: Why use a 1-to-1 relationship in database design?

From the logical standpoint, a 1:1 relationship should always be merged into a single table.

On the other hand, there may be physical considerations for such "vertical partitioning" or "row splitting", especially if you know you'll access some columns more frequently or in different pattern than the others, for example:

You might want to cluster or partition the two "endpoint" tables of a 1:1 relationship differently. If your DBMS allows it, you might want to put them on different physical disks (e.g. more performance-critical on an SSD and the other on a cheap HDD). You have measured the effect on caching and you want to make sure the "hot" columns are kept in cache, without "cold" columns "polluting" it. You need a concurrency behavior (such as locking) that is "narrower" than the whole row. This is highly DBMS-specific. You need different security on different columns, but your DBMS does not support column-level permissions. Triggers are typically table-specific. While you can theoretically have just one table and have the trigger ignore the "wrong half" of the row, some databases may impose additional limits on what a trigger can and cannot do. For example, Oracle doesn't let you modify the so called "mutating" table from a row-level trigger - by having separate tables, only one of them may be mutating so you can still modify the other from your trigger (but there are other ways to work-around that). Databases are very good at manipulating the data, so I wouldn't split the table just for the update performance, unless you have performed the actual benchmarks on representative amounts of data and concluded the performance difference is actually there and significant enough (e.g. to offset the increased need for JOINing).

On the other hand, if you are talking about "1:0 or 1" (and not a true 1:1), this is a different question entirely, deserving a different answer...

Daniel Johnson
  • 193
  • 2
  • 14