Null & one more table

[A.V.C.]

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.

[John Bell]

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.c om> wrote in message
news:d2******** *************** **@posting.goog le.com...

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.

[Erland Sommarskog]

A.V.C. (yh************ *****@hotmail.c om) 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[A.V.C.]

Thanks Erland Sommarskog & John Bell
I will use #1 approach..
Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...

A.V.C. (yh************ *****@hotmail.c om) 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.