Results 1 to 3 of 3

Thread: nas

  1. #1
    Join Date
    Mar 2003
    Location
    Stoke on Trent
    Posts
    8

    Thumbs up Unanswered: nas

    hello there

    i need to create avilability table which is for interpereters, who are aviable in different time and different day during the week.

    manul sheet work like this

    monday am pm nights 24 hours
    tuesday am pm night 24hours
    wednessday am pm night 24hous
    thursday am pm night 24hours
    friday am pm nights 24 hours
    saturday am pm nights 24houre
    sunday am pm nights 24hours

    i am not sure how i can do this in oracle

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: nas

    So does the table show that a particular interpreter's availability for a particular day of the week is EITHER am OR pm OR night OR 24hours? Or can the interpreter be available both am and pm, or am and night?
    Presumably, interpreter may not be available at all on some days?

    Suppose we have an interpreter table:

    CREATE TABLE interpreter
    ( interpreter_id NOT NULL
    , interpreter_name VARCHAR2(60) NOT NULL
    , CONSTRAINT interpreter_pk PRIMARY KEY(interpreter_id)
    );

    Depending on the requirements, you could then have:

    CREATE TABLE availability
    ( interpreter_id NUMBER NOT NULL
    , day_of_week VARCHAR2(3) NOT NULL CHECK (day_of_week IN ('SAT','SUN','MON','TUE','WED','THU','FRI'))
    , time VARCHAR2(7) CHECK (time in ('AM','PM','NIGHT','24HOURS')
    , CONSTRAINT availability_pk PRIMARY KEY (interpreter_id, day_of_week)
    , CONSTRAINT availability_interpreter_fk FOREIGN KEY (interpreter_id) REFERENCES interpreter
    );

    INSERT INTO availability( interpreter_id, day_of_week, time )
    VALUES (123, 'MON', 'AM');

    or

    CREATE TABLE availability
    ( interpreter_id NUMBER NOT NULL
    , day_of_week VARCHAR2(3) NOT NULL CHECK (day_of_week IN ('SAT','SUN','MON','TUE','WED','THU','FRI'))
    , am_flag VARCHAR2(1) CHECK (am_flag IN ('Y','N'))
    , pm_flag VARCHAR2(1) CHECK (pm_flag IN ('Y','N'))
    , night_flag VARCHAR2(1) CHECK (night_flag IN ('Y','N'))
    , x24hour_flag VARCHAR2(1) CHECK (x24hour_flag IN ('Y','N'))
    , CONSTRAINT availability_pk PRIMARY KEY (interpreter_id, day_of_week)
    , CONSTRAINT availability_interpreter_fk FOREIGN KEY (interpreter_id) REFERENCES interpreter
    );

    INSERT INTO availability( interpreter_id, day_of_week, am_flag, pm_flag, night_flag, x24hour_flag )
    VALUES (123, 'MON', 'Y','N','N','N');

    There will be various other ways to do the same thing, or variations on it.

  3. #3
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    ello nas!

Posting Permissions

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