Database Design (Oracle) - modelling business logic

Question

I face the following dilema.

Consider the code below - it's something I wrought to describe the problem - not a real implementation, I omitted some check constraints and NOT NULL to avoid clouding the picture.

CREATE TABLE Theater_Halls(
    thha_id NUMBER(2) CONSTRAINT pk_thha_id PRIMARY KEY,
    thha_name VARCHAR2(30) CONSTRAINT nn_thha_name NOT NULL,
    thha_no_of_seats NUMBER(4) CONSTRAINT ch_thha_no_of_seats CHECK (thha_no_of_seats > 0)
        CONSTRAINT nn_thh_no_of_seats NOT NULL
);     
CREATE TABLE Seats (
    seat_id NUMBER (8) CONSTRAINT pp_seat_id PRIMARY KEY,
    seat_no NUMBER (4) CONSTRAINT ch_seat_no CHECK (seat_no > 0),
    thha_id NUMBER(2) CONSTRAINT fk_seat_thha_id REFERENCES Theater_Halls(thha_id)
);


CREATE TABLE Events ( -- each event has a hall in theater associated with it
    evnt_id NUMBER(4) CONSTRAINT pk_evnt_id PRIMARY KEY,
    evnt_name VARCHAR2(30) CONSTRAINT nn_evnt_name NOT NULL,
    thha_id NUMBER(2) CONSTRAINT fk_evnt_thha_id REFERENCES Theater_Halls(thha_id),
    evnt_date TIMESTAMP CONSTRAINT nn_evnt_time NOT NULL
);

CREATE Table Users (
    user_id NUMBER(10) CONSTRAINT pk_user_id PRIMARY KEY
    );

CREATE TABLE Bookings (
    bkng_id NUMBER(10) CONSTRAINT pk_bkng_id PRIMARY KEY,
    evnt_id NUMBER(10) CONSTRAINT fk_bkng_evnt_id REFERENCES Events(evnt_id),
    seat_id NUMBER(8) CONSTRAINT fk_bkng_seat_id REFERENCES Seats(seat_id),
    user_id NUMBER(10) CONSTRAINT fk_bkng_user_id REFERENCES Users(user_id),
    bkng_price NUMBER(6,2) CONSTRAINT nn_bkng_price NOT NULL,
    CONSTRAINT un_evnt_seat_user UNIQUE (evnt_id, seat_id)
);

Now, this implementation is satisfying criteria in a sense it holds the data without (it would seem) anomalies.

I have two questions however.

1. Should it be left in a form above, initially created with user_id as NULL and once a booking is made in an application user_id would be populated and procedure would keep track of booked seats (user_id <> NULL)

2. Or would it be a better approach to create intermediary table e.g.

   CREATE TABLE Events_Seats (
   evse_id NUMBER(8) CONSTRAINT pk_evse_id PRIMARY KEY,
   evnt_id NUMBER(4) CONSTRAINT fk_evse_evnt_id REFERENCES Events(evnt_id),
   seat_id NUMBER(8) CONSTRAINT fk_seat_id REFERENCES Seats(seat_id),
   evse_price NUMBER(6,2) CONSTRAINT nn_evse_price NOT NULL,
   CONSTRAINT un_evnt_seat_user UNIQUE (evnt_id, seat_id));
   

   and then have a Foreign Key linked to the Bookings, where Bookings table would be 'transactional' - meaning, new rows would be inserted when a picks a sit for a given event. Is there any advantage in given approaches as far as modelling business logic and potential errors / invalid data is concerned?

Answer 1

I think you do not need EVENT_SEATS table. You do not need even populated BOOKINGS with NULL for userid. Just leave the Bookings table empty. You can query your possible bookings for events using :

select e.EVNT_NAME as EventName, s.seat_no as AvailableSeat
from  events e
join seats s
on s.thha_id = e.thha_id
left join Bookings b
on b.seat_id = s.seat_id and
   b.evnt_id = e.thha_id
where  e.evnt_id = 1 and
   b.bkng_id is null

When record for Booking will be created then b.bkng_id is null will remove result from possible booking list.