MS SQL Server - De-normalization

Author

De-normalization

robert@orisoft.co.uk

2006-10-24, 6:38 pm

I am designing a database which will be under heavy load so I am
considering the following to keep performance up:

I am thinking of using a human readable primary key (char) where
possible for foreign key tables and there relating foreign key fields.
The reason for this is that it would eliminate a join to the foreign
key table if I only wanted to return the human readable key that
represents that record, and not have to join using a numeric INDENTIY
field. An example would be a cities table, if joining the city field on
a given table to the city primary key on the foreign key table then it
would not be necessary to have a join if only city name was required
for the query?

Is this good practice?

Dan Guzman

2006-10-24, 6:38 pm

> Is this good practice?

The subject of natural vs. surrogate keys is often discussed here. My
personal preference is to use the natural key and introduce a surrogate key
only when needed to address issues with performance, maintainability or
laziness on the part of the data modeler ;-)

--
Hope this helps.

Dan Guzman
SQL Server MVP
<robert@orisoft.co.uk> wrote in message
news:1160996860.326150.37450@e3g2000cwe.googlegroups.com...
>I am designing a database which will be under heavy load so I am
> considering the following to keep performance up:
>
> I am thinking of using a human readable primary key (char) where
> possible for foreign key tables and there relating foreign key fields.
> The reason for this is that it would eliminate a join to the foreign
> key table if I only wanted to return the human readable key that
> represents that record, and not have to join using a numeric INDENTIY
> field. An example would be a cities table, if joining the city field on
> a given table to the city primary key on the foreign key table then it
> would not be necessary to have a join if only city name was required
> for the query?
>
> Is this good practice?
>

Sponsored Links