Tables - Best practice - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Tables - Best practice

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2002-04-16 : 06:09:10

I'm wondering what experts think.

An order has contact-moments (when a customer calls)
a contactmoment is linked to one or more processes
A proces can be a change (for example a change of color).
A change has something to do with.... etc.

If I look at the Changes table and I want to find out what the Order-ID for that change is, I need to make a query with four joins to get to the Order-ID.

Of course I can store the Order-ID in the Change table, but that would be redudant information. An change can never switch orders. So the value once stored (Order-ID) will NEVER change.

Is it best practice to store the Order-ID also in the Change table for performance sakes?

Henri

Henri

~~~
Guilt is like a bag of bricks. All you gotta do is set it down...

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-16 : 20:13:13

quote:

I'm wondering what experts think.

I wonder that everyday...

but since none of them have replied yet, I'll tell you what I think.

For my part it would depend on the size of the tables in question, and how fast you need a response. If all your tables are of the order of less than 100 000 records each and you only need a sub 1 sec response - go the joins.

If you need a really fast response with a really BIG table, you might need to consider some other methods....

How big are your tables and how fast does your response need to be?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-16 : 20:21:46

Henri,

What you are asking can be simply stated...

Q) "Should I denormalise for performance?"
A) "No!"

Why?

The performance increase you get, will be offset by the additional "house keeping" that will be required to keep the data denormalised and true.

I can phrase it slightly differently...

Do you want a result quickly or do you want the CORRECT result?

Extra milliseconds are worth it IMHO.

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-16 : 20:25:43

Aw, come on David!

It is possible that in some bizarre circumstance it's the ONLY way to achieve the required result...you old stick-in-the-mud!

Next you'll be suggesting people should ALWAYS document their code!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-16 : 20:37:15

quote:

Next you'll be suggesting people should ALWAYS document their code!

You don't need to document a database (except for the Procs and triggers) because a database should be self describing. After all it is meant to be a "model of reality".

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-16 : 20:43:34

actually, that was sarcasm...

except the bit

quote:

It is possible that in some bizarre circumstance it's the ONLY way to achieve the required result

say cheese

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-16 : 20:52:48

Cheese

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-16 : 21:07:47

LOL

BTW henri - David here IS a guru - I'm only someone who posts a lot....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-16 : 21:49:26

quote:

David here IS a guru

Very flattering but I would say "Advanced User"..

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-16 : 21:56:31

Poor old henri - he's going to get back to this post - he won't even be able to find his questions!

How's the farm Dave?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-16 : 22:08:55

Not bad mate.

As always, need more rain heading into winter.

Trees are looking good (Lost a few in the dry spell though)

Bloody water pump did a seal the other day, it is only 2 years old!
So much for German engineering!

How is your little one going?

PS Will Graz get angry using SQL Team as an Instant Messenger?

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-16 : 22:15:41

quote:

How is your little one going?

I assume you mean my expansive 3 acres! Somehow, extremely expensive! H/W Service died the other day - $1200!!!

As for Graz, he's only dangerous if he hasn't been fed....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-16 : 22:28:41

quote:

H/W Service died the other day - $1200!!!

Ouch

DavidM

Tomorrow is the same day as Today was the day before.

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2002-04-17 : 02:25:35

LOL,

My mouth indeed figured a small 'wow' when I say my mailbox replies. Joy was in my mind and I expected a lot....

No worries, thx for the answer anyway.

BTW: I've been down under for a year and loved it. I wan't to leave Holland for some place warm, where can I buy your 'expensive' land? I bet it ain't AUD 400 per square METER!!

Henri

~~~
Guilt is like a bag of bricks. All you gotta do is set it down...

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-17 : 02:31:29

quote:

I bet it ain't AUD 400 per square METER!!

It is if you are stupid enough to buy land in Sydney at the moment.

If you want some nice warm places that are cheap..

-Any where above the Tropic of Capricorn
Pros: Tropical, isolated
Cons: Crocodiles, The Wet Season, Other nasty wild life

-Southern WA
Prop: Pristine Land, Awesome coast line, great surfing
Cons: Can get cold in Winter (0C), Stay away from the Olive and wine area (expensive)

-Northern NSW and South East Queensland
Pros: God's country
Cons: The Gold Coast!

DavidM

Tomorrow is the same day as Today was the day before.

Edited by - byrmol on 04/17/2002 02:32:30

Nazim
A custom title

1408 Posts

Posted - 2002-04-17 : 02:38:17

Me too

quote:

I'm only someone who posts a lot....

rrb, dont rub graz the wrong way. he feeds on titles (Ask Tim

)

quote:

As for Graz, he's only dangerous if he hasn't been fed....

--------------------------------------------------------------

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-17 : 02:50:18

[quote]
dont rub graz the wrong way
[quote]
Yep, my cat doesn't like that either

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-04-22 : 11:02:08

>> I'm wondering what experts think. <<

That it would help if you posted some DDL. Why aren't the actions taken classified by type and described in the order contact table?

CREATE TABLE Orders
(order_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE OrderContacts
(order_id INTEGER NOT NULL
REFERENCES Orders(order_id),
contact_nbr INTEGER NOT NULL,
contact_time DATETIME NOT NULL,
contact_action CHAR(10) NOT NULL
CHECK(contact_action IN ('change', ...)),
description VARCHAR(255) NOT NULL DEFAULT 'No Action Required',
status CHAR(10) NOT NULL
CHECK(status IN ('open','resolved', 'lawsuit', ...)),
...
PRIMARY KEY (order_id, contact_nbr));

--CELKO--
Joe Celko, SQL Guru

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2002-04-22 : 11:38:57

Dear sir Celko,

I'm honored that you replied. I read 'SQL for Smarties' and I understood not all. I can not upload any screenshots from here, so I have to do some in writing. My application is Dutch, so I have to translate.

Table: tblOrder
OrderID Primary INT,
CustomerID INT (Foreign Key)

Table: tblContactMoment
ContactMomentID Primary INT,
OrderID INT,

Table: tblProcesCategory
ProcesCategoryID Primary INT,
ProcesCategoryName VARCHAR (50),
ProcesTable VARCHAR (50) --> contains the table name with the proces-attributes

Table: tblFlowProces
FlowProcesID Primary INT
ContactMoment INT
ProcesCategory INT
ProcesAttribute INT --> Contains the ID of the table which is referenced in ProcesTable in the table ProcesCategory.

Table: tblService
ServiceID Primary INT
ServiceDate SmallDateTime
etc.

The relations are like this. 1 Order can have several ContactMoments (customers calling, fax-ing, emailing, etc.).
1 Contact moment can have several FlowProces-ses.
1 FlowProces has a category. The category has an table name which stores specific information about the proces.
tblService is one of those processes. If I have a ContactMomentID I have the OrderID and I can find my Service which is related to the OrderId. But every time I want to find an OrderID when I am looking at service, I need a lot of joins to have the OrderID and the ServiceID in one record.

My question is. Should I store the OrderID in the tblService for:

1) Easy programming
2) performance

The Model I use is far more complicated than stated here, But this is a basis question I'm wondering about for some time.

Thx for helping!!

Henri

~~~
Guilt is like a bag of bricks. All you gotta do is set it down...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources