Join Simon Allardice for an in-depth discussion in this video Defining table relationships, part of Foundations of Programming: Databases.
- While any database begins with defining your tables, a vital next step is the ability to add relationships from one table to another, because much of your data is naturally connected. You're not trying to invent arbitrary relationships that don't exist, you're trying to describe what's already there. So, you might first define a Customer Table by itself, or an Order table, or a Product Table, but orders are placed by customers and they are orders for specific products.
Yes, information about an order is different from information about a customer, but they are related, so we need to formally describe the relationship between our tables, and the way we describe relationships is based on keys. Let's go through an example. I have a simple Customer Table here, and it has this column at the start, CustomerID, which is set up to automatically generate a unique number for each customer row as it's added to the database, so that's our primary key here, and one of the benefits of having that key is we can also use it elsewhere.
So, I have another simple table: Order. And each row here represents one order, so it has its own primary key, OrderID, which also is generating an automatic number. Now I'm going to imagine we have such a straightforward business that we only sell one product, so I just need to know a quantity and a total amount due, but, each order is an order for a particular customer and to process an order, you would need to know who this applies to.
So, how do we get that data? Well, we could add columns to the Order table and start copying across all the relevant data from the Customer Table into the Order Table whenever an order is placed, but; A: That would be duplication of data, something to avoid in general; and B: There's just no need to do this. Instead, what we do is we add a CustomerID column to our Order Table. We're taking the key to one row in the Customer Table and using it again in the Order Table to describe the connection between the two.
And we're not doing this casually. We actually tell the database management system that this is a formal relationship between these tables. So, every Order row has a CustomerID, but, whereas the Customer Table has a CustomerID that has to be unique, in Order Table it doesn't have to be unique. We could have the same CustomerID occur twice, or three times, or a dozen times, in this case, 367 is occurring twice, multiple orders by the same customer, because here, this number is not uniquely identifying a row in the Order Table, we have OrderID for that.
So, in the Customer Table, CustomerID is our primary key, but when it's being used in the Order Table, it is not a primary key, though it is still a key, it's referred to as a foreign key and is not unique. Now the benefit of having this relationship defined in the database is we can go either way, we could start at the customer row and then take that CustomerID and go get every order for that customer, or we can go from the order row, find a CustomerID, and find out which customer is associated with that particular order.
And when you have a relationship like this, it's what's defined as a one-to-many relationship, one customer can have many orders. In a database diagram you'll see these sketched out a couple of different ways, some people use the crow's foot symbol to show which way the relationship goes, in others you might see the one-to-infinity symbol, so, one customer has many orders, one category has many products, one department has many employees, one classroom has many students, and so on.
And it's very, very common to have these kind of relationships between your tables. Most tables in well-designed databases have relationships to one or more other tables. Now, bear in mind, when you describe a one-to-many relationship in the database, you aren't required to have many, a customer is not required to have many orders, customer might place only one order, might place no orders at all, but, the database internal rules support the idea that one customer can have many orders, one category can have many products, one department can have many employees, but, the opposite of these statements is not true, at least in the imaginary business I'm describing.
So, while each customer can have many orders, each order is for only one customer. I can't say that an order has many customers, it can't. A department has many employees, but an employee only has one department, a product only has one category. Now, as always, I'm trying to describe simple business examples that might be a little mundane, but are easier to grasp. If your actual business rules support a more flexible situation, then perhaps an order can involve multiple customers, or an employee can work for multiple departments at the same time.
You may have to define a different kind of relationship, instead of one-to-many, many-to-many, and that's up next.
- What is a database?
- Why do you need a database?
- Choosing primary keys
- Identifying columns and selecting data types
- Defining relationships: one-to-one, one-to-many, and many-to-many
- Understanding normalization
- Creating queries to create, insert, update, and delete data
- Understanding indexing and stored procedures
- Exploring your database options