Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Question Unanswered: 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:
    (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?

  2. #2
    Join Date
    Jul 2004
    Posts
    60
    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

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You PKs are each 1 character? Doesn't seem too scalable to me...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2004
    Posts
    2
    (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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •