Design with the best space allocation.

We are getting ready to setup the gov's death file on a sql server. I would
like thoughts on the table design to optimize space. This is the data spec's
https://dmf.ntis.gov/recordlayout.pdf
The master file doesn't have the first column. So here is where I was headed
for the main table. The column's with asterisk are possibly empty. The
file updates will be done based off SSN column.
CREATE TABLE [dbo].[tblDMFile] (
[SSN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
*[NameSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
*[MiddleName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
*[StatusCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeathDate] [smalldatetime] NULL ,
[BirthDate] [smalldatetime] NULL ,
*[ResidentCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
*[ZipLastResident] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
*[ZipLumpSumPaymt] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDMFile] WITH NOCHECK ADD
CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
(
[SSN]
) ON [PRIMARY]
GO
CREATE INDEX [IX_tblDMFile] ON [dbo].[tblDMFile]([LastName], [FirstName])
ON [PRIMARY]
GO
TIA
CDMy understanding (could be wrong) is that SSNs of deceased persons are
reused, (or can be resused) so, if that is in fact true, then your use of SS
N
as single column Primary Key might be an issue. If true, you could add
DeathDate as well, (make it non null) I would assume since this is Death
File, every record will have to have a Death Date?
CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
([DeathDate],[SSN]) ON [PRIMARY]
That will handle the issue...
Also, suggest you put DeathDate first in the Index, and make this the
Clustered Index (on DeatHDate, SSN), since this will ensure that new Inserts
will generally be added to new Pages on disk, not randomly distributed
throughout the physical storage... Putting SSN as first column in PK index,
means that records would be physically ordered on SSN, and new inserts will
be distributed among all the physical pages on disk, causing many page split
s
and rapid table and index fragmentation.
"CD" wrote:

> We are getting ready to setup the gov's death file on a sql server. I wou
ld
> like thoughts on the table design to optimize space. This is the data spec
's
> https://dmf.ntis.gov/recordlayout.pdf
> The master file doesn't have the first column. So here is where I was head
ed
> for the main table. The column's with asterisk are possibly empty. The
> file updates will be done based off SSN column.
> CREATE TABLE [dbo].[tblDMFile] (
> [SSN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> *[NameSuffix] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FirstName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> *[MiddleName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> *[StatusCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DeathDate] [smalldatetime] NULL ,
> [BirthDate] [smalldatetime] NULL ,
> *[ResidentCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> *[ZipLastResident] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> *[ZipLumpSumPaymt] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblDMFile] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
> (
> [SSN]
> ) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tblDMFile] ON [dbo].[tblDMFile]([LastName], [FirstName])
> ON [PRIMARY]
> GO
> TIA
> CD
>
>|||Thanks for the reply. That is a good point about SSN resuage(possibly)
1) To clarify the table schema is good for the best Least space usage
2) ALTER TABLE [dbo].[tblDMFile] WITH NOCHECK ADD
CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
([DeathDate], [SSN] ) ON [PRIMARY]
3) CREATE INDEX [IX_tblDMFile] ON [dbo].[tblDMFile]([DeathDate],
[LastName], [FirstName])
ON [PRIMARY]
I am guessing most of the searches will be off the SSN then probably least
likely LastName...
Thanks again.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:A19A88F8-A183-4116-98F2-24BF3666352E@.microsoft.com...
> My understanding (could be wrong) is that SSNs of deceased persons are
> reused, (or can be resused) so, if that is in fact true, then your use of
> SSN
> as single column Primary Key might be an issue. If true, you could add
> DeathDate as well, (make it non null) I would assume since this is Death
> File, every record will have to have a Death Date?
> CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
> ([DeathDate],[SSN]) ON [PRIMARY]
> That will handle the issue...
> Also, suggest you put DeathDate first in the Index, and make this the
> Clustered Index (on DeatHDate, SSN), since this will ensure that new
> Inserts
> will generally be added to new Pages on disk, not randomly distributed
> throughout the physical storage... Putting SSN as first column in PK
> index,
> means that records would be physically ordered on SSN, and new inserts
> will
> be distributed among all the physical pages on disk, causing many page
> splits
> and rapid table and index fragmentation.
>
> "CD" wrote:
>|||CD,
The only other suggestion I can make, related to minimizing disk storage,
which will only save you 8bytes per record, is based on my assumption that
you are not storing time data in either Birthdate or death date... Therefore
,
by using SmallDateTime, which uses 2bytes for date, and 2 bytes for time, yo
u
are wasting the 2 time bytes. One way avoid this is to use your own
propietary "Date" data type, based on SmallInt, where the value of the
smallInt
(which is from -2^15 (-32,768) through 2^15 - 1 (32,767) represents the
Date as number of days since some arbitrary Date. If you Cast a
smalldateTime to a SmallInt, you will get the same value based on 1 Jan 1900
,
If you want to use the same values as the 2 bytes in a SmallDateTime Value,
you must remember that the 2 date bytes in a smalldatetime represent integer
s
from 0 to 65535, (UNSIGNED 2-byte Integer), not from -32,768 to 32,767.
So, if you want to take this approach, first you need to decide what range
of dates you want to be able to represent... And to do this in 2 bytes, you
need to limit it to 65536 dates.. If you want to use the same range of dates
as SmallDateTime
(1 Jan 1900 - 6 Jun 2079)
Then the conversion from a SmalldateTime value (say it's in Variable @.SDT)
to the smallint you need to store in your table will be
Cast (Cast (@.SDT as Integer) - 32768 As SmallInt)
1) Cast SmallDateTime as Integer (it's in range 0 - 65535)
2) Subtract 32,768 (now it's in range - 32,768 to 32,7687)
3) Cast it as SmallInt (This not absoutely necessay as Insert into SmallInt
Column will auto convert an integer)
And the conversion from the smallint in the table (say Colname is
"DeathDt"), to the SmalldateTime value will be
Cast(DeathDt + 32768 As SmallDateTime)
Add 32,768 (to make it positive (in range from 0 - 65535) and then cast it
to smalldateTime
By the way, you can add Calculated columns to the table definition, based
exactly on that latter formula [ Cast(DeathDt + 32768 As SmallDateTime) ]
that will not increase on disk data storage, yet output the exact
SmallDateTime value exactly as you would if you stored it in the the table a
s
a SmallDateTime...
"CD" wrote:

> Thanks for the reply. That is a good point about SSN resuage(possibly)
> 1) To clarify the table schema is good for the best Least space usage
> 2) ALTER TABLE [dbo].[tblDMFile] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDMFile] PRIMARY KEY CLUSTERED
> ([DeathDate], [SSN] ) ON [PRIMARY]
> 3) CREATE INDEX [IX_tblDMFile] ON [dbo].[tblDMFile]([DeathDate],
> [LastName], [FirstName])
> ON [PRIMARY]
> I am guessing most of the searches will be off the SSN then probably least
> likely LastName...
> Thanks again.
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:A19A88F8-A183-4116-98F2-24BF3666352E@.microsoft.com...
>
>|||Never actually researched this one, Thanx Michael... Another urban legend
bites the dust...
"Michael C#" wrote:

> According to the Social Security Administration (www.ssa.gov), SSN's are N
OT
> re-assigned after someone dies. However, some people are not assigned SSN
's
> (Pennsylvania Amish spring immediately to mind), so SSN might be NULL for
> some folks, which could cause issues. I'm facing a similar issue with
> trying to identify people uniquely in a database right now myself.
> --Quote From SSA.GOV Website:
> Question:
> Are Social Security Numbers re-assigned after a person dies?
> Answer:
> No. We do not reassign a Social Security number (SSN) after the
> number holder's death. Even though we have issued over 415 million SSNs so
> far, and we assign about 5 and one-half million new numbers a year, the
> current numbering system will provide us with enough new numbers for sever
al
> generations into the future with no changes in the numbering system.
>
> --End Quote
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:A19A88F8-A183-4116-98F2-24BF3666352E@.microsoft.com...
>
>|||Sorry, not 8bytes per record, only 2 byes/record, total only 4 bytes saved..
.
"CBretana" wrote:
> CD,
> The only other suggestion I can make, related to minimizing disk storage,
> which will only save you 8bytes per record, is based on my assumption that
> you are not storing time data in either Birthdate or death date... Therefo
re,
> by using SmallDateTime, which uses 2bytes for date, and 2 bytes for time,
you
> are wasting the 2 time bytes. One way avoid this is to use your own
> propietary "Date" data type, based on SmallInt, where the value of the
> smallInt
> (which is from -2^15 (-32,768) through 2^15 - 1 (32,767) represents the
> Date as number of days since some arbitrary Date. If you Cast a
> smalldateTime to a SmallInt, you will get the same value based on 1 Jan 19
00,
> If you want to use the same values as the 2 bytes in a SmallDateTime Value
,
> you must remember that the 2 date bytes in a smalldatetime represent integ
ers
> from 0 to 65535, (UNSIGNED 2-byte Integer), not from -32,768 to 32,767.
> So, if you want to take this approach, first you need to decide what range
> of dates you want to be able to represent... And to do this in 2 bytes, yo
u
> need to limit it to 65536 dates.. If you want to use the same range of dat
es
> as SmallDateTime
> (1 Jan 1900 - 6 Jun 2079)
> Then the conversion from a SmalldateTime value (say it's in Variable @.SDT)
> to the smallint you need to store in your table will be
> Cast (Cast (@.SDT as Integer) - 32768 As SmallInt)
> 1) Cast SmallDateTime as Integer (it's in range 0 - 65535)
> 2) Subtract 32,768 (now it's in range - 32,768 to 32,7687)
> 3) Cast it as SmallInt (This not absoutely necessay as Insert into SmallIn
t
> Column will auto convert an integer)
>
> And the conversion from the smallint in the table (say Colname is
> "DeathDt"), to the SmalldateTime value will be
> Cast(DeathDt + 32768 As SmallDateTime)
> Add 32,768 (to make it positive (in range from 0 - 65535) and then cast i
t
> to smalldateTime
> By the way, you can add Calculated columns to the table definition, based
> exactly on that latter formula [ Cast(DeathDt + 32768 As SmallDateTime) ]
> that will not increase on disk data storage, yet output the exact
> SmallDateTime value exactly as you would if you stored it in the the table
as
> a SmallDateTime...
>
> "CD" wrote:
>|||Thanks for the advice,
The date is currently a problem in its current format (MM,DD,CC,YY)
01301980. I have the data loaded (dates are in varchar) and plan to run a
update to move the year to front the change the column type.
I have not test but hope this will work.
set Deathdate = Right(Deathdate, 4) + Left(Deathdate, 4)
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:17A3FFDD-9F3F-4266-9B28-9F57F729BC07@.microsoft.com...
> Sorry, not 8bytes per record, only 2 byes/record, total only 4 bytes
> saved...
> "CBretana" wrote:
>|||Yes that should work...
but cast or convert the result to smalldateTime as well...
Cast(Right(Deathdate, 4) + Left(Deathdate, 4) As SmallDateTime)
"CD" wrote:

> Thanks for the advice,
> The date is currently a problem in its current format (MM,DD,CC,YY)
> 01301980. I have the data loaded (dates are in varchar) and plan to run a
> update to move the year to front the change the column type.
> I have not test but hope this will work.
> set Deathdate = Right(Deathdate, 4) + Left(Deathdate, 4)
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:17A3FFDD-9F3F-4266-9B28-9F57F729BC07@.microsoft.com...
>
>|||On Wed, 9 Mar 2005 12:14:46 -0600, CD wrote:
(snip)
> The column's with asterisk are possibly empty.
(snip)
Hi CD,
Then why are the columns without asterisk not declared as NOT NULL?
* Prevents data corruption
* Since you want the best space allocation: would save you one byte per
row (up to 8 nullable columns take a one-byte bitmap to hold the
NULL/NOT NULL control bits; 9-16 nullable columns add a second byte to
the NULL/NOT NULL bitmap, etc.)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Design type question

Out of curosity I am asking this design type question because I am curious as to what thoughts you fine folks have on the subject matter.

I have seen many instances of a databse design where there are detail records that have a dollar value associated with them such as an invoice table which holds invoice records. I have noticed that no where is a total of the invoices stored, causing SQL to have to run through the records each time a total is needed.

In days gone by prior to SQL, data structures would be such that a total accumulator field in a data file somewhere would be updated as invoices were processed so that the system would not have to run through the individual records to derive the total of them.

I am familiar with normalization in terms of SQL as I have worked with SQL for years and of course I have work with non-SQL technologies before SQL came onto the scene. What I am curious to know is that is it such a bad thing in the SQL world to have something like an invoice total accumulator that constantly is updated as invoices are processed and queries can access this total accumulator directly rather than having to gather the individual invoice records and total them up first before presenting the total to the user?

If a total field is kept, you have the same data in two places. If you have the same data in two places, they can get out of sync.

Say I have three items at 5, 10 and 20 dollars and a total field of 35 dollars.

Sam, a student intern, edits an item and changes it from 5 to 7 dollars but doesn't recalculate the total field (because he's an intern and doesn't know).

Now the data is corrupt, the total field still has 35 dollars, but the real total is 37 dollars.

|||Actually I wasn't thinking in terms of a field that the user had access to thr the UI. Instead, a table for invoice id and invoice total, stored procedures would handle updating the invoice total in the table when the invoice itself from the invoice table was modified or new invoices added. Then when a total was needed for whatever reason the query could go right to the table and obtain the total rather than crunching the number.|||

It's a good question, but in the scenario you presented you are atucally causing much more harm than good. Here's why...

irasmith:

stored procedures would handle updating the invoice total in the table when the invoice itself from the invoice table was modified or new invoices added.

Now you are running that harsh code every time someone does an insert, or update... whereas if you just let SQL do the mathonly when it is being requested to be seen, it's much better. Also, as was mentioned above, it could get out of sync.

There is a place for storing, or "caching" the calculated value:

A friend of mine has just launched a HUGE project for the County, and in it there are certain bugeting calculations that take about 2 minutes to calculate. In this kind of invironment, caching (by storing the calculated value into a table) would not be so bad :P

That's my 2 cents :P

Peace,

|||I once worked on a project where once a day they ran a stored procedure that pulled data from another database and ran calculations on it, and then stored the information in a format that was easily read into reports, including precalculated totals. Of course the proc took about half an hour to run (don't ask) so there was no way you'd want to wait that long for a single invoice. As Nullable wrote, its pretty situational, but there are definately times you would want to 'cache' the data like this.|||

Thanks to all who have posted to this thread. Naturally I am not one to run out and just do something out of the norm, however, as I work more in the design area I am finding that sometimes you have to weigh things and accept a trade off in order to accomplish the main goal. It is good to know others have had similar type issues arise before and while never an easy choice we just have to look at the specific situation and go from there.

Design Thoughts - Booking application

We are currently looking into the development of an application for booking
resources and are researching the available options for recording and
tracking these bookings.
The possibilities we have presently for managing and tracking bookings are:
a) Store bookings in SQL db and code the calendar functionality, showing
existing bookings & availability
b) Interface with Exchange
c) Utilise a 3rd party product
As there may be 1000s of resources that users can book online, such as a
video library, I am not sure of the feasibility of the Exchange option
however I am open to all suggestions.
ThanksMurphy
I think due to where you have posted this you will mostly
get recommendations to use SQL Server.
It's probably down to size and performance. If you are
only going to have from a few hundred pieces of data to at
most a few thousand, then you don't need the power of a
database like SQL Server. Something like Access, Excell or
Lotus Notes can do the job (Don't really know about
exchange, but probably that too). If your data gets into
10,000's, 100,000's and millions of items of data then you
need to consider a 'full' strength database, like SQL
Server.
Hope this helps
John|||Thanks John,
So your advice would be to use a db to store booking information and then
code to ensure resources are not double booked etc
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:052601c3acec$d72f81a0$a401280a@.phx.gbl...
> Murphy
> I think due to where you have posted this you will mostly
> get recommendations to use SQL Server.
> It's probably down to size and performance. If you are
> only going to have from a few hundred pieces of data to at
> most a few thousand, then you don't need the power of a
> database like SQL Server. Something like Access, Excell or
> Lotus Notes can do the job (Don't really know about
> exchange, but probably that too). If your data gets into
> 10,000's, 100,000's and millions of items of data then you
> need to consider a 'full' strength database, like SQL
> Server.
> Hope this helps
> John|||Murphy
If you use a product like SQL Server you can use unique
indexes or constraints to not allow duplicate bookings to
be made. When you try to enter duplicate information you
will not be able to.
Regards
John|||RE/
>The possibilities we have presently for managing and tracking bookings are:
>a) Store bookings in SQL db and code the calendar functionality, showing
>existing bookings & availability
>b) Interface with Exchange
>c) Utilise a 3rd party product
>As there may be 1000s of resources that users can book online, such as a
>video library, I am not sure of the feasibility of the Exchange option
>however I am open to all suggestions.
Sounds like a bread-and-butter application...I'd expect to find lots of 3rd
party products available that would do 80% of what you want for 10% the cost of
writing an app.
--
PeteCresswell|||Now that sounds very promising...
Any suggestions on where I could start looking ?
"(Pete Cresswell)" <x@.y.z> wrote in message
news:cnpirvsn6btc53kqmmffi23ef41e1d3iko@.4ax.com...
> RE/
> >The possibilities we have presently for managing and tracking bookings
are:
> >a) Store bookings in SQL db and code the calendar functionality, showing
> >existing bookings & availability
> >b) Interface with Exchange
> >c) Utilise a 3rd party product
> >
> >As there may be 1000s of resources that users can book online, such as a
> >video library, I am not sure of the feasibility of the Exchange option
> >however I am open to all suggestions.
> Sounds like a bread-and-butter application...I'd expect to find lots of
3rd
> party products available that would do 80% of what you want for 10% the
cost of
> writing an app.
> --
> PeteCresswell|||"Murphy" <murphy@.murphy.com> wrote in message:
> Now that sounds very promising...
> Any suggestions on where I could start looking ?
http://www.swinc.com/erm for example.
--
Cheers,
Siegfried Weber|||Or if you need something that you can customize, but without the excellent
support that the swinc one comes with, take a look at
http://autoaccept-sink.sourceforge.net/.
- Dave
"Siegfried Weber" <siegfriedcw@.notmail.com> wrote in message
news:%239igEpZrDHA.2592@.TK2MSFTNGP10.phx.gbl...
> "Murphy" <murphy@.murphy.com> wrote in message:
> >
> > Now that sounds very promising...
> > Any suggestions on where I could start looking ?
> http://www.swinc.com/erm for example.
> --
> Cheers,
> Siegfried Weber
>|||Piling on... :)
Dave's right - his version offers more ability to get under the hood.
Depending on what the needed functionality is, we might be interested in
adding it to ERM, or might be willing to add it as a custom feature to ERM
as well.
--
========================================ERM (Exchange Resource Manager) Released
http://www.swinc.com/erm
========================================
"Dave Mills [MVP]" <dave@.dontSPAMme-exchange-mail.org> wrote in message
news:uLwrfBfrDHA.512@.tk2msftngp13.phx.gbl...
> Or if you need something that you can customize, but without the excellent
> support that the swinc one comes with, take a look at
> http://autoaccept-sink.sourceforge.net/.
> - Dave
> "Siegfried Weber" <siegfriedcw@.notmail.com> wrote in message
> news:%239igEpZrDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > "Murphy" <murphy@.murphy.com> wrote in message:
> > >
> > > Now that sounds very promising...
> > > Any suggestions on where I could start looking ?
> >
> > http://www.swinc.com/erm for example.
> >
> > --
> > Cheers,
> >
> > Siegfried Weber
> >
>