|
|
Posted by Mike on 12/08/05 14:46
Thnx for answers.
On Wed, 7 Dec 2005 22:10:53 +0000 (UTC), Erland Sommarskog wrote:
> 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.
Navigation:
[Reply to this message]
|