I thought I would change topic for the moment and talk about some important concepts to understand before creating a database.
I am off on vacation starting tomorrow so I probably won't be blogging. Have a wonderful 4th of July.
The core of good database design is planning. Before actually creating a database, you should have a good idea of the kind of data your database will contain and how that data should be broken down. It is recommended to plan your database out on paper before beginning the creation process.
When creating databases, there are rules that most designers follow which help them create consistent, efficient well thought-out databases. This set of rules is called normalization, which dictates that your database tables will eliminate inconsistencies and maximize efficiency. The goal of normalization is to reduce data to its simplest structure with minimum redundancy and maximum data integrity.
Some of the important goals of normalization are:
• All fields should be broken down so that data cannot be divided further. For example, the field Name could be broken down further to last name, middle name, and first name.
• Each table must have one unique key field called a primary key. That is to say, there must be one field that identifies a record and does not allow duplicates. An example of this would be a social security number or customer number.
• All fields must directly refer to the primary key. For instance, in a customer table, you would only include information related to that customer, such as name, address, etc. You would not include a field called “Product name” in a customer table.
• A field cannot contain more than one value.
Thus, a normalized database stores each piece of information in its own table, all fields are broken down to their lowest possible level and each piece of information can be referred to by the its primary key. This is an important concept and is the entire premise behind a relational database.