Review of DB Design - Normalized, Contraints, Foreign Keys, etc.

First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)

I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.

My partial db schema is as follows:

participants
--DID (pk) char(1)
--LID (fk - schools) char(4)
--studentLast varchar(50)
--studentFirst varchar(25)

Sample Data would be
010191M001 | 5671 | SPARKS | JONATHAN
030495F283 | 5671 | DYLAN | CYNTHIA
=====================================

enrollhist (insert/update trigger for enrollactive)
--EID (pk - autonumber) bigint(8)
--EMID (fk - enrollmode) int(4)
--DID (fk - participants) char(10)
--LID (fk - schools) char(4)
--enrollactive bit(1)

Sample Data would be
38173 | 4 | 030495F283 | 9003 | 0
38266 | 3 | 010191M001 | 5671 | 0
39022 | 6 | 030495F283 | 9003 | 0
39036 | 5 | 030495F283 | 9003 | 0
39044 | 4 | 030495F283 | 5671 | 1
39117 | 4 | 010191M001 | 5671 | 1
=====================================

enrollmode
--EMID (pk) int(4)
--mode varchar(25)

Sample Data would be
1 | RECEIVED
2 | WAITING
3 | PENDING
4 | ENROLLED
5 | DROPPED
6 | TRANSFERRED
10 | ORPHANED
11 | DENIED
=====================================

schools
--LID (pk) varchar(4)
--CTID (fk - caltracks) char(1)
--AID (fk - agencies) char(1)
--SDID (fk - schooldist) char(1)
--COID (fk - countydist) char(1)
--sitename varchar(25)
--sitetitle varchar(75)

Sample Data would be
5671 | 3 | 2 | 1 | 4 | ASCOT | ASCOT AVENUE
9003 | 2 | 1 | 4 | 1 | ROWAN | ROWAN AVENUE
2865 | 1 | 3 | 2 | 3 | BRIGHT | BIRDELEE BRIGHT
=====================================

caltracks
--CTID (pk) char(1)
--legend char(4)
--trktitle varchar(15)
--trkcnt int(4)

Sample Data would be
1 | 9030 | 90/30 | 4
2 | CON6 | CONCEPT-6 | 3
3 | SNGL | SINGLE TRACK | 1
=====================================

agencies
--AID (pk) char(1)
--legend varchar(4)
--agencytitle varvhar(50)

Sample Data would be
1 | CRYS | CRYSTAL STAIRS
2 | MAOF | MEXICAN AMERICAN FOUNDATION
3 | PATH | PATHWAYS
4 | CCRC | CHILD CARE RESOURCE CENTER
5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA
==========================================

THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."

My questions are: :confused:
(1) Is the use of FK as a Constraint any different than using an INDEX and how?
(2) Should I Alter the Tables to include CASCADING Up/Down?
(3) Are the use of CHARs Ok for the Keys?
(4) Have I over/under-normalized any of the relationships?not to avoid your good questions, but i'll grab at the low hanging fruit...

the PK of 'participants' won't allow you to have many participants.
256 tops, it would appear.

ditto all your char(1) PKs|||You PKs are each 1 character? Doesn't seem too scalable to me...|||(oddity) Sorry about the TYPO, the PK for table.participants is CHAR(10) as shown as a FK in table.enrollhist

(blindman) The instances when I use CHAR(1) is truly not scalable, but the chances of the keys needing 2+ chars are slim to none. But, in the optimum chance of meeting that slim probability, I would have to agree with you.

I also have an additional question regarding the schema I have for the enrollhist table. Does anyone see any flaws in its design.

The implementation I want is to have the enrollmode and effective date (forgot the column for effective date in the schema listed [effdate datetime]) stored whenever there is a change in the status of the participant. The previous database had each of the modes as a column of the participants table. This is a no-no in my book!

thanks so far...
-bigEz|||Its not a no-no if each state can be achieved at most one time, but since this is probably not the case then your implementation is much more robust.

returning the table that caused a contraint error

HI all,
I have, as per normal, relations setup with delete contraints on the parent
child relation.
Now I have a table that has many such child relations and constraint. As a
result when a user tries to delete the parent, a correct message is
triggered.
My problem is, trying to determine which table is the cause of this
contraint error. I can see the table mentioned in the 547 error that SQL
produces. I was wondering if there is a SP or something that could return
just the table name, that I could use, instead of wading through the error
message.
Thanks
Robert
hi Robert,
Robert Bravery wrote:
> HI all,
> I have, as per normal, relations setup with delete contraints on the
> parent child relation.
> Now I have a table that has many such child relations and constraint.
> As a result when a user tries to delete the parent, a correct message
> is triggered.
> My problem is, trying to determine which table is the cause of this
> contraint error. I can see the table mentioned in the 547 error that
> SQL produces. I was wondering if there is a SP or something that
> could return just the table name, that I could use, instead of wading
> through the error message.
this should be the scenario you mean...
SET NOCOUNT ON
USE tempdb ;
GO
CREATE TABLE dbo.MasterT (
ID int NOT NULL PRIMARY KEY ,
Data varchar(10) NOT NULL
) ;
CREATE TABLE dbo.DetailT (
ID int NOT NULL PRIMARY KEY ,
IdMasterT int NOT NULL
CONSTRAINT FK_MasterT_DetailT
FOREIGN KEY REFERENCES dbo.MasterT (ID),
Data varchar(10) NOT NULL
) ;
GO
INSERT INTO dbo.MasterT VALUES ( 1 , 'value' ) ;
INSERT INTO dbo.MasterT VALUES ( 2 , 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 1 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 2 , 1, 'value' ) ;
INSERT INTO dbo.DetailT VALUES ( 3 , 2, 'value' ) ;
GO
DELETE dbo.MasterT
WHERE ID = 1 ;
GO
DROP TABLE dbo.DetailT , dbo.MasterT ;
all you can get is the error you already noted, 547, which reports the
actual problem indicating the constraint name, the involved table and colum,
Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_MasterT_DetailT'. The conflict occurred in database 'tempdb', table
'DetailT', column 'IdMasterT'.
The statement has been terminated.
no other way is allowed/possible...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply