Results 1 to 2 of 2

Thread: db design

  1. #1
    Join Date
    Feb 2009
    Posts
    1

    db design

    hi i have a db design question:

    i was designing a class registration system with the following relationship:

    student <--one to many--> registration <-- many to one --> class

    while i was creating the db tables, i came across this field which i'm not sure where it should belong. there is what you call students on hold or students in the waiting list. there are certain reasons why this happen. one is that the maximum number of students allowed for a class is reached so subsequent registrations cannot anymore be accommodated. thus those registrations are said to be in the waiting list.

    Column indicator
    i was thinking. should i just add a column in the registration table to indicate that the a registration record is in waiting list, e.i:

    Code:
        registration_id  fk_student_id  fk_class_id   is_in_waiting_list
               1              3                   2               N
               2              1                   1               N
               3              2                   2               Y
    now i say record of registration_id 3 is an unqualified entry of the registration table 'coz it cannot fully utilize of the relationship the table has. take for example, registrations is associated with the payments table:

    registration <--many to many--> payment

    and say that a registration can be associated to any number of payment (this is used for paying in installment). now there is a constraint that waiting list entries cannot be associated to any payment.

    Creating a separate table:
    now, i was having problems. how can i insure that this policy is enforced. i mean, i don't think the db design is good enough to enforce this rule. another implementation possible is to create a separate table for the waiting list entries, thus:

    student <-- one to many --> waiting_list <-- many to one --> class
    student <-- one to many --> registration <-- many to one --> class

    but my problem is it would be too ackward to work with two tables. suppose i want to count the number of registration regardless whether it was wait listed or not. then i have to query two tables.

    another problem, is that when a waiting list registration is approved. it should be moved to the registration table. well, that's good 'coz i could retain the entry in the waiting list table and remember that the registration was previously wait listed. but is the analysis right? that two tables should be maintained instead of adding an additional column to indicate a record is wait listed as discussed in the 'Column indicator' section.
    Last edited by gogimondi; 02-03-09 at 23:56.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your first idea was very good

    note that normally you do not need a separate PK for the association table, i.e. registration_id
    Code:
    CREATE TABLE registrations
    ( student_id    INTEGER NOT NULL
    , class_id      INTEGER NOT NULL
    , PRIMARY KEY ( student_id, class_id )
    , waiting_list  CHAR(1) NOT NULL DEFAULT 'N'
    );
    if you really want to allow multiple payments for a single registration, or a single payment for multiple registrations, then yes, registration_id might be a good idea

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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