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