Re: Handling Multi User Stock Taking

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Tue, 10 Feb 2009 20:38:24 -0600
Message-ID: <IXqkl.10507$pr6.485_at_flpi149.ffdc.sbc.com>

krislioe_at_gmail.com wrote:
> Hi sql gurus,
>
> We have a system to handle seat inventory stock taking in a marine
> cruise. All seats in all rooms are maintained in a table. below is the
> simplified structure of the table :
> CREATE TABLE SEAT_STOCK
> (
> ROOM_ID NUMBER(12) PRIMARY KEY,
> ROOM_NO VARCHAR2(5 CHAR) NOT NULL, -- room number
> SEAT_NO VARCHAR2(1 CHAR) NOT NULL, -- seat number : A,B.C,D etc
> ROOM_TYPE VARCHAR2(1 CHAR), -- Room Status [N]Neutral, [M]ale, [F]
> emale
> SEAT_STATUS VARCHAR2(1 CHAR), -- Unoccupied, [O]ccupied
> PSG_STATUS VARCHAR2(1 CHAR), -- Passenger Status : [M]ale, [F]emale
> PSG_NAME VARCHAR2(30 CHAR) -- Passenger Name
> )
>
> I want to create a procedure that will do stock taking the available
> seat based on the passed parameter:
> v_psg_status (M/F)
> v_num_seat (Number of seat requested)
>
> The rule is :
> - at the beginning, all Seat in the room will be 'Unoccupied' and have
> Room_Type : 'Neutral'
> First Male passenger that occupy the room will change ALL Seat in the
> room to have Room_Type = 'Male', and the SEAT_Status = 'Occupied'
> First Female passenger that occupy the room will change ALL Seat in
> the room to have Room_Type = 'Female', and the SEAT_Status =
> 'Occupied' (because male & female cannot share same room)
>
> - On stock taking, the priority rule applies :
> For Male pasenger, first the query should look for the 'Unoccupied'
> Seat in Room_Type 'Male' for the number of
> seat requested, Only if its not available or not enough then the
> remaining should go to 'Unoccupied' seat in
> Neutral room (consequently change the room type to 'Male'). Same rule
> applies for female passenger.
>
> - How to handle multi user locking/ concurrency requirement ?
> because many users may see SAME unoccupied Seat and take it
> concurently .
>
> I try to avoid cursor and use 2 or 3 DMLs to do the task if its more
> efficient. Could you please give suggestions ?
>
> Thank you very much,
> xtanto
>
> PS : the sample data.
> begin
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (1, '101', 'A', 'N');
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (2, '101', 'B', 'N');
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (3, '101', 'C', 'N');
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (4, '101', 'D', 'N');
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (5, '101', 'E', 'N');
> --
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (6, '201', 'A', 'N');
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (7, '201', 'B', 'N');
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (8, '201', 'C', 'N');
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (9, '201', 'D', 'N');
> INSERT INTO SEAT_STOCK(ROOM_ID, ROOM_NO, SEAT_NO, ROOM_TYPE) VALUES
> (10,'201', 'E', 'N');
> end;
>

So at the end of a cruise do you "reset" the inventory to "zero" status?

If M/F cannot be in the same room, what about couples? How many "seats" per room? Are these literally seats and rooms? Received on Tue Feb 10 2009 - 20:38:24 CST