Working with databases/Database design/Planning a database

From WikiEducator
Jump to: navigation, search
OtagoPoly Logo S.png

Planning a Database

When creating a database, it is very important that you give some thought to how you will implement your database. You will need to consider things like which pieces of information you want to store, who is going to be using your database, etc.

Planning is not just a single decision at the beginning about what is going to happen – planning is a cyclical process of Design ⇒ Implement ⇒ Evaluate that feeds back into design. This process is repeated until the brief has been met. For your assessment, we will not be expecting you to create the “perfect plan” first time - but we will want to see that you understand and can document the above process.

Decide the purpose of the database by looking at the brief (the list of everything that needs to be included in the database and everything that needs to be produced from the database)

What facts do you need to keep track of? These facts will be used as fields in your database. (A field can be considered a snippet of information about a thing. For instance, if you wanted to create records about customers for your business, then we could create fields that contained information about their address, phone number, money owed, etc.)
Create your database, using your plan as a guide. We will cover the details of “how to” later in the book.
Try entering trial data into your database. Does the database behave as you would expect from your plan? Do the results it provides make sense? If not, then have at look at your original design and make changes to your database. This is a very important step, as usually once a database is full of information, it can be almost impossible to change the basic structure of it, without the risk of losing some or all of the information contained in it. Re-evaluate before you hand your database over to your client.

Decoding a brief

Before we get into the actual “how to” of creating a database, we need to look at the exercise of interpreting a brief.

Usually you’ll be presented with a situation where the client you are creating a database for is not a computer expert. They might have a list of things they’d like to record but might be a bit vague on the specifics. Or they might be organized and have a list written down. It will be up to you to try and interpret what is being asked of you.

We are going to work though an example of interpreting a brief, and then you can work though an example on your own. You will not be creating a database at this point.

OP icon activity.gif


Example 1

Mr Mann is fulfilling his lifelong dream of running a second-hand bookshop. He has decided that he’s going to create a database of his stock so that he can see at a glance what he has in the shop, and he wants to record all the details of his books that he needs in order to identify and catalogue a book. He also wants to add items that are on order.

What are the facts that we will need to record? Details about books that are to be sold. These could be:

  • Title of book
  • Author
  • Year published.
  • Number of pages
  • Paperback or hardback
  • Price purchased for
  • Price sold for

Can you think of any other details we might need to store in the database?

From the above list we can see that there will be about 10 fields to create in our database table. If you’ve thought of anything else then there will be more fields.

Example 2

The Dr Fixit Medical Centre wants you to plan a database for them to help them manage their patients. They need to make a note of patient names, address and other possible contact details, date of birth and which doctor they see. They would also like to record prescription details and the cost of the last visit.'

On a sheet of paper, make a list of the relevant facts that should be recorded in this database. That is, which fields will be needed?

When you have done this, check out our model answer.

OP icon activity.gif


Please note: the following tutorials will open in a new window/tab. When you have finished the tutorial, simply close the window/tab and you'll return to this page.

Before you move on, we recommend you take a few minutes to work through these online tutorials:

  • Exploring an Access Database
  • Thinking about Database Design