Lesson 28: Joining tables using Multiple Joins.
Eliminate data redundancy.
One of the biggest elements of "good" database design is reducing data redundancy. This means eliminating any objects which aren't required or necessary.
Don't have a field for Date of Birth and another for Age for example. Don't hold the number of products sold by salesperson in one table and a list of every sale in another table. One element is redundant so get rid of it.
We can help achieve this by proper use of multiple tables and using foreign keys to join them.
How would that look in practice?
If we ran a fintech startup, we would have one table for Customers and another table for Accounts. Customers could have multiple accounts. Similarly, a single account could have more than one customer connected to it (like a joint mortgage or credit card). We would then have another table that shows the combinations of Customer and Account connections. Let's see it in an example.
We can write our SQL query to bring back the Customer Name, Account ID and Account Type for all customers and their connections to their accounts.
SELECTCustomers.CustomerName,Accounts.AccountID,Accounts.AccountTypeFROM CustomersINNER JOIN CustAccConnON Customers.CustomerID = CustAccConn.CustomerIDINNER JOIN AccountsON CustAccConn.AccountID = Accounts.AccountID;
As the years have went on, we have more and more Marvel movies to enjoy. And more of our favourite characters (and therefore actors) keep showing up in different movies.
Write a SQL query to bring together the movies and the actors who appeared in them - using the link table in the middle.