Creating a website – Part 6

Database Design

So far we have designed two tables, created the relationship between them, and designed an ERD (Entity Relationship Diagram). Let’s look at that again:

Entity Relationship Diagram 1
figure 1.0

I mentioned that we would be looking into the ‘keywords’ column. So what problem do we have here? In its current design we could have a photo with multiple keywords. For example if the photo is of a tree in a field, the keywords could be: tree, landscape, field, nature.

In database design we want to avoid any columns that can hold multiple values – this is called normalisation – there are multiple levels of this to ensure our tables conform to certain standards. They are referred to as First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). More information on these can be found on the freecodecamp website.

To get around this we need to remove the ‘keywords’ column and create its own table which will contain (surprisingly) a list of all used keywords. However using just this table could create repeating keywords as there will likely be more than one photo using the same keyword. So we will need a junction table which will link the ‘photo’ table and the ‘keywords’ table. In figure 1.1 we can see these integrated into the ERD from figure 1.0.

Entity Relationship Diagram 2
figure 1.1

I have used the naming convention of using the two table titles for the junction table so we can instantly see which tables they relate to – in this case ‘photo_keyword’.

In the next article we will create the database in MySQL ready for use when we begin coding the Pic-a-day website.

Note: In the ERD, I have used VAR as the type for ‘password’ in the user table. This should be VARCHAR.

« Creating a Website – Part 5Creating a Website – Part 7 »