SQL relationships and identities

[Mike]

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.

Now, is it better to link those tables on this three fields, because they
are already there or define new field in OrderDetails with name OrderID and
link it on ID field from table Orders?
In this case I will always access table Orders to check year of order,
order number or client id for records in OrderDetails.

Which one of these examples have better performace on server?

I'm also doubting is it good to define field Id in each table and use it
for relationships to other tables or link tables on fields like
Number_of_order.

Is it enough to define field "Id" set it as PK and Identity Seed 1 for
unique mark of record and preserving consistency of data.

Is it good practice to link tables on primary key from one table to other
column in other table?

Thnks.

[David Portas]

Mike wrote:

Can anyone tell me what is best practices to define relationships between
tables and to define their primary key?

I think you mean Foreign Keys, not "relationships". Foreign Keys
maintain referential integrity by ensuring that data is complete and
consistent in your tables.
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.

What does "ease of accessing data" mean? I think you are making a
fundamental error. Do you understand what Normalization is? There is a
set of well-founded principles for designing a database schema and you
don't seem to be familiar with them. I recommend you take a course or
study a book on design because there is far more material for you to
cover than we can sensibly try to convey in a newsgroup.

--
David Portas
SQL Server MVP
--

[Mike]

Do you have any book that you recomend?
On 7 Dec 2005 09:19:16 -0800, David Portas wrote:

Mike wrote:

Can anyone tell me what is best practices to define relationships between
tables and to define their primary key?

I think you mean Foreign Keys, not "relationships". Foreign Keys
maintain referential integrity by ensuring that data is complete and
consistent in your tables.

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.

What does "ease of accessing data" mean? I think you are making a
fundamental error. Do you understand what Normalization is? There is a
set of well-founded principles for designing a database schema and you
don't seem to be familiar with them. I recommend you take a course or
study a book on design because there is far more material for you to
cover than we can sensibly try to convey in a newsgroup.

[Erland Sommarskog]

Mike (pe******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mike]

Thnx for answers.

On Wed, 7 Dec 2005 22:10:53 +0000 (UTC), Erland Sommarskog wrote:

Mike (pe******@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.

[Alexander Kuznetsov]

>> 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.
<<

Erland,
I think an Indexed view may be a good alternative to a denormaled
table, having the best of both worlds.

[Erland Sommarskog]

Alexander Kuznetsov (AK************@hotmail.COM) writes:

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.
<<

I think an Indexed view may be a good alternative to a denormaled
table, having the best of both worlds.

Indexed views can sometimes be used, that is correct, but there are
plenty of restrictions with indexed views, so it may not always work
out.

And speaking of denormalisations: in our database there is one table
that holds the current positions (in stocks, options, and other instruments)
and then there is another one for the current cash holdings. Furthermore,
there are historic tables that gives you the end-of-day standings for
both positions and cash. So what's denormal with this? These tables are
just a sum of the transactions table, so they are completely redudant.

Indexed view? I don't think so. A transaction has a book-record day (when
it was registered), a trade date (when the transaction took place) a
settle date (when it was actually paid) and a value date (from when it
affects the interest). And for cash holdings we have the values for each
of all these four dates, so that is four different aggregations.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

is order_id unique? In other words, do YOU define the order, or does
the customer? If YOU, then would there ever be a reason to restart
numbering, consolidating, or otherwise restarting? Would you ever
"change" the order number for a specific order?

I'd say in general that you would be better off to define an order
using an identity key. This will assign it a number that no end user
will ever see. You never know what the future might bring.
For the "order" "row", you will have attributes. The date of the
initial order, hte "order year", the customer, etc.
You will also have a different table of "order items." Each row will
have ANOTHER unique identity key. It will also have the Order identity,
line item, part ordered, etc.

Then, build generic code you will use over and order that does a
"select order year, order number, orderid, line item, etc etc from
ORDERS left outer join ORDERDETAIL on Orders.orderid =
ORDERDETAIL.orderid where blah blah blah"