Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

November 15, 2022

14:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.

Description

How should we design a database? You decide!
When creating database tables there are lots of decisions you need to make. For example, should you use:

Surrogate or natural primary keys?
Check constraints or lookup tables for status codes?
Store or calculate derived values?

Each choice affects the table structure and the queries you'll write to fetch data.

Watch this interactive session to see how these decisions affect your schema. See which options the audience voted for and hear the trade-offs discussion. Watch how the winning choices affect the schema and queries against.

Highlights include:

- 01:50 - The challenge design the schema for an international sporting league
- 05:00 - Surrogate or natural (ISO codes) keys for the countries tables?
- 09:25 - Use a check constraint or lookup table to restrict game statuses?
- 12:30 - Store the start times in UTC or a timestamp with time zone using the time zone for the home country
- 17:35 - Store the home/away teams in each game as columns or rows?
- 22:40 - For unplayed games, should the score for each team be null or not null with a default of zero?
- 29:30 - Store or calculate the win/draw/loss points for each team for completed games?
- 34:10 - Demo building the schema based on the audience votes for each question
- 39:05 - Query to find all the games for a country with results for completed games
- 44:15 - Show the fixtures in the next seven days
- 47:10 - Query to display the league
- 51:00 - Summary of pros and cons for each decision
- 58:30 - Consider the tradeoffs

Final script for demo https://livesql.oracle.com/apex/livesql/file/content_OCID7KNZF9F5FV1IEF2715XYH.h tml
Final script with opposite choices https://livesql.oracle.com/apex/livesql/file/content_OCIIILPL7X55470HMHI9JQCLN.h tml

Learn SQL for free http://bit.ly/learn-sql-free-dfd-f

Your Experts

  • #SELECTION#
    Chris Saxon

    Chris Saxon   

    Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.
    #MISC#
    #ACTIONS#