Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: Design for checkbox..PLZ help

    Hi Gurus, Please help me… this is very simple query for you guys, but I am almost stuck with it…

    I have requirement as below:

    Customer can select all or none of the programs to be enrolled in.
    • There are 3 checkboxes on the form, Prpgram A, Program B, Program C

    Now how can I design the table for this requirement?

    I have designed as below

    Design:1
    Table:
    [1] PROGRAM_ENROLLMENT

    EnrollmentID NUMBER(10) NOT NULL,
    CustomerID NUMBER(8) not null,
    PrpgramA VARCHAR2(1) default ‘N’ not null CONSTRAINT chk_pe_ProgramA CHECK (PrpgramA IN (‘Y’,’N’)),
    PrpgramB VARCHAR2(1) default ‘N’ not null CONSTRAINT chk_pe_ProgramB CHECK (PrpgramB IN (‘Y’,’N’)),
    PrpgramC VARCHAR2(1) default ‘N’ not null CONSTRAINT chk_pe_ProgramC CHECK (PrpgramC IN (‘Y’,’N’)),
    CONSTRAINT PE_PK PRIMARY KEY (EnrollmentID)


    Here, EnrollmentID is primary key & CustomerID is foreign key.

    Now here problem is, if customer only select to enroll in ProgramA, still, space for ProgramB & ProgramC fields get wested.

    So came up with Design:2

    Table:
    [1] PROGRAM
    ProgramID number(1) not null,
    Program varchar2(10) unique not null,
    CONSTRAINT PROGRAM_PK PRIMARY KEY (ProgramID)



    [2] PROGRAM_ENROLLMENT

    EnrollmentID NUMBER(10) NOT NULL,
    CustomerID NUMBER(8) not null,
    PrpgramID NUMBER(1) not null,
    CONSTRAINT PE_PK PRIMARY KEY (ProgramID)


    ALTER TABLE PROGRAM_ENROLLMENT ADD CONSTRAINT PE_UQ1 UNIQUE(CustomerID, PrpgramID);


    Here, EnrollmentID is primary key, CustomerID & ProgramID are foreign key.


    Now here problem is, if customer select more than 1 program to enroll than I have multiple rows for one coustomer.


    Question
    1. Please tell me, from above 2 designs, which 1 is the best
    2. If you can suggest any better design than these, than will be very appretiated by me.


    Gurus, Please answer these questions asap as this is very urgent for me.

    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The second is much better. If you use the first one, wasted space is NOT a problem, the problem is what happens when the management decides to have 4 classes. Your second scheme allows you to have any number of enrollments, the first schema would have to have a table modification to allow the additional classes. Also, do NOT restrict your PrpgramID to one digit. What happens when there are 10 classes? Never design for how the data is now, always design for what it can be in the future.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2008
    Posts
    8
    Thanks beilstwh, I got it that design2 is better & I shouldn't restrict program id to 1 digit.

    Appreciated.


    Is there any better design (than design:2) to solve this....as I am beginner in database designing? Please let me know.
    Last edited by NirajPatel; 04-08-08 at 15:27.

Posting Permissions

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