Prev: Custom Setup MS SQL Server 2008 R2
Next: Tester Position (QA),Halifax
From: wackyphill on 29 Jul 2010 08:26
I have a table that holds agreement information. It works well for 95%
of the agreements we record.

But there is a certain type of agreement that would require another 6
or so fields to capture info specific to that type of agreement.

My question is if its better to just add those 6 fields to the
existing agreement table knowing that the info is meaningless to many
of the agreement records or if its better to create another table w/ a
1:1 relationship w/ the original agreement table to extend it in the
case of these special types of agreements.

Neither option is all that attractive to me, but I wanted to know if
one was considered a better practice than the other when you have a
choice.

Thanks for any help.
From: Erland Sommarskog on 29 Jul 2010 10:57
wackyphill(a)yahoo.com (wackyphill(a)gmail.com) writes:
> I have a table that holds agreement information. It works well for 95%
> of the agreements we record.
>
> But there is a certain type of agreement that would require another 6
> or so fields to capture info specific to that type of agreement.
>
> My question is if its better to just add those 6 fields to the
> existing agreement table knowing that the info is meaningless to many
> of the agreement records or if its better to create another table w/ a
> 1:1 relationship w/ the original agreement table to extend it in the
> case of these special types of agreements.
>
> Neither option is all that attractive to me, but I wanted to know if
> one was considered a better practice than the other when you have a
> choice.

It's certainly a trade-off situation.

In the system I work with there is a table that holds contract notes.
Contract notes for bonds and other interest-related instruments requires
six extra columns which are meaningless for stocks. As in your case, the
bonds is just some 3-5% of this big table. Originally, the bond-specific
columns were in the table itself, but in order to preserve some space,
I decided at one point to put them in a table of its own. Which meant
many queries had to be rewritten and include an extra left join.

When I did this, the current SQL Server versions were SQL 2000 with SQL 2005
on the way in.

Now, in SQL 2008 there exists a different solution: I could mark these
columns as SPARSE, and these columns would not take up any space when they
are NULL. (The columns in questions are float and datetime values, so
unless SPARSE is used, they take up 8 bytes, NULL or NOT.)

I should add that there is a second advantage with using an extra table. If
there are constraints appliable to these columns, for instance if it is an
agreement of type X the columns A, B, and C must be NOT NULL, then this is a
lot easier to handle it there is a separate table. And in the same vein, if
it is a separate table, you can easier prevent that there suddenly is data
in these columns for other type of agreements.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: wackyphill on 5 Aug 2010 11:51
On Jul 29, 10:57 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> wackyph...(a)yahoo.com (wackyph...(a)gmail.com) writes:
> > I have a table that holds agreement information. It works well for 95%
> > of the agreements we record.
>
> > But there is a certain type of agreement that would require another 6
> > or so fields to capture info specific to that type of agreement.
>
> > My question is if its better to just add those 6 fields to the
> > existing agreement table knowing that the info is meaningless to many
> > of the agreement records or if its better to create another table w/ a
> > 1:1 relationship w/ the original agreement table to extend it in the
> > case of these special types of agreements.
>
> > Neither option is all that attractive to me, but I wanted to know if
> > one was considered a better practice than the other when you have a
> > choice.
>
> It's certainly a trade-off situation.
>
> In the system I work with there is a table that holds contract notes.
> Contract notes for bonds and other interest-related instruments requires
> six extra columns which are meaningless for stocks. As in your case, the
> bonds is just some 3-5% of this big table. Originally, the bond-specific
> columns were in the table itself, but in order to preserve some space,
> I decided at one point to put them in a table of its own. Which meant
> many queries had to be rewritten and include an extra left join.
>
> When I did this, the current SQL Server versions were SQL 2000 with SQL 2005
> on the way in.
>
> Now, in SQL 2008 there exists a different solution: I could mark these
> columns as SPARSE, and these columns would not take up any space when they
> are NULL. (The columns in questions are float and datetime values, so
> unless SPARSE is used, they take up 8 bytes, NULL or NOT.)
>
> I should add that there is a second advantage with using an extra table. If
> there are constraints appliable to these columns, for instance if it is an
> agreement of type X the columns A, B, and C must be NOT NULL, then this is a
> lot easier to handle it there is a separate table. And in the same vein, if
> it is a separate table, you can easier prevent that there suddenly is data
> in these columns for other type of agreements.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks for the input as always Erland.
 | 
Pages: 1
Prev: Custom Setup MS SQL Server 2008 R2
Next: Tester Position (QA),Halifax