Algorithm Question

SQL Apprentice Question
I have user table named TBL_USER which has userid and username fields.

I have another table named TBL_PRODUCT which need user info in it


Which way you offer me to follow .


1. Having Userid Field in TBL_Product
or
2. Having Username field in TBL_PRODUCT


to have relationship between 2 tables.


Could you explain which way i should follow and wht i should follow ?
You might give a web url ..



Celko Answers
First, stop using uppercase names that violate ISO-11179 rules. Those
prefixes are redundant, mess up the data dictionary.


>> I have user table named TBL_USER which has userid and username field [sic] <<


Columns are not fields. The source of your design problem is that you
do not know what a table, coluymn or row are. Let's try guess at the
proper DDL, since you did not bother to post anything:

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY
CHECK (<>,
user_name VARCHAR(35) NOT NULL,
etc);
.



>> I have another table named TBL_PRODUCT which need user info in it <<


NO! Why is a user attribute part of a set of products??

CREATE TABLE Products
(product_id INTEGER NOT NULL PRIMARY KEY
CHECK (<>,
product_name VARCHAR(35) NOT NULL,
etc.);
.



>> Which way you offer me to follow .. to have relationship between 2 tables. <<


Of course.

CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Products(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE, -- guess at the rules
user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON UPDATE CASCADE
ON DELETE CASCADE, -- guess at the rules
PRIMARY KEY (user_id, product_id),
etc.);



>> Could you explain which way I should follow and why i.. <<


Any book on basic data modeling will tell you. A table is not a file;
it represents a set of entities of the same kind and their attributes
or it models a relationship -- never both in one table.

SQL search returning duplicate values

SQL Apprentice Question
I am having some trouble. I am doing some db search tests before
moving from access to mysql/sql server. Before I get into the Full
Text Search battle.....

If I try to do the following and there are multiple stores who have the
same book, all values are returned, which make duplicate entries in the
results. How do I accomplish the following but get a single price
value? How do I get the lowest, highest, middle, n'th etc value?


SELECT DISTINCT tblBook.*, tblStore.Price
FROM tblBook INNER JOIN tblStore ON tblBook.ID = tblStore.BookID
WHERE (((tblBook.Description) Like '%value%'));


tblBook tblStore
======== ========
ID------------| ID
Value |------BookID
Description Price



Celko Answers

>> Any help would be greatly appreciated. <<


Why did you make the book price an attribute of a store instead of the
book??? Think about it.

Also, read something about ISO-11179 metadata rules, so you will stop
using things like "tblBook" (a scalar value because it is singular and
it belongs to a piece of furniture -- I would have modeled a set of
book titles in my schema). Why does the book_id attribute change names
from table to table? Did you know that the ISBN-13 is the standard
identifier for books?


In the real world, you would have several prices for a book (wholesale,
retail, discounted, etc.) and they would be with the book. The min and
max prices would be known and then you can use a weighted average to
get a "middle" value. That means you need a quantity at each price
point.


Your current design is too simple for your query. Perhaps you use a
discount scale based on volume, or classes like this:


CREATE TABLE BookSales -- plural name for a set!! no redundant prefixes
(isbn CHAR(13) NOT NULL PRIMARY KEY, -- industry standards
wholesale_price DECIMAL (8,4) NOT NULL,
wholesale_qty INTEGER DEFAULT 0 NOT NULL,
retail_price DECIMAL (8,4) NOT NULL,
retail_qty INTEGER DEFAULT 0 NOT NULL,
etc.);


Start over and get some help from someone who knows how to do the model.

Default IN

SQL Apprentice Question
I want to pass parameters to a sql query. For example:

WHERE ANS IN (" & MyString & ")


So if MyString = 'This', 'That', 'The Other'


It will populate the Where clause.


But I also want to be able to pass an empty string and have it return
all values for ANS.


Any ideas?


Celko Answers

>> I want to pass parameters to a sql query. For example: <<


This comes up all the time from Newbies who do not work with a compiled
language and have no idea what a parameter is. You will get a FAQ to
the standard kludges for this -- parsing routines without safety
checks, dynamic SQL that can crash, etc.

One answer is that T-SQL can handle up to 1024 parameters (REAL
parameters, not things parsed off a single string in procedural code)
and you will not need more than 100 in the real world. Suddenly, you
have all the power of the compiler to check data, to optimize, etc.


Another answer is to load a working table with a single column. The
bad news is that in the year 2006, SQL is missing basic parts of the
VALUES() clause that would make this very, very easy.

Using one stored procedure to update table created in another?

SQL Apprentice Question
I have a stored procedures which creates a new table and then runs a
specific query. The results of said query are then inserted into the
newly created table. Now, I have another stored procedure which
should, in theory, update that new table with extra data.


I thought I could just do an update in the second sp but I'm hitting
some problems. Mainly, it's telling me that there's an invalid column
name, error 207, although I copied and pasted the name so the spelling
should match. It tells me that "observation_report_id" is the invalid
column name.


If it;s not the spelling of the name that's wrong, then what else could
be it be? Is there something else I'm missing that could bring up the
207 error?


If I can't get this working, could I do it with parameters? How would
I go about passing the unique key for each row from sp1 to sp2?


Thanks for any help,
-M-


Code example:


CREATE PROCEDURE frm_CNE_BasicInfo_pt2
AS


/** run query and insert results **/
update tbl_CNE_BasicInfo
set mainname=[mainname],
heading=[heading],
title=[title],
where obsRepID=observation_report_id


Select obstable.n_number, obstable.taskname as taskname,
obstable.observation_report_id, obstable.stream_id, mainname.mainname,
heading.heading, title.title
from (select p.n_number, tt.name as taskname,
orr.observation_report_id, pp.stream_id
from
parent p, parentpast pp, pastor pr, observationreport orr, trequest tr,
ttype tt
where p.parent_id = pp.parent_id
and pp.pastor_id = pr.pastor_id
and p.record_status = 'A'
and p.parent_id = orr.parent_id
and p.parent_id = tr.parent_id
and orr.trequest_id = tr.trequest_id
and tr.ttype_id = tt.ttype_id
and pp.stream_id = tr.stream_id
)as obstable
left outer join
(select ao.observation_report_id, dt.name, ao.value as mainname
from
atomicobservation ao
inner join dtype dt
on dt.dtype_id=ao.dtype_id
where dt.name = 'mainname')
as mainname
on mainname.observation_report_id=obstable.observation_report_id


etc etc



Celko Answers

>> I have a stored procedure which creates a new table and then runs a specific query. The results of said query are then inserted into the newly created table. Now, I have another stored procedure which should, in theory, update that new table with extra data. <<


This is not how to write an RDBMS. The schema is supposed to be a data
model of some real world situation. Creating tables on the fly is like
elephants appearing out of the sky.

What you seem to be doing is mimicking a file system. Hang a scratch
tape, dump some data to it. In the next *procedural* step, update the
scratch tape. In declarative programming, we try to get the desired
results in one step.


Given column names like "heading" and "title", it looks like you are
formatting a report on the database instead of in your application.
File systems and 3GL programming languages blend data and application
code into the same module, but RDBMS is supposed to be a tiered
architecture.


You can probably kludge your way thru this, but I would re-think how
you code in SQL.

Multiple bit data type in a table

SQL Apprentice Question
We are going to have a table that store about 300 bit fields (answers to
about 300 yes/no question for a client). I am curious as to whether I need
to split them based on the size of each row. How are mulitiple bit fields
in a table stored in SQL server 2000? Also if each field allows null (or
does not allow), how would that change the size of each row?

Any insight, recommendation, advice would be appreciated.
Thanks



Celko Answers

>> a table that store about 300 bit fields [sic] (answers to about 300 yes/no question for a client). <<


Bits are a low-level assembly language things that are a bitch to do
anything high-level with -- liker stat analysis. Columns are nothing
like fields. You are still thinking in assembly language, not SQL.


>> Any insight, recommendation, advice would be appreciated. <<


Do not design questionaires this way. First of all, there are very few
independent yes/no questions. You are looking for dependencies in the
data; that is the whole point of gathering data.

There are unanswered questions -- people do that! There are
interdependent questions -- "Are you female? No. Have you had cancer of
the uterus? Yes" is clearly wrong and needs a N/A answer. This means a
decision table for the validation rules (you do have those, don't you?)
and flow of questions chart.


Frankly, you probably want to get a questionaire package like RaoSoft
and use it. But if you have to use SQL, then get a normalized schema
and use an encoding for the answers that can be expanded to several
values as needed. Consider multiple choice questions -- nobody like to
do 300 questions.

need help with insert into table from two other tables

SQL Apprentice Question
have a table with 3000 Client_id's.

I have another table with 56 different order types. The order types
primary key is two fields (search_id, item_id)


I need to insert into another table each client_id, along with each
unique instance (56 instances) of search_id, item_id.


So the new table would have 3000 (client_id's) x 56 (distinct order
types) = 168000 rows.


What would be the easiest to go about this?



Celko Answers
>> I have another table with 56 different order types. The order types primary key is two fields [sic] (search_id, item_id) I need to insert into another table each client_id, along with each unique instance (56 instances) of search_id, item_id. <<


The quick answer is to look up a CROSS JOIN. You can materialize it or
put it in a VIEW.

But a better question is why are you doing this? Does each row (NOT
field) actually represent a fact in the reality of the data model?
That is, does everyone really have all the order types? My guess would
be that you are printing out a form of some kind and want to show all
the options to the users to get them to check off what they really use.
Cross joins do not happen very often in the real world.

Question About Revenue

SQL Apprentice Question
I have a reports table in my database where i am storing revenue
collected for each particular month.

Table structure
ID Int(4)
Hotel Number char(6)
Month char(1)
Year char(4)


each row can be something like 1001, 1894, 1,2006


I need to write a query which should return text 'N/A' for months that dont
contain data. For example if march 2006 has no revenue then it should return
n/a.


Can someone please assist me?


Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.


>> I have a reports table in my database where i am storing revenue collected for each particular month. <<


Your narrative did not have a revenue column :) We will ignore the
fact that computing values and storing them in a table is a bad
programming practice for OLTP apps and should only be done with a data
warehouse app.

CREATE TABLE ComputableSummary
(hotel_nbr CHAR(6) NOT NULL
CHECK (??),
year_month CHAR(7) NOT NULL
CHECK (year_month LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]'),
hotel_revenue DECIMAL(12,2) NOT NULL);


This next one can be part of a general Calendar table


CREATE TABLE ReportPeriods
( cal_date DATETIME NOT NULL PRIMARY KEY,
year_month CHAR(7) NOT NULL
CHECK (year_month LIKE '[0-9][0-9][0-9][0-9]-[0-9][0-9]'),
etc.);



>> I need to write a query which should return text 'N/A' for months that don't contain data. <<


SELECT S.hotel_nbr, R.year_month, S.hotel_revenue
FROM ReportPeriods AS R
LEFT OUTER JOIN
ComputableSummary AS S
ON R.year_month = S.year_month
WHERE .. ;

And then following the basic rules of a tiered architecture, you would
do the display in the front end for NULL revenues. However, if you
want to be a bad programmer, kludge it with:


COALESCE (CAST(S.hotel_revenue AS CHAR(12)), 'N/A') AS revenus

Update Priority Field

SQL Apprentice Question
I am trying to create a priority field to organize a list of tasks.
The following 3 fields are being used: EventID (PK, int, not null),
Name (nvarchar(50), not null), pri_ss (int, null).
It would be nice if the code did several things at once:
1. Automitically add items to the list with the max pri_ss number +
1.
(i.e. if you have 50 prioritized items in the list and you add
another one, it adds it as pri_ss = 51)
2. If I change the priority of an item I would like the priority of
all the numbers below it have 1 added to their initial number. (i.e.
if i change item 25 to item 5, i want item 5 to become item 6, and item
6 to become item 7, and so on)
My environment is SQL Server 2000, and i am somewhat fimiliar with.
Any code from SQL experts would be a great help!

Celko Answers

Here is an old posting about manipulating such numberings:

Given a motorpool with numbered parking spaces, you want to move the
automobiles around.


CREATE TABLE Motorpool
(parking_space INTEGER NOT NULL PRIMARY KEY
CHECK (parking_space > 0),
vin CHAR(17) NOT NULL);


Re-arrange the display order based on the parking_space column:


CREATE PROCEDURE SwapVehicles (@old_parking_space INTEGER,
@new_parking_space INTEGER)
AS
UPDATE Motorpool
SET parking_space
= CASE parking_space
WHEN @old_parking_space
THEN @new_parking_space
ELSE parking_space + SIGN(@old_parking_space - @new_pos)
END
WHERE parking_space BETWEEN @old_parking_space AND @new_parking_space
OR parking_space BETWEEN @new_parking_space AND @old_parking_space;


When you want to drop a few rows, remember to close the gaps with this:


CREATE PROCEDURE CloseMotorpoolGaps()
AS
UPDATE Motorpool
SET parking_space
= (SELECT COUNT (M1.parking_space)
FROM Motorpool AS M1
WHERE M1.parking_space <= Motorpool.parking_space);

append results of a simple select to table without a cursor?

SQL Apprentice Question
I supect it can be done but I don't see it

scenario:


User has Items and the first user is the Default user


I want all new Users to have the same list of items as the default user


Select * from Users
right join Items
on User.UserID = Items.UserID
where User.LocalID = 0 -- This is the default user


the above gets me the list of x items for the default Customer


Is it possible to append this list to Items for the new User, without using
a cursor?


Thanks



Celko Answers


>> I want all new users to have the same list of items as the default user <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. ISO-11179 prefers that you use collective or plural names for
tables because they are sets, unless they really do model single
entites. Here is a skeleton of what you need:

CREATE TABLE Users
(user_id INTEGER DEFAULT 0 NOT NULL PRIMARY KEY
CHECK (user_id >= 0),
user_name VARCHAR(35) DEFAULT '{{DEFAULT}}' NOT NULL, --usps
standard length
etc.)


--set up the default user with little known trick
INSERT INTO Users(user_id, user_name, ..)
DEFAULT VALUES;


--table of items needed
CREATE TABLE Items
(item_nbr INTEGER NOT NULL PRIMARY KEY, -- needs standard code
item_name VARCHAR(35) NOT NULL,
etc.);


-- ownership is a relation so it has its own table!
CREATE TABLE Ownership
(user_id INTEGER NOT NULL
REFERENCES Users (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
item_nbr INTEGER NOT NULL
REFERENCES Items(item_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (user_id, item_name),
etc.);


-- Now you need to proc to add new users and get them a default package
to start
CREATE PROCEDURE AddNewGuy
(@new_user_id INTEGER, @new_user_name VARCHAR(35), etc.)
AS
BEGIN
INSERT INTO Users (user_id, user_name, etc.)
VALUES (@new_user_id, @new_user_name, etc.);


INSERT INTO Ownership
SELECT @new_user_id, O.item_nbr
FROM Ownership AS O
WHERE O.user_id = 0;
END;



>> Is it possible to append this list to Items for the new User, without using a cursor? <<


Of course. Why did you even think of using a cursor?

3 value logic. Why is SQL so special?

SQL Apprentice Question
3 value logic. Why is SQL so special?

what would be the consequences of NULL=NULL being true?



Celko Answers

>>, what would be the consequences of NULL=NULL being true?


NULL=NULL should not be true.
NULL=NULL should not be false.
NULL=NULL should not be UNKNOWN.
NULL=NULL should be NULL. <<

NULL is a missing *attribute* value; UNKNOWN is a *logical* value. The
first rule of NULLs is that they propagate. You can easily set up
contradictions that depend on the order evaluation when you have a
BOOLEAN data type. All SQLK data types must allow NULLs by definition.


NULL OR TRUE = NULL -- by definition
UNKNOWN OR TRUE = TRUE -- by definition


NULL AND TRUE = NULL -- by definition
UNKNOWN AND TRUE = UNKNOWN -- by definition


This is why we have the IS [NOT] [TRUE | FALSE |UNKNOWN]
predicate in SQL-92

Cursor Performance

SQL Apprentice Question
Cursor performance for a real world application.

I have a table that holds records for a stops along a bus route. Each
records has a number of people getting on, number of people getting
off, a spot check, and a current load column.
The spot check column is the to verify that the sensors on the bus are
working correctly; the most reliable number to be use in the is
SPOT_CHECK.


Table Structure as follows:


ID (identity column)
ROUTE (Description of the bus route)
ONS (# of people getting on)
OFFS (# of people getting off)
SPOT_CHECK (visual spot count of people on the bus)
LOAD (Calculated load on the bus)


ID ROUTE ONS OFFS SPOT_CHECK LOAD
1 AAAA 5 0 null
2 AAAA 0 0 null
3 AAAA 2 1 null
4 AAAA 0 2 5
5 AAAA 6 3 8
6 AAAA 0 5 null
7 AAAA 1 2 null
8 AAAA 0 1 null
9 AAAA 0 1 null
10 AAAA 0 0 null


Now, I want to calculate the load at each stop along the way.


Load = Previous stops load + current stop ONS - Current stop's OFFS if
SPOT_CHECK is null, otherwise LOAD = SPOT_CHECK


So the results of the above table will be as follows:


ID ROUTE ONS OFFS SPOT_CHECK LOAD
1 AAAA 5 0 null 5
2 AAAA 0 0 null 5
3 AAAA 2 1 null 6
4 AAAA 0 2 5 5
5 AAAA 6 3 10 10
6 AAAA 0 5 null 5
7 AAAA 1 2 4 4
8 AAAA 0 1 null 3
9 AAAA 0 1 2 2
10 AAAA 0 2 null 0


Current programming, we using a cursor and seeing much much slower
performance than in our Oracle app. Does anyone see a way of doing
this load calculation without using a cursor and improving performance?


I can't do simple sums of ons and offs to calculate load, because of
the SPOT CHECK column.


Celko Answers
>> I have a table that holds records [sic] for a stops along a bus route. <<


Please post DDL and do not confuse records and rows.


>> records [sic] has a number of people getting on, number of people getting off, a spot check, and a current load column. <<


The current load is a computed column and we do not store such
computations in a good schema design.


>> The spot check column is the to verify that the sensors on the bus are working correctly; the most reliable number to be use in the is spot_check<<


Why did you use a vague, magical, non-relational, universal "id" column
instead of a relational key? The stops are a known attribute of a
route, but you left them out of the data model in spite of talking
about them.

CREATE TABLE BusTraffic
(route_nbr INTEGER NOT NULL ,
stop_nbr INTEGER NOT NULL,
bus_nbr INTEGER NOT NULL
REFERENCES Buses(bus_nbr),
PRIMARY KEY (route_nbr, stop_nbr),
boarding_cnt INTEGER DEFAULT 0 NOT NULL,
disembark_cnt INTEGER DEFAULT 0 NOT NULL,
spotcheck_cnt INTEGER -- null means not done
);


other stuff not shown might be ..


CREATE TABLE Buses
(bus_nbr INTEGER NOT NULL PRIMARY KEY,
bus_capacity INTEGER NOT NULL,
etc.);



>> Now, I want to calculate the load at each stop along the way... I can't do simple sums of ons and offs to calculate load, because of the SPOT CHECK column. <<


Probably something like this

SELECT T2.route_nbr, T2.stop_nbr,
SUM (CASE WHEN spotcheck_cnt IS NULL
THEN (boarding_cnt - disembark_cnt)
ELSE spotcheck_cnt END) AS bus_load,
(CASE WHEN spotcheck_cnt IS NULL
THEN 'spot checked'
ELSE 'computed' END) AS verification
FROM BusTraffic AS T1, BusTraffic AS T2
WHERE T1.route_nbr = T2.route_nbr
AND T1.stop_nbr <= T2.stop_nbr;


If you can use the OLAP SUM() OVER() functions in SQL, this will be
easier and faster than a self-join.

scramble ssn with sql server

SQL Apprentice Question
I'm trying to scramble the ssn#s within our database. I would need a 9
digit number to be converted into another 9 digit number in our dev
database.


Example #1:
ssn: 123456789 converts to 987654321


Also there is a catch, there is a possibility that there could be
duplicate ssn within a table due to bad data. I was the 2 records with
the same actual ssn# to be converted into the same scrambled ssn# using
sql server (so that the scrambled ssn#s match) for this issue.


Is there a way to do this?



Celko Answers
>> create table SSN_MASK


(EW_SSN integer identity primary key not null,
OLD_SSN varchar(9)); -- not sure how you defined it <<

SSN is always CHAR(9). Simply numbering it with a proprietary feature
is not that good; you have destroyed the data type. Here is one we
used in procedural code with arrays.


CREATE TABLE SSN_masks
(shift_id INTEGER NOT NULL PRIMARY KEY -- cols 8 & 9
CHECK (shift_id BETWEEN 00 AND 99),
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
col4 INTEGER NOT NULL,
col5 INTEGER NOT NULL,
col6 INTEGER NOT NULL,
col7 INTEGER NOT NULL);


You take the last two digits of the SSN (fastest changing values) and
look up a vector that tells you how to shift the remaining seven
digits.


shift(x) = ABS((x + col_n) % 11-10)


Since you have 100 different masks, the data gets scrambled pretty good
and it destroys the area digits which would otherwise repeat and give
information about the population geographic distribution. This is also
reversible because we preserve the shift identifier in the output;
shift all of them and it is a bitch to unscramble without the shift
array. Then you change the array when you get the next sample.

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.

Creating a view do display normalised data flattened

SQL Apprentice Question
I have created the following schema to illustrate my question:


CREATE TABLE [dbo].[tblCustomerProperty] (
[cpcusID] [int] NOT NULL ,
[cpproID] [int] NOT NULL ,
[cpValue] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblCustomers] (
[cusID] [int] IDENTITY (1, 1) NOT NULL ,
[cusName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblProperties] (
[proID] [int] NOT NULL ,
[proName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[tblCustomers] WITH NOCHECK ADD
CONSTRAINT [PK_tblCustomers] PRIMARY KEY CLUSTERED ([cusID]) ON
[PRIMARY]
GO


ALTER TABLE [dbo].[tblProperties] WITH NOCHECK ADD
CONSTRAINT [PK_tblProperties] PRIMARY KEY CLUSTERED ([proID]) ON
[PRIMARY]
GO


ALTER TABLE [dbo].[tblCustomerProperty] ADD
CONSTRAINT [FK_tblCustomerProperty_tblCustomers] FOREIGN KEY
([cpcusID]) REFERENCES [dbo].[tblCustomers] ([cusID]) ON DELETE CASCADE
,
CONSTRAINT [FK_tblCustomerProperty_tblProperties] FOREIGN KEY
([cpproID]) REFERENCES [dbo].[tblProperties] ([proID]) ON DELETE
CASCADE
GO


This is essentially a very normalized customer database. A number of
properties are defined in the tblProperties table. One record per
customer exists in the tblCustomer table, and one record per
customer/property combination exists in the tblCustomerProperty table.


I imagine this is fairly common in systems that need to be highly
configurable. Obviously it comes with it's performance overheads but
it's very flexible.


Assume the following data
INSERT INTO tblProperties(proID, proName) VALUES(1, 'Occupation')
INSERT INTO tblProperties(proID, proName) VALUES(2, 'Email')
INSERT INTO tblCustomers(cusName) VALUES('Fred Bloggs')
DECLARE @ID int
SELECT @ID = @@IDENTITY
INSERT INTO tblCustomerProperty(cpcusID, cpproID, cpValue) VALUES(@ID,
1, 'Computer Engineer')
INSERT INTO tblCustomerProperty(cpcusID, cpproID, cpValue) VALUES(@ID,
2, '...@bloggs.com')


My question is how can I create a view that will return a flat view of
customers? The view needs to dynamically include 'columns' specified
in the tblProperties table without having to be changed.


eg.


cusID cusName Occupation Email
1 Fred Bloggs Computer Engineer f...@bloggs.com


Many thanks!



Celko Answers

>> This is essentially a very normalized customer database. <<


Not only is it un-normalized (it never made it to normalized to be
denormalized!), it is full of ISO-11179 violations, lack of relational
keys, data and meta-data are mixed, etc.

What you have is called a EAV (entity-attribute-value) design flaw. It
is a very common design error among people who were asleep in their
database classes :) Google it; it is a disaster that will fall apart
from the lack of data integrity in about one year of production work.


Also, get rid of that silly "tbl-" prefix and camel case -- it makes
you look like an OO programmer who does not know that even MS gave up
on camel case and Hungarian notation.


Seriously, you need to start over from scratch with a real data model
instead a vague "thingies have properties" view of the world. "To be
is to be something in particular; to be nothign in particular or
everything in general is to be Nothing" -- Aristotle

Using GO in stored proc

SQL Apprentice Question
I need to alter a temp table to add column, and then access that column
in next line.As it cant be possible in a same batch , I need to put a
GO between the lines.But the problem is, these are the lines of a
stored proc,and as soos as query analyzer(sqlserver2000) finds a GO,it
takes it as a last line of the stored proc.


Can't I add column and access the same in a same sp?Is it technically
possible?


Should you have any comments or any other work around , pls share with
me.


Apologies for any inconvenience in understnding the above points.


thanks,

Celko Answers
>> Should you have any comments or any other work around , pls share with me. <<


Do not program this way at all. This is like wanting to modify an
automobile in the middle of a race. You should start the race with a
complete car and not create it on the fly. Temp tables are also a
symptom of a procedural approach to a problem rather than a relational
approach.

Until we have actual specs (i.e. What you want to do) instead of a
kludge request (I.e. how you have decided to do it already), we cannot
help you.