Tables with relations

Tables with relationships

Sorry if you've always thought this, but a spreadsheet program like MS Excel is not a database application, even though a lot of people use such software for just that purpose. In a database, you store structured information by storing it in one or more tables.

Do you regularly store at a store that also gives out loyalty cards? Very often when you make a purchase, they will first ask you for your zip code. Below you will find out why this is so.

Let's start with the example of an address list. A simple address list most people would immediately keep in one table. It might then look like this:

Often such a simple database is not enough. Even in the case of the above example, it could be better. After all: as your address list grows, you may often need to enter the same municipality and zip code and the same street name. We could divide the above example into 3 tables in this way.

A table for the cities

We give the table the name tblCities

You notice that one column is named "ID." An "id" is a unique number. You can make the database automatically increment this number when you add a row. If you delete a row, not all the other numbers change. This may sound strange, but it is quite normal. You should think of it as a "sequence number." In cycling, not all riders have to change their sequence number either if someone drops out along the way.

As column names, we use English names and only "letters," no spaces.

A table of the street names

We give the table the name  tblStreets 

In addition, we create a separate table for street names. Each municipality has several streets, but one street is (generally) located in one municipality (of course, this is not always true). The table in this case contains 3 columns: an ID, the ID of the municipality in the other table, the street name.

The table of municipalities and the table of streets have an intimate relationship. You can now quickly "filter" data and search. For example: give me all the streets of "Tienen". In database terms, this amounts to the following: select all fields in the table "streets" where IDcity = 1.

Slightly more advanced, you can now say: give me all the streets in the municipality with zip code 3300. That's the zip code of Tienen.

A table of the contacts

We give the table the name tblClients 

The table for contacts can now be somewhat simplified. By connecting the 3 tables, you can find out in which municipality and in which street someone lives. After all: Jos Janssens lives in IDstreet 1. That ID can be found in the table with street names. That table contains the ID of the municipality in the table "cities".

This seems rather cumbersome, but ultimately allows you to store data in a much more structured and compact way. Moreover, this way all data are compliant and you avoid different notations and errors.

Advantages:

• You can create one-to-many relationships that way. 
• You avoid spelling errors. For example, if you have the name of the municipality manually entered for each new customer, spelling errors can crop up. When entering a new customer, you can select the zip code from a list. Then you can filter streets belonging to that zip code. 
• Your database stays "more compact" (Note: if you include a list of all streets and towns in your database, this will also take up some space.