Reply to Re: SQL relationships and identities

Your name:

Reply:


Posted by Erland Sommarskog on 12/08/05 00:10

Mike (peroos_7@hotmail.com) writes:
> Can anyone tell me what is best practices to define relationships between
> tables and to define their primary key?
>
> For example...
> One table is Orders and other is OrderDetails.
> For ease of accessing datas from table OrderDetails I have fields copied
> from Orders like year_of_order, order_number, client_id.

That is usually not a good idea. For this pair of tables I would
expect order_number to appear in both tables. Further more I would expect
order_number to be the primary in Orders, and I would expect it to be
the first colunm of a two-column key in OrderDetails. (The second
column would be a row number or a product id.)

But year_of_order or client_id should be in Orders only. There are cases
where it's right to denormalise and duplicate data, but the better
optimizers in modern RDBMS get, the less often there is a reasone for this.
And, in any case, before you start to denormalise, you need to learn
to normalise. (Unless you are into data-warehousing where they de-
normalize all day long.)

What is normalisation about? Basically about avoiding redudancy. The
Orders/OrderDetails is maybe a poor example, as a lot of order data is
static. But assume that the ClientID can change. If you have duplicated
the column, you need to update it both tables, and if you fail to do
that you have an anomaly.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация