Am I writing this script correctly? (SQL)

Question

So I am writing a SQL script for a library system. I have a member table but the member table includes Staff , Librarian , students and so for the attribute mem_id it can be (STF001 , LIB001 , STU001) Staff , Librarian and Students respectively. But I also have to create Staff , Librarian and Students table separately. How do I do it? this is what I have so far,

DROP TABLE MEMBER CASCADE CONSTRAINTS;
DROP TABLE STAFF CASCADE CONSTRAINTS;
DROP TABLE STUDENT CASCADE CONSTRAINTS;
DROP TABLE LIBRARIAN CASCADE CONSTRAINTS;
DROP TABLE BOOK_DETAIL CASCADE CONSTRAINTS

CREATE TABLE MEMBER 
(
    mem_id      VARCHAR2(8)  NOT NULL, 
    mem_fname   VARCHAR2(10) NOT NULL,
    mem_lname   VARCHAR2(20) NOT NULL,
    mem_email   VARCHAR2(50) NOT NULL,
    mem_mobile  VARCHAR2(10) NOT NULL,
    mem_pass    VARCHAR2(20) NOT NULL,
    mem_status  VARCHAR2(1)  NOT NULL, 

    CONSTRAINT mem_id_pk PRIMARY KEY(mem_id),
)

CREATE TABLE STAFF
(
    Staff_id  VARCHAR2(8) NOT NULL,
    Branch_id VARCHAR2(8) NOT NULL,

    CONSTRANT staff_id_pk PRIMARY KEY(staff_id) 
    CONSTRANT branch_id_fk 
        FOREIGN KEY(branch_id) REFERENCES lib_branch(branch_id)
)

Am I doing this right? because I feel Like I need to reference Staff table with the member table or is it not required?

Thanks for the help in advance

Answer 1

Am I doing this right?

You're not.

---

"Members" of the library can be - as you said - staff, librarian, students. That should be just a column in the members table, referencing another (let's call it member_type). Something like this:

create table member_type
  (member_type_id    number       constraint pk_memtyp primary key,
   name              varchar2(20) not null
  );

insert into member_type (member_type_id, name) 
  select 1, 'staff'     from dual union all
  select 2, 'librarian' from dual union all
  select 3, 'student'   from dual;
  

create table member
  (mem_id            number       constraint pk_mem primary key,
   ...
   member_type_id    number       constraint fk_mem_type references
                                    member_type (member_type_id)
                                    not null,
   ...
  );

Why such a model? Because - what if another "member type" shows up? Will you create yet another table, modify application to support it? You could, but you should NOT.