Relational databases: some simple rules (part 2)
This is the second part of the two parts article "Relational databases: some simple rules".
In the first part we have seen the ideas of tables and the way we can relate them.
And now... the conclusion!
Primary and foreign keys
We now need to move on a bit, but please keep in mind the example made in the first part of the article: two tables, with a relation for the state column.
The names table has the following columns: ID, name, surname, address, post code, city, state.
The states table has the following columns: ID, state.
In the persons table, the “state” column will contain only IDs related to the states table.
The ID column in the person table is a primary key. The ID in the state table is a primary key.
The “state” column in the persons table is a foreign key.
Quite easy, isn’t it?
And next? Queries
Following the above ideas, we can start to build our new database. Usually, we start from creating tables structures and then we fill them with records. But after that? How can we use the information stored in tables?
In the web thought, we have seen many ways of using data stored in a database. We can use different programming languages in order to query our data, however the query commands and the resulting recordset is what really matters.
We cannot here list all the commands available in each environment used to query database. It is important here to consider that the basic rules always apply. There are many query builder around the web, and many tutorials that explain how to query a database, however we basically need to point to a table – or a set of related table – and fetch the data we want.
Once we have the information, we can show it and interact with the records. In fact we have different kind of queries: we can select records if we need to show them (possibly in a ordered form); we can update a table, delete records or insert a new record. All those operations (among others) are made using queries. So, if tables are the core of a database and relations are the way tables work together, queries are what we need in order to interact with records contained in tables.
There are in fact other ways we can use when we need to work on records contained in tables. In SQL Server for example we have views and stored procedures. However for the purpose of this article we are not going to see them. If you’re interested, the web thought has many articles which can guide you on the use of SQL Server queries, stored procedure, functions etc (just use the search section, please).
As a general rule, building queries is an easy job. Creating an efficient query is far more difficult (specifically in complex queries). What I suggest is to create the query in the its simpliest form and then work on it in order to make it faster and more efficient.
Conclusion
This two parts article was conceived because a friend told me to do simple tutorials on different subjects. One of them was the idea behind a relational database.
Writing about it isn’t really easy and being precise and short is a real challenge. As you may know there are hundreds of books (from “for dummies” to “for experts”) about it, and I surely don’t have the conceit to be a real expert. I’ve been creating and using relational databases since around 2000. Nobody taught me what I know... or – to better say it – many people on the internet have taught me what I know today. The rest is basically experience. But surely if I could have found an article like the above, surely I would have understood some basic rules which should be always taken into account when designing a relational database. I hope you have found the two articles interesting enough.
As usual, please use the comments section below if you want to share your thoughts.
In the first part we have seen the ideas of tables and the way we can relate them.
And now... the conclusion!
Primary and foreign keys
We now need to move on a bit, but please keep in mind the example made in the first part of the article: two tables, with a relation for the state column.
The names table has the following columns: ID, name, surname, address, post code, city, state.
The states table has the following columns: ID, state.
In the persons table, the “state” column will contain only IDs related to the states table.
The ID column in the person table is a primary key. The ID in the state table is a primary key.
The “state” column in the persons table is a foreign key.
Quite easy, isn’t it?
And next? Queries
Following the above ideas, we can start to build our new database. Usually, we start from creating tables structures and then we fill them with records. But after that? How can we use the information stored in tables?
In the web thought, we have seen many ways of using data stored in a database. We can use different programming languages in order to query our data, however the query commands and the resulting recordset is what really matters.
We cannot here list all the commands available in each environment used to query database. It is important here to consider that the basic rules always apply. There are many query builder around the web, and many tutorials that explain how to query a database, however we basically need to point to a table – or a set of related table – and fetch the data we want.
Once we have the information, we can show it and interact with the records. In fact we have different kind of queries: we can select records if we need to show them (possibly in a ordered form); we can update a table, delete records or insert a new record. All those operations (among others) are made using queries. So, if tables are the core of a database and relations are the way tables work together, queries are what we need in order to interact with records contained in tables.
There are in fact other ways we can use when we need to work on records contained in tables. In SQL Server for example we have views and stored procedures. However for the purpose of this article we are not going to see them. If you’re interested, the web thought has many articles which can guide you on the use of SQL Server queries, stored procedure, functions etc (just use the search section, please).
As a general rule, building queries is an easy job. Creating an efficient query is far more difficult (specifically in complex queries). What I suggest is to create the query in the its simpliest form and then work on it in order to make it faster and more efficient.
Conclusion
This two parts article was conceived because a friend told me to do simple tutorials on different subjects. One of them was the idea behind a relational database.
Writing about it isn’t really easy and being precise and short is a real challenge. As you may know there are hundreds of books (from “for dummies” to “for experts”) about it, and I surely don’t have the conceit to be a real expert. I’ve been creating and using relational databases since around 2000. Nobody taught me what I know... or – to better say it – many people on the internet have taught me what I know today. The rest is basically experience. But surely if I could have found an article like the above, surely I would have understood some basic rules which should be always taken into account when designing a relational database. I hope you have found the two articles interesting enough.
As usual, please use the comments section below if you want to share your thoughts.