Table with Foreign and running total

SQL Apprentice Question
sql server noob here. class Relational DB question - I wanted to see
if my thinking is right and bounce this very simple design question off
you guys.. In particular using ASP.NET as the client and wanted to know
if I should address this on the db side or the client/business layer
side.

3 tables:


Master:
ssn varchar (10) (PK)
phone nchar(10)


Detail
ssn varchar (10) FK ?
datetime datetime ?
amt numeric


Totals:
ssn varchar (10) FK
amt numeric (should hold a running total by ssn of detail.amt)


Whenever a new detail record is entered, of course the master key must
be there, I want to keep Totals.amt current by ssn. Three questions:


1. What's the best way to keep Totals current? A trigger? Call it
automatically using db dependendancy setup? would dotNet or ADO.net
need to know or do anything.


2. I have not tried, but I can set up a compound unique key for detail
that covers ssn and datetime right? and can ssn still be a foreign key
to Master? That did not seem trivial in sql server 2000.. at a high
level how?


3. I've know some look down on the idea of running totals living inside
the db, afterall Totals.amt can be calculated at any given time. I've
seen in other designs like this, Is this terribly unacceptable? Even if
a poor design, I'm still looking for how to maintain integrity for this
design. Also wonder, would it be terrible to maintain integrity with
client code?


If you have any code or samples that would be greatly appreciated.



Celko Answers
CREATE TABLE Callers
(ssn CHAR(9) NOT NULL PRIMARY KEY, --you got the size wrong!
phone_nbr CHAR(10) NOT NULL -- us only, fixed length?
);


CREATE TABLE CallLog
(ssn CHAR(9) NOT NULL
REFERENCES Callers(ssn)
ON UPDATE CASCADE,
call_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
call_amt DEICMAL (8,2) NOT NULL
PRIMARY KEY (ssn, call_time));


CREATE VIEW CallerAcctTotals (ssn, caller_amt_tot)
AS
SELECT C.ssn, COALESCE (SUM(L.caller_amt), 0.00)
FROM Callers AS C
LEFT OUTER JOIN
CallLog AS L
ON C.ssn = L.ssn
GROUP BY C.ssn;



>> Whenever a new detail record [sic] is entered, of course the master key must


be there, I want to keep Totals.amt current by ssn. <<

Rows are not records-- nothing like them at all. Youa re still
thinking in terms of files -- names like Master and Details really show
that mindset -- tape file terms!



>> 1. What's the best way to keep Totals current? A trigger? <<


Triggers? You mean proceudral file system code! No, use a VIEW that
will always be up to date when it is invoked, not an other file thinly
disguised as a table.


>> 2. I have not tried, but I can set up a compound unique key for detail that covers ssn and datetime right? <<


DATETIME is a reserved word and tooooo vague to be a data element name.
And, yes these two columns should be the key for the log of calls
made.


>> can ssn still be a foreign key to Callers? <<


See DDL for use of UNIQUE()


>> 3. I've know some look down on the idea of running totals living inside the db, afterall Totals.amt can be calculated at any given time. I've seen in other designs like this, Is this terribly unacceptable? <<


Might okay for a data warehouse, but not for a production DB.


>> Even if a poor design, I'm still looking for how to maintain integrity for this design. <<


Constantly firing triggers that will drag performance into the ground
and kill it when it gets to production size


>> Also wonder, would it be terrible to maintain integrity with client code? <<


YES. That defeats the whole purpose of RDBMS as the one central tool
for data integrity. The minute someone uses QA or another tool to get
around you app code, the game is over. Oh, and how did you plan on
being sure that ALL zillion app programs do integrity checks the same
way? or at all?

SQL is declarative and not procedural. Your whole approach is wrong.