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]

Re: Null & one more table

Hi

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

John

"A.V.C." <yhspl_software group@hotmail.c om> wrote in message
news:d28fa5d0.0 407012140.cefa4 3e@posting.goog le.com...[color=blue]
> 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.[/color]

[Erland Sommarskog]

Re: Null &amp; one more table

A.V.C. (yhspl_software group@hotmail.c om) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[A.V.C.]

Re: Null &amp; one more table

Thanks Erland Sommarskog & John Bell
I will use #1 approach..


Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns951A9C DFD7FCDYazorman @127.0.0.1>...[color=blue]
> A.V.C. (yhspl_software group@hotmail.c om) writes:[color=green]
> > 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.[/color]
>
> 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.[/color]