How to implement rental limit for different members in DVD rental database-CodePudding

I was hoping I could get some advice on how to implement a rental limit for users in a DVD rental database design. Basically there are two types of Users(Members and Staff) who all have a membership card. I was thinking that the MEMBERSHIP_CARD could be my supertype and then MEMBERS and STAFF would be my subtypes.

So this brings me to how to implement a limit for renting DVD's. Staff can rent out 5 DVD's at a time while Members can only rent 3.

EDIT: I think its important to add that the structure of the design includes 3 tables:

MEMBERSHIP_CARD: Card_id (PK), Fname, Lname, Email, Status

DVD: DVD_id(PK), Title, Duration, Publisher (FK)

RENTALS: Rental_id(PK), Issue_date, Return_date, DVD_id (FK),Card_id(FK)

Here were the options I had in mind, but I am not sure which one to use..

Option 1: Each MEMBERSHIP_CARD will have a max loans attribute. But that seems quite redundant, there’s gonna be so many people with the the same values.

Option 2: Use a trigger so that when a rental is created it checks if its a member or staff and checks how many rentals that member already has. But then I am just hard coding these values, doesn't seem very flexible.

Option 3 - my current favourite We add a MEMBERSHIP_TYPE entity: this stores the name of the membership and the maximum rentals? But is this overkill if there are only 2 membership types..? Membership_type_id, Membership_name, Rental_max eg : --> 1, STAFF, 5(then each MEMBERSHIP_CARD will have a Membership_type_id as a Foreign Key)

I would love any input here, Thanks!

CodePudding user response：

I see three tables involved here:

user_type with two rows MEMBER and STAFF, contains the rental limit
users with one row per user, each user is tagged with a user_type
rentals with the users' rentals and a return date, so we see when a rental is ended

Then I'd create a compound trigger to check whether reantal inserts violate a user's rental limit.

CREATE OR REPLACE TRIGGER trg_rental_limit_check
FOR INSERT ON rentals COMPOUND TRIGGER
  -- -----------------------------------------------------------
  -- DECLARATION SECTION
  -- -----------------------------------------------------------
  v_user_ids sys.ora_mining_number_nt := sys.ora_mining_number_nt();

  -- -----------------------------------------------------------
  -- AFTER EACH ROW SECTION
  -- -----------------------------------------------------------
  AFTER EACH ROW IS
  BEGIN
    v_user_ids.extend(1);
    v_user_ids(v_user_ids.count) := :new.user_id;
  END AFTER EACH ROW;

  -- -----------------------------------------------------------
  -- AFTER STATEMENT SECTION
  -- -----------------------------------------------------------
  AFTER STATEMENT IS
  BEGIN
    FOR rec IN
    (
      select listagg(user_id, ', ') within group (order by user_id) as users
      from
      (
        select user_id
        from rentals r
        where user_id in (select * from table(v_user_ids))
        and return_date is null
        group by user_id
        having count(*) > (select ut.rental_limit
                           from users u
                           join user_type ut on ut.user_type_id = u.user_type_id
                           where u.user_id = r.user_id
                          )
      )
    )
    LOOP
      RAISE_APPLICATION_ERROR(-20001, 'Too many rentals for user(s) ' || rec.users);
    END LOOP;
  END AFTER STATEMENT;
END trg_rental_limit_check;

(Of course you can call the users table MEMBERSHIP_CARD and the user_type table MEMBERSHIP_TYPE, if you like that better :-)

CodePudding user response：

A member_type coumn which defines if as user is staff or not. Also a loan column where you keepo track howmany loan a user has

the rest is a simple CHECK constraint will help keep always track that a user will nocht get more dvd as it is allowed

CREATE TABLE member ( id int, member_type int, loan int 
,
  CONSTRAINT check_loan
  CHECK ((member_type = 1 AND loan <= 5) OR (member_type = 2 AND loan <= 2)))

INSERT INTO member VALUEs (1,1,0)

1 rows affected

UPDATE member SET loan = 2 WHERE id = 1

1 rows affected

UPDATE member SET loan = loan   4 WHERE id = 1

ORA-02290: check constraint (FIDDLE_IHFVIILOHPJRJZLFVGTY.CHECK_LOAN) violated

fiddle