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