We are going to continue working with the users table that we've started with, but we are going to add a few tables. Imagine a system where you can create projects. And users can be added to these projects. So this could be some kind of productivity app or a project management solution, think of JIRA.
We are going to start with three tables. The first table is going to be a users table that contains all of the information about each user's account.
We are then going to have a table that is called projects. Each project will have data about the project and a foreign key that is the creator of the project. This is a situation where the database design depends a lot on the business rules and requirements of the application. Is it appropriate to have only one creator, or can it have multiple creators? We are going to design it with only one creator per project to increase simplicity.
The third table is going to be used to record what users are part of certain projects. This situation is a many to many relationship because we've decided that one user can be a part of multiple projects and an individual project can have multiple members working on it. Because this is a many to many relationship, it calls for an intermediary table, project_users.
First, we will draw out the user table. We will have a user_id, username, first_name, and last_name.
Now, this is our parent table, because it has no foreign keys. Now, this is our parent table, because it has no foreign keys. Other tables are going to be referencing this table, so they would be the children.
The project table will have a project_id, title, description, and creator. The column that needs to be a foreign key is the creator.
Let's move on to the next table and we'll get back to the foreign key of the project table. The other table was project_users. Knowing that this is an intermediary table, immediately we know that the first two columns are going to be foreign keys to the each of the other tables.
Now, let's ask the important questions about the foreign keys. Let's first start with the project table's user column. The first thing we need to ask is what column does it need to reference? Remember, the only options are the columns that are UNIQUE. Our candidates are user_id and username. For now, let's go with username as it makes things easier to work with. Once we go into learning about joins, we will talk about joining things by ID. Different people do it different ways, with the majority using only ID columns for primary and foreign keys, but it's important to be familiar with different ways of doing things. The important thing to remember is that keys should never change, so if we should only reference the username if a user's username will never change.
Should the foreign key be labeled UNIQUE? If yes, it means that a user can only create one project. I vote no.
Should the foreign key be labeled NOT NULL? If not, it means that a project can exist without a creator. I vote no.
Moving on to the next table, I think I'll have the columns reference the project's id and user's id, so we can get some experience referencing surrogate keys.
We can apply to these foreign keys the same questions we asked about the other foreign key, and I would encourage you to do so and really think about why. But I can tell you that we are not going to want them to be NOT NULL, but not UNIQUE.
Now that we have a pretty decent database design, we can proceed with creating our database.
HELP ME! http://www.patreon.com/calebcurry
Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter
More content: http://CalebCurry.com
Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)