By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,750 Members | 1,456 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,750 IT Pros & Developers. It's quick & easy.

Null & one more table

P: n/a
Hello,

I want to store two information (so 2 columns) for 2/3rd of the rows
that will be in a table
and only one information (1 column is suffecient) for 1/3rd of the
rows of the table.

ex:
jb_id, previous_jb_id -------- for 2/3rd of the rows
jb_id -------- for 1/3rd of the rows

What is better

#1) add both the column (jb_id, previous_jb_id ) in the table itself.
disadvantage:
By this for 1/3rd of the rows, previous_jb_id will be NULL

#2) create different table for storing previous_jb_id for required
rows only.
disadvantage:
1) more table---> more Joins & more table to update
2) more Joins --->slow
& more Joins ---> bit difficult to write "select" queries.

Thanks in advance.
waiting for reply.
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi

I am not sure why you think having a NULL column is a problem, they take up
little space?

John

"A.V.C." <yh*****************@hotmail.com> wrote in message
news:d2*************************@posting.google.co m...
Hello,

I want to store two information (so 2 columns) for 2/3rd of the rows
that will be in a table
and only one information (1 column is suffecient) for 1/3rd of the
rows of the table.

ex:
jb_id, previous_jb_id -------- for 2/3rd of the rows
jb_id -------- for 1/3rd of the rows

What is better

#1) add both the column (jb_id, previous_jb_id ) in the table itself.
disadvantage:
By this for 1/3rd of the rows, previous_jb_id will be NULL

#2) create different table for storing previous_jb_id for required
rows only.
disadvantage:
1) more table---> more Joins & more table to update
2) more Joins --->slow
& more Joins ---> bit difficult to write "select" queries.

Thanks in advance.
waiting for reply.

Jul 20 '05 #2

P: n/a
A.V.C. (yh*****************@hotmail.com) writes:
I want to store two information (so 2 columns) for 2/3rd of the rows
that will be in a table
and only one information (1 column is suffecient) for 1/3rd of the
rows of the table.

ex:
jb_id, previous_jb_id -------- for 2/3rd of the rows
jb_id -------- for 1/3rd of the rows

What is better

#1) add both the column (jb_id, previous_jb_id ) in the table itself.
disadvantage:
By this for 1/3rd of the rows, previous_jb_id will be NULL

#2) create different table for storing previous_jb_id for required
rows only.
disadvantage:
1) more table---> more Joins & more table to update
2) more Joins --->slow
& more Joins ---> bit difficult to write "select" queries.


For the case given, I vote for #1.

That does mean to say that #1 is always the best. Say that you had 20
columns in the table, and of these six would only apply to a subset of
the rows. (And all six apply to the same subset.) In this case a subtable
is more palatable, as it makes the main table easier to grasp.

Another situation is when you have a large table, and you have some columns
that apply only to a very small subset of rows. In this case you can save
space (and thus time) by moving these columns to a side table. Note that
if the columns are varchar columns that else are NULL, there is not much
to win. But for fixed-width columns NULL takes up the same space as a value.
Also, the overall size for the table matters. Moving out 24 bytes of 520 per
row may not be worth it, but 24 of 56 bytes can give a huge effect.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
Thanks Erland Sommarskog & John Bell
I will use #1 approach..
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
A.V.C. (yh*****************@hotmail.com) writes:
I want to store two information (so 2 columns) for 2/3rd of the rows
that will be in a table
and only one information (1 column is suffecient) for 1/3rd of the
rows of the table.

ex:
jb_id, previous_jb_id -------- for 2/3rd of the rows
jb_id -------- for 1/3rd of the rows

What is better

#1) add both the column (jb_id, previous_jb_id ) in the table itself.
disadvantage:
By this for 1/3rd of the rows, previous_jb_id will be NULL

#2) create different table for storing previous_jb_id for required
rows only.
disadvantage:
1) more table---> more Joins & more table to update
2) more Joins --->slow
& more Joins ---> bit difficult to write "select" queries.


For the case given, I vote for #1.

That does mean to say that #1 is always the best. Say that you had 20
columns in the table, and of these six would only apply to a subset of
the rows. (And all six apply to the same subset.) In this case a subtable
is more palatable, as it makes the main table easier to grasp.

Another situation is when you have a large table, and you have some columns
that apply only to a very small subset of rows. In this case you can save
space (and thus time) by moving these columns to a side table. Note that
if the columns are varchar columns that else are NULL, there is not much
to win. But for fixed-width columns NULL takes up the same space as a value.
Also, the overall size for the table matters. Moving out 24 bytes of 520 per
row may not be worth it, but 24 of 56 bytes can give a huge effect.

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.