If SQL could only handle one table at a time, it would be convenient, but not very powerful. The true power of SQL and relational databases lies in the fact that tables can be related to each other in useful ways, and SQL queries can gather data from multiple tables and display that data in a logical fashion.
The following example uses a banking scenario to show how multiple tables are useful.
Each customer of a bank might have more than 1 account. There is no real limit to the number of accounts a person might have. One customer might have a checking account, savings account, certificate of deposit, mortgage, credit card, and so on. Furthermore, a person might have multiple accounts of the same type. For example, a customer might have one savings account with retirement money and another savings account (of the same type) that has money for a college fund for their child. The "relationship" between a person and their accounts is described as a "one to many" relationship – one person can have many accounts.
Because there is no limit to the number of accounts a person can have, there is no way to design a record structure ahead of time that can handle all possible combinations of accounts. And if you created a record structure that held the maximum number of accounts that anyone actually owned, you would waste a lot of space. Suppose that you tried to build a single table that held all the information about one bank customer and their accounts. Your first draft might look like the following example:
Customer ID Number Customer Name Customer Address Checking Account #1 ID Checking Account #1 Balance CD #1 ID CD #1 Balance CD #2 ID CD #2 Balance ...
However, you do not know when to stop because there is no obvious limit to the number of accounts that each person might own.
Another solution is to create multiple records, one for each account, and duplicate the customer information for each account. So you have a table that looks like:
Customer Name Customer Address Account ID Account Balance
If a customer has more than one account, you can merely create a complete record for each account. This works reasonably well, but it means that every single account record holds all the information about the customer. This wastes storage space and also makes it harder to update the customer address if the customer moves (you might have to update the address in several places).
Relational databases, such as solidDB, are designed to solve this problem. You create one table for customers, and another table for accounts. (In a real bank, you would probably divide the accounts into multiple tables, too, with one table for checking accounts, another table for savings accounts, and so on) Then you create a "link" between the customer and each of their accounts. This allows you to waste very little space and yet still have the complete information available.
As mentioned earlier, in the composers example, every record should have a unique value that allows you to identify that record. The unique value is usually just an integer. You can use that unique integer to help "relate" a customer to their accounts. This is discussed in more detail in Getting started with SQL.
When you create an account for a customer, you store that customer ID number as part of the account information. Specifically, each row in the accounts table has a customer_id value, and that customer_id value matches the id of the customer who owns that account. Smith has customer_id value of 1, and so each of the Smith accounts has a 1 in the customer_id field. That means that you can find all of the Smith account records by doing the following actions:
1 Look up the Smith record in the customers table.
2 When you find the Smith record, look at value of the customer_id in that record. (In this case, the value is 1.)
3 Then, look up all accounts in the accounts table that have a value of 1 in the customer_id field.