Information Technology Freelancer

helloWorldTech

  • Facebook
  • Pinterest
  • Gill Metcalf

Relational Database Design – Phase 1: Rough


My previous post You pay for it - are you using it? Microsoft Access pointed out that there are circumstances where Microsoft Access was able to handle the system requirements in a far easier and more professional way than Microsoft Excel.



It is all very well to say that but how do you design and develop an Access database? If you just need to do a quick project, then to make using Access worthwhile the design and development process must be quick and easy. My next few posts should make this possible for you.

There are different types of databases including text, relational, NoSQL and object-oriented. Relational databases remain the most common type and can be used in most circumstances.

This post focuses on Relational Database Design.

I have put together an infographic

Making Sense of the Relational Database Structure which shows the relational database terms and how they interrelate. If you are no familiar with these terms, this is a useful resource to look at as we go along.


It is very important to get the design of the database right before creating it in a database tool like Access. There is a general computer principle that the further you progress in the development of a system, program, database, etc. the longer, harder and more expensive it is to make changes.

Yes, I know we all want to jump into Access or another database tool we are using, but it is well worth spending time on the design first. Again, when we first start out as a computer systems program, database, etc. developer the first few (or many – depending on how stubborn we are) times, we just jump straight into setting up the system. Hopefully, after a while, we realize how inefficient this is. I have certainly learnt this through experience.

I’ve actually found it quite surprising that once I have designed a good database and then implemented it in Access or another database tool, I can easily produce a new report or store further information as requested. Getting the basic database design correct is critical.

That said, we start with just a rough design and then fine-tune it until it becomes our final database design.

Step 1: What do I want my database for (probably will be my Database)


My example is hotel booking information. At the end of this post, you will be able to design a relational database for your own requirements.

Step 2: What items/categories/things do I need to store information about (probably will be my initial draft of my Tables)

I decided:

  • Rooms

  • Guests


Step 3: What detailed information do I need to store about each item/category/thing (start of identifying my Fields)

My initial thoughts were:

Rooms

  • Room Number

  • Number of Beds

  • Bath or Shower

Guests

  • Name of the person who booked (First Name and Surname)

  • Number of guests

  • Phone Number

  • Arrival date

  • Departure date

For each of our tables (Rooms and Guests), we need to have a way of uniquely identifying each record (the actual information that will be stored). In my hotel each room has a unique room number so when I think about room number 6 I know exactly which room I am referring to and there is only one room number 6.

In database terms this is known as a Primary Key. Some countries have social security number or identification number to uniquely identify each person in the country. These would be primary keys.

In the Guest table, it is possible there could be 2 different people with the same name who make a booking.

e.g. Jack Smith from the USA makes a booking and another Jack Smith from South Africa makes a booking. “Jack Smith” is not unique so can’t be a Primary Key. In this case, I need to include a Primary Key field in the Guest table. e.g. I add “Guest ID.”


My Guest table now has the following fields:

Guests

  • Guest ID

  • Name of the person who booked (First Name and Surname)

  • Number of guests

  • Phone Number

  • Arrival date

  • Departure date

At this stage, I have a very rough idea of what my database structure will be.

However, there is still work to be done to turn my rough structure into a Relational Database Structure. These steps include:

  1. Linking the tables using relationships (i.e. assigning guests to rooms)

  2. Database normalization (making sure we are only entering information once)

  3. Definition of the characteristics of each field (e.g. the Phone Number must have 12 numeric digits – including the International code for a USA number)

Please refer to my next post to continue with this process.

Remember I have put together an infographic Relational Database Structure which shows the relational database terms and how they interrelate. Click here to see it.

I would recommend that at this point you think of information that would be useful to you to store in a database and go through the steps above in your situation. This will have 2 benefits:

  1. You will start to design your own database.

  2. You will see if there is anything that you don’t understand. In that case please feel free to leave a comment below or email me and I will respond with assistance.

39 views0 comments

© 2020 - helloWorldTech.

Contact

Privacy Policy

Disclaimer

Terms and Conditions