Creating a website – Part 5

Database Design

Previously we created our tables and I said we would look at a graphical representation – also called an Entity Relationship Diagram (ERD) – which will help us visualise the lists for our table columns. This is extremely helpful because it can show if a relationship (the connection between tables) has any errors. So far we have the following tables and their related columns:

USER

  • user_id
  • username
  • password
  • reg_date

PHOTO

  • photo_id
  • user_id
  • filename
  • description
  • keywords
  • active
  • up_date
  • up_vote
  • down_vote

Developing these into an Entity Relationship Diagram looks like figure 1.0.

Entity Relationship Diagram 1
figure 1.0

The line that connects the ‘user_id’ in the photo table to the ‘user_id’ in the user table is a one-to-many connector. In this instance this is explained in the following way (which I mentioned in the previous article):

  • A user can have many photos
  • A photo can only have one user

I have also used the terms ‘PK’ (Primary Key), and ‘FK’ (Foreign Key). The former is a an identifier used for each record to ensure that each one is unique and we don’t have duplicate rows. The latter is used to tell us which user the photo belongs to.

The next thing we need to look into is the keywords column. In the next article I will talk about why this is an issue and how to correct it.

« Creating a Website – Part 4Creating a Website – Part 6 »