When to use a joined table for comments? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

When to use a joined table for comments?

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2005-11-09 : 13:57:39

I have a table with almost a million rows, although it's quite slim with just ID, date, userID, JobID etc.

Now I want to the ability to add comments to some (probably less than 1%) of those lines.

The question is whether to create a separate comments table to join to it, or to create a comments field within the existing table? The comments field would obviously default to NULL, so wouldn't bloat the table unnecessarily if I add that field (right?), and would always be selected with the row from that table, so I'm leaning towards the latter alternative.

Any thoughts, words of warning?

Thanks
Mark

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-09 : 14:25:05

You could have a separate table, but a million rows isn't a lot. less than 1%, how would you know when you need to look for comments.

I'd probably go with 1 table.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-09 : 14:32:53

If you anticipate searching by the comment values, I'd put them in their own table. Otherwise, I'd go with Brett's suggestion.

generally speaking I'd avoid text/ntext if possible.

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-09 : 14:36:48

If most of your queries won't be pulling back comments, then I'd go with the two table approach. If you have a screen that only shows the PK and the comments, then I'd go with the two table approach.

Otherwise, the one table approach is fine.

I guess it also depends on which side of the database design fence you are on: to NULL or not to NULL. I believe David (byrmol) would go with the two table approach as he's on the no nulls side.

Tara Kizer

mark1504
Posting Yak Master

103 Posts

Posted - 2005-11-09 : 14:54:03

Thanks for the advice.

I'll go ahead with the one table approach, and if there's ever a problem I don't think the rewrite will be too difficult.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-09 : 15:54:51

Mark,

You didn't answer a basic question I asked.

How will you know when you want to look for Comments

quote:

If most of your queries won't be pulling back comments, then I'd go with the two table approach. If you have a screen that only shows the PK and the comments, then I'd go with the two table approach.

Otherwise, the one table approach is fine.

Tara Funny....you must be like my wife when we go park the car....use that spot....no wait....use that one....no wait....
[;-)]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-09 : 16:06:18

quote:

Tara Funny....you must be like my wife when we go park the car....use that spot....no wait....use that one....no wait....

I'm actually the opposite when it comes to parking. My husband (that sounds weird) once switched parking spots 3 times. I was about to slug him.

Tara Kizer

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-09 : 16:43:15

quote:

I guess it also depends on which side of the database design fence you are on: to NULL or not to NULL. I believe David (byrmol) would go with the two table approach as he's on the no nulls side.

If I went with the one table approach, I would make it NOT NULL with a default of an empty string.

Some developer: "But what about disk space!"
David: "Stick it up your arse!"

DavidM

A front-end is something that tries to violate a back-end.

mark1504
Posting Yak Master

103 Posts

Posted - 2005-11-09 : 17:31:13

quote:

how would you know when you need to look for comments?

Sorry to miss your question, but that wouldn't be an issue.

quote:

If I went with the one table approach, I would make it NOT NULL with a default of an empty string.

Um...Why?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-09 : 17:52:23

quote:

quote:

If I went with the one table approach, I would make it NOT NULL with a default of an empty string.

Um...Why?

Because he's on the side of the fence that designs databases without NULLs. It's a long debate.

Tara Kizer

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-09 : 17:53:20

>> Um...Why?
Because I don't like them.

DavidM

A front-end is something that tries to violate a back-end.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-09 : 18:21:57

David,

I would think the answer he was looking for is, that it really doesn't matter in regards to the amount of space used.

I would use nulls though

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mark1504
Posting Yak Master

103 Posts

Posted - 2005-11-09 : 20:05:12

quote:

>> Um...Why?
Because I don't like them.

What's not to like? ;-)

I thought using nulls was supposed to use less space than a zero length field, at least that's what the book said.

Well, I guess that saves a lot of ISNULLs in Select statements, and other workarounds in VB to prevent binding failures.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-09 : 22:45:37

>> Well, I guess that saves a lot of ISNULLs in Select statements, and other workarounds in VB to
>> prevent binding failures

Avoiding NULL is meant to be a theoretical debate!
How dare you disparage NULL with practical concerns! :-)

DavidM

A front-end is something that tries to violate a back-end.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-09 : 23:04:07

On a practical note, there's a large penalty for making a TEXT column not null, allocating an additional 8K to hold each empty string.

That's 7.6 GB of empty strings for 1,000,000 rows.

CODO ERGO SUM

mark1504
Posting Yak Master

103 Posts

Posted - 2005-11-09 : 23:11:41

quote:
Avoiding NULL is meant to be a theoretical debate!
How dare you disparage NULL with practical concerns! :-)

You're making a fuss over nothing! :-)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-11-09 : 23:23:08

>> That's 7.6 GB of empty strings for 1,000,000 rows.

That hurts!

Put another way... $15 dollars worth of storage plus a headache for storage/backup admins..

Does the new varchar(unlimited) in SQL 2005 cop this penalty?

DavidM

A front-end is something that tries to violate a back-end.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-11-09 : 23:32:52

Actually, in SQL 2000 they can store multiple text values on a single page, but I'm not sure how it would apply to empty strings (from BOL, text, ntext, and image Data):

quote:
In SQL Server 2000, individual text, ntext, and image pages are not limited to holding data for only one occurrence of a text, ntext, or image column. A text, ntext, or image page can hold data from multiple rows; the page can even have a mix of text, ntext, and image data.

You can also find some more info in Inside SQL Server 2000 by Kalen Delaney. There's also the text in row option.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources