When to use django Foreign Key relationships VS a SQL View

Using Django
1

I’ve been playing with FK relationships in the Shell to familiarize myself with different things you can do. I have to say thank you to @KenWhitesell again for opening my eyes on the possibilities of these relationships.

But now I am kind of wondering when to leverage a SQL View over these relationships.

An example is - I have a test app that consists of a four tables - date_dim, calendar_event, event, and event_type table.

  • date_dim is your typical calendar table except I have the PK as the actual date
  • calendar_event is a list of all “events” - It is a list of dates and event_ids that tie to the event table as an FK.
  • event is a list of events - Holidays and a couple of special events - FK to event _type
  • event_type is the type of event - There are two types - Holiday and Special.

This isn’t anything I am building, this is just an example app I put together for me to test things against and learn against. The schema isn’t meant to be the greatest schema on earth, I just wanted tables that could tie into each other via FKs. The more the better just to see how far I could push things.

My dilemma is - I am not seeing when it makes more practical sense to use these relationships over SQL Views. So let’s say I have a goal of looking at today’s date, checking if it has an event, then determining the event_type.

To see if today has some kind of event, I can just look in calendar_event for today’s date and if it finds a record - then today had some kind of event.

Now I could just have a View that consists of three tables joining together 5 pieces of data that I plan to use and reuse to perform work. And I can just look up today’s date in that view and I have everything I need right there. I have the date, the holiday name, the event priority, event start, event end.

Or I could do something like (not meant to be exact - I’m riffing)

today = timezone.now()
e = Event.objects.get(date_today=today)

Then look at e.event_type.event_type which will give me either holiday or special. But I would have to write additional code and run addtional queries to get the rest of the data - date, holiday name, the event priority, event start, event end right.

Where as - if I used a view I could literally have one object / one line of code and just keep referencing it. Which makes way more sense in this specific situation. And that makes me wonder when and when it doesn’t make sense to use the relationships vs a SQL View.

e = v_view.objects.get(date_today=today)

Am I making any sense as to what I am asking…? Thanks.

2

First, I don’t think I’ve ever written an SQL view for use in Django.

One of the benefits of a framework such as Django is that it’s a complete environment for everything you want to do with the database. You’re not looking through multiple sources to find where data is coming from. I’ve found that overall, it aids comprehension of the system.
(Granted, the previous paragraph is a bit of a generalization. There are always edge cases and oddball situations where this doesn’t exactly apply, but it’s true enough to use as the common-case.)

You probably haven’t gotten deep enough into the features available within Djagno to see what you’re asking to do is just as easy - and “resuable” as views. But once you get into custom managers and querysets, you’ll see where this all fits.

3

Thanks for the insight.

For example, say I wanted to do this - how would I in the django ORM?

SELECT 
    c.date_actual,
    e.holiday_name,
    t.event_priority,
    s.event_start,
    s.event_end
FROM event_calendar c
LEFT JOIN event e ON c.event_id = e.event_id
LEFT JOIN event_type t ON e.event_type_id = t.event_type_id
LEFT JOIN event_startend s ON c.event_startend_id on s.event_startend_id
4

I’d need to see the schema for these tables to be more specific.

The basic query of EventCalendar.objects.all() automatically gives you access to all related objects. Using the select_related clause will convert this query into a set of joins.

5

I will test and play with it to see. Thanks.

Schema is basically this. Keep in mind - I made this up to test with. I built the models for it and have it in a local Postgres DB.

schema2
schema21180×292 15.4 KB

6

I’m sorry, that’s not readable to me. Do you have the create table statements for these tables? Or, failing that, the Django models?

7

I don’t want to post all of the CREATE SQL here. It’d be a lot to format and go through. It also doesn’t match 100% how I outlined here which would be confusing. Is this at all better? The table name is the top cell, and the cells below are the table columns. The colors correlate to the FK. So for example -

event

  • event_id
  • event_name
  • event_type_id
  • is_active

schema
schema1099×157 8.79 KB

8

Fair enough - if you can just provide the primary keys and foreign key references for these tables, that will be enough.
The images just aren’t large enough on my screen to read. They really don’t do me any good.

9

Ah, all right. See below. Calendar doesn’t have all of the columns, just enough to get the idea across. Thanks again for helping me understand. I feel like if I can see how I would do the above query in the ORM alone, it would help me see more why I don’t need a view or when I might.

calendar

  • date_actual
  • epoch
  • day_suffix
  • day_name

event_calendar

  • event_calendar_id
  • date_actual – FK to calendar
  • event_id – FK to event
  • event_startend_id – FK to event_startend

event

  • event_id
  • event_name
  • event_type_id – FK to event_type
  • is_active

event_startend

  • event_startend_id
  • start_time
  • end_time

event_type

  • event_type_id
  • event_type
  • event_priority
10

Ok, first I’m going to assume a “calendar_id” as a primary key for calendar. (It really does work out better that way. It’s fine to create a unique index on date_actual, but a date shouldn’t be a primary key.)
Your original query, shortened somewhat:

SELECT c.date_actual, e.holiday_name, t.event_priority, s.event_start, s.event_end
FROM event_calendar c
LEFT JOIN event e ON c.event_id = e.event_id
LEFT JOIN event_type t ON e.event_type_id = t.event_type_id
LEFT JOIN event_startend s ON c.event_startend_id on s.event_startend_id

Your query then becomes:
EventCalendar.objects.all().select_related('calendar_id', 'event_id', 'event_startend_id', 'event_id.event_type_id')

Now - you don’t need to specify this every time you’re going to perform this query. You can write a custom manager that adds the select_related to every query - or at least every query for which this behavior is desired.

11

I’m sorry. I didn’t change that when I posted to read, date_actual.

I had a date dim as an int as the PK originally, but I’ve been doing “experiments” and I changed the PK to the actual date just to test some things. This is a date dim table so the date in every record is unique. Which allowed me to use it as a PK. But again, this schema is meant as more of a playground.

I’m going to have to play around with your example a bit to understand it a bit better.

I just read an article on managers and it looks like they’re essentially a class and a way to return a set queryset again and again. Which would be the equivalent of a view in a sense.

Something I am very curious about is performance. I am going to run my view on the database end and analyze it. Then return the same data set through the ORM and analyze it as well. I am very curious on the difference in performance. Not because I feel any certain way - this is just getting insanely fascinating. Just how expansive the ORM is.

Thanks again for you time. Really appreciate it. And I appreciate that you genuinely answer what is being asked and delving into that specifically. It really helped me understand.

12

Depending upon your database engine, there may be a performance benefit of a view - the same that there would be in any comparison between a query submitted through the database interface vs a function call to code already on the server. After all - that’s all the ORM is doing. (Building an SQL query from the internal data structures and submitting it through the DB API.)

Percentage wise, my expectations are that you’ll find the difference to be miniscule compared to the time consumed processing the results in Python.

If there’s really a time-consuming part of this entire process, it’s the creation of the Python objects from the resultset returned from the database. If you’re returning a lot of data, you’ll notice it. But even then there are steps you can take to mitigate some of the worst of it.

13

Noted.

I actually turned my view into a model. I used the inspect db feature to generate the model for me and then just left managed=false and migrated it. I was then able to use my SQL View in my views.py. I’m more curious to see the difference if any. But you’re probably spot on about it being minuscule. A lot of this is just my curiosity. I should probably just build something already. Thanks again. I’m definitely going to dive in on custom managers more and read through some stuff after I’ve played around recreating the data set my view returns using the ORM. Thanks again. Have a good night.