A good Information System must contain a perfect model and a proper database design. In database designing, building the data layer is the first and foremost thing. This indeed,the critical step in planning a new system to get it right requires a lot of attention and planning. A database is a model of piece of the real world and like any model this also disregards the complexity of the real thing.

Now the database system highly rely on the relational modal to store and retrieve data. This use the relation between the columns. In other words using relation model we can relate several values that belongs to same row in a table

Primary key is an attribute that uniquely identifies a row in a table. Using the primary key it is easy to handle the data in the row. Primary key helps to map relational data to an object-oriented model. It also helps to identify and manipulate each entity in the database. The main aim of the primary key is to uniquely identify an entity inside your database.

Natural Keys

The use of primary key is very familiar in the real world. For example the use of address to identify the building, account number to identify credit cards etc. In general if the data that you are modeling has a decent natural identifier then you must use it as primary key.

Selecting a primary key is a tricky process. There are a number of desirable characteristics to identify which is the primary key. In these some of the characteristics do not exist in natural identifier

  • Unique values: Primary key uniquely able to identify a row in a table.

  • Non-intelligent: The primary key with out having semantic meaning.

  • No change over time: The value of a primary key never change. If we change the value then we are altering the name of the entity

  • Numeric: It’s easy to handle unique values when they are numeric. Most database have their own methods to create Surrogate Keys.

For all the above rules there are exceptions. For example using the composite primary key, usually in joint tables giving many to many relation.


Surrogate Keys
When there is no primary key in the database model and there is need for primary key we can go for the Surrogate keys. Surrogate keys are simply numeric integers. In most case it is automatically generated by the database itself. The main problem with surrogate keys are they simply don’t have any semantic meaning. Surrogate key is also called as Synthetic key.

Conclusion
Foundation of many Information System made the database design most important part of a project, so it should be carefully planned and properly implemented. Selecting a primary key is also a critical task in modeling relational data. It is better to have a natural key to every table. If it is trivial you can design with Surrogate keys.