This post makes it sound like this is all about OpenOffice Base, when in fact this is about tables in any relational databases. Hsqldb which is the default database for Base is in fact a relational database manager. It is what is called an embeddable database. That means that it can be completely non-standalone database. It can be embedded in another application like Base.
Relational databases have been around since the 60s. Since that time there has been a few rules designed to make the design of tables easier and less problematic. These rules are called the normal forms. I could spout them at you but that would be rather hard for you to read. So what I have done is synthesized my own set of rules that will help you to normalize your data and database tables.
Rule #1 – Make a Primary Key
Each table should have a primary key. A primary key is a field or group of fields that uniquely identifies each record in a table. The group of fields part can be ignored because it is used for very specific things, which is not in scope of this post. This rule makes sure that you don’t enter the same record twice. It also makes building relationships between tables that much easier.
Rule # 2 – Each field is Referring to the Primary Key
Each field in a table should be some information about the entity that the primary key refers to. If the information is about something else, it belongs in another table, and if we need that information together with our table we build a query to produce that result. This results in data that is clean and very much just about the entity that the primary key is about.
Rule #3 – Each field should have just one piece of information
This can be rewritten to say each field should be atomic. Not atomic in the nuclear sense, but atomic in the Greek sense. Atomic means indivisible, unable to be broken down further. There should be no Name field for People’s names. There should be a first name and last name field, because logically we can break down a name that way. A street address field really can’t be broken down any further logically, and so it is atomic. This allows us to use queries to assemble this atomic data in any way we wish
These are just a few rules that I have come up with that will help you to make tables that are somewhat normalized. If you follow these rules, then you should feel okay about showing people your database.
If you liked this article, please retweet it using the retweet button on the top right of the article. You can also vote the article up or down,
Related posts:
{ 1 trackback }