Creating a website – Part 4

Database Design

I previously talked about creating lists for what we require for our site and how messy our initial lists were. Before I get into improving these, I would recommend reading the link below. It covers database normalisation which is a method to organise your databases – making them simpler to use, improving the structure, and reducing redundancies.

TutorialsPoint – They offer thousands of online courses including, of course, database design and normalisation.

The above website goes into good detail of what normalisation is with explanations and examples so I won’t cover them here as I will just be repeating what are already excellent resources. Instead I will just improve the previous lists and convert them into tables so we can then proceed onto the next stage of our website. To help us remember, here is the original (messy!) lists that were created:

USER

Login details
  • Username
  • Email address
  • Password
  • Registered date
Photo details
  • Filename
  • Title
  • Description
  • Keywords
  • Active
  • Upload date

VISITOR

Login details
  • Username
  • Email address
  • Password
  • Registered date
Photo details
  • Filename
  • Up votes
  • Down votes

The first thing to note is that we have ‘user’ and ‘visitor’. Obviously there could be a user that is a visitor, and vice versa so these can be put into a single table. The second thing is the photo details, and again these can be put into a single table. So let’s do that now:

USER

  • user_id
  • username
  • password
  • reg_date

PHOTO

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

Now we have two tables to record users and photos. But why did I put user_id in the PHOTO table? Looking at the tables we can say:

  • A user (owner) can have many photos
  • A photo can only have one user (owner)

Based on this information, if we referred to the photo_id in the USER table, a new row will be created for each photo which will repeat the user data which is not what we want. So we put the user_id in the PHOTO table. This is because a photo can only have one user and the data will not be repeated.

In the next article we will give these tables a graphical representation to make it easier to understand.

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