In this article we will discuss some of the ground rules that we need to keep in mind while we design a relational database.
Some of these rules are copied from various sites , some are from database books, some experience and many from various white papers.
These rules are my preffered rules.
As my experience is in SQL server side , so some of the rules may not be applicable if you are designing for Oracle/Db2 or any other web sites.
Table & Keys:
Rule 1: Each table should represent one and only one thing.For example, it might be a customer, an inventory item, or an invoice
Rule 2:The relational model dictates that each row in a table be unique. There should be no redundancy of data in the table, neither row-wise nor column- wise.
Rule 3:Each table can have only one primary key, even though several columns or combination of columns may contain unique values.
A little on Keys:
Rule 5:Whenever you think the database is going to increase to a very big size use BigInts. When considering database security and HIPPA regulations you may use GUID columns as primary key.
Rule 6: Never use real numbers as primary keys since they are inexact.
Rule 7: Never use a text column to be a primary key.Spelling and name changes may create problems. Also, performance wise it is better to use a int/bigint column as primary key.
Rule 8 : Try not to add a lot of columns to the table , the wider the table the slower the performance.
Rule 9 : When denormalizing have a good reason for denormalization.
Some of these rules are copied from various sites , some are from database books, some experience and many from various white papers.
These rules are my preffered rules.
As my experience is in SQL server side , so some of the rules may not be applicable if you are designing for Oracle/Db2 or any other web sites.
Table & Keys:
Rule 1: Each table should represent one and only one thing.For example, it might be a customer, an inventory item, or an invoice
Rule 2:The relational model dictates that each row in a table be unique. There should be no redundancy of data in the table, neither row-wise nor column- wise.
Rule 3:Each table can have only one primary key, even though several columns or combination of columns may contain unique values.
A little on Keys:
- All columns (or combination of columns) in a table with unique values are referred to as candidate keys, from which the primary key must be drawn.
- All other candidate key columns are referred to as alternate keys. Keys can be simple or composite.
- A simple key is a key made up of one column, whereas a composite key is made up of two or more columns.
- Primary keys become essential, however, when you start to create relationships that join together multiple tables in a database.
- A foreign key is a column in a table used to reference a primary key in another table
- Domains are simply pools of values from which columns are drawn.
Rule 5:Whenever you think the database is going to increase to a very big size use BigInts. When considering database security and HIPPA regulations you may use GUID columns as primary key.
Rule 6: Never use real numbers as primary keys since they are inexact.
Rule 7: Never use a text column to be a primary key.Spelling and name changes may create problems. Also, performance wise it is better to use a int/bigint column as primary key.
Rule 8 : Try not to add a lot of columns to the table , the wider the table the slower the performance.
Rule 9 : When denormalizing have a good reason for denormalization.
First fully
normalize the database (to Third Normal Form or higher) and then denormalize
only if it becomes necessary for reasons of performance.
Rule 10 : Try to keep Date columns as DateTime and not varchar.
Rule 11: Write SQL keyword in capital letters for readability purpose.
Rule 10 : Try to keep Date columns as DateTime and not varchar.
Rule 11: Write SQL keyword in capital letters for readability purpose.
Next we will look at Clusterd and non-clustered index requirements.
No comments:
Post a Comment