|
|
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
Navigation:
[Reply to this message]
|