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.