#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    3
    Rep Power
    0

    Postgresql design advice


    I have some tables

    Code:
    CREATE TABLE members (
        member_id SERIAL PRIMARY KEY,
        first_nam text,
        address address, -- (composite type)
        contact_numbers text,
        date_joined date,
        type text
    );
    and two related tables:
    Code:
    CREATE TABLE basic_member (
        activities text[3]
    ) INHERITS (members);
    
    CREATE TABLE full_member ( 
        activities text[]
    ) INHERITS (members);
    and a planner table

    Code:
    CREATE TABLE planner (
        day text foreign key ...,
        time time foreign key ...,
        activity text foreign key ...,
        member bigint foreign key ...,
    );
    and a times table

    Code:
    CREATE TABLE times (
    time time
    );
    For the sports center open 9:00am to 8:00pm only 1 hour slot of activities can be booked at one time.

    Full members can book when ever they would like, but basic are only allowed to book between 9:00 to 12:00, 2:00 to 5:00 and 7:00 till 8:00.

    I need some way of making sure basic members can only book in those time slots. and then find a way to search for free slots that basic members can book.

    Any help is greatly appreciated.
    Thank you
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0
    So they are booking "courts" or "fields" for sports use?

    Thanks,

    Craig
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2009
    Posts
    7
    Rep Power
    0
    Try this design. You can then easily constrain the inserts into the "Member_Schedule" table through the front end code or through a BEFORE INSERT trigger.

    I did this kind of fast and w/o knowing all the requirements, but it should get you started down the right path. Forgive any wrong data types, syntax, etc...

    Code:
    ---------------------------------
    ----      Activity   
    ---------------------------------
    
    
    
    CREATE TABLE Activity
    (
    	Activity_Cd          CHAR(3) NOT NULL PRIMARY KEY,
    	Activity_Ds          VARCHAR(20) NOT NULL
    );
    
    
    
    
    ---------------------------------
    ----      Address   
    ---------------------------------
    
    
    
    CREATE TABLE Address
    (
    	Address_Id           serial NOT NULL PRIMARY KEY,
    	Address_Line_1_Tx    TEXT NULL,
    	Address_Line_2_Tx    TEXT NULL,
    	City_Nm              TEXT NULL,
    	State_Nm             CHAR(2) NULL,
    	Zip_Cd               CHAR(10) NULL
    );
    
    
    ---------------------------------
    ----      Member   
    ---------------------------------
    
    
    
    CREATE TABLE Member
    (
    	Member_Id            serial NOT NULL PRIMARY KEY,
    	First_Nm             VARCHAR(20) NULL,
    	Last_Nm              VARCHAR(20) NULL,
    	Join_Dt              DATE NULL,
    	Member_Type_Cd       CHAR(2) NULL
    );
    
    
    CREATE INDEX XIF1Member ON Member
    (
    	Member_Type_Cd
    );
    
    
    
    ---------------------------------
    ----      Member_Address   
    ---------------------------------
    
    
    
    CREATE TABLE Member_Address
    (
    	Address_Id           bigint NOT NULL PRIMARY KEY,
    	Member_Id            bigint NOT NULL PRIMARY KEY,
    	Start_Dt             DATE NULL,
    	End_Dt               DATE NULL
    );
    
    
    ---------------------------------
    ----      Member_Schedule   
    ---------------------------------
    
    
    
    CREATE TABLE Member_Schedule
    (
    	Member_Id            bigint NOT NULL PRIMARY KEY,
    	Activity_Cd          CHAR(3) NOT NULL PRIMARY KEY,
    	Schedule_Dt          CHAR(18) NOT NULL PRIMARY KEY,
    	Schedule_Tm          CHAR(18) NOT NULL PRIMARY KEY
    );
    
    
    
    ---------------------------------
    ----      Member_Type   
    ---------------------------------
    
    
    
    CREATE TABLE Member_Type
    (
    	Member_Type_Cd       CHAR(2) NOT NULL PRIMARY KEY,
    	Member_Type_Ds       VARCHAR(40) NOT NULL
    );
    
    
    
    ---------------------------------
    ----      Schedule   
    ---------------------------------
    
    
    
    CREATE TABLE Schedule
    (
    	Activity_Cd          CHAR(3) NOT NULL PRIMARY KEY,
    	Schedule_Dt          CHAR(18) NOT NULL PRIMARY KEY,
    	Schedule_Tm          CHAR(18) NOT NULL PRIMARY KEY,
    	Status_Cd            CHAR(1) NULL
    );
    
    
    
    ---------------------------------
    ----      Status   
    ---------------------------------
    
    CREATE TABLE Status
    (
    	Status_Cd            CHAR(1) NOT NULL PRIMARY KEY,
    	Status_Ds            VARCHAR(20) NOT NULL
    );
    
    
    ---------FKs
    
    ALTER TABLE Member
    ADD CONSTRAINT FK01_MEMBER_TYPE FOREIGN KEY (Member_Type_Cd) REFERENCES Member_Type (Member_Type_Cd);
    
    ALTER TABLE Member_Address
    ADD CONSTRAINT FK01_MEMBER FOREIGN KEY (Member_Id) REFERENCES Member (Member_Id);
    
    ALTER TABLE Member_Address
    ADD CONSTRAINT FK01_ADDRESS FOREIGN KEY (Address_Id) REFERENCES Address (Address_Id);
    
    ALTER TABLE Member_Schedule
    ADD CONSTRAINT FK02_MEMBER FOREIGN KEY (Member_Id) REFERENCES Member (Member_Id);
    
    ALTER TABLE Member_Schedule
    ADD CONSTRAINT FK01_SCHEDULE FOREIGN KEY (Activity_Cd, Schedule_Dt, Schedule_Tm) REFERENCES Schedule (Activity_Cd, Schedule_Dt, Schedule_Tm);
    
    ALTER TABLE Schedule
    ADD CONSTRAINT FK01_ACTIVITY FOREIGN KEY (Activity_Cd) REFERENCES Activity (Activity_Cd);
    
    ALTER TABLE Schedule
    ADD CONSTRAINT FK01_STATUS FOREIGN KEY (Status_Cd) REFERENCES Status (Status_Cd);
    Thanks,

    Craig

    edit 2012-05-18@16:33 : I just saw that I missed the PK constraints somehow...sorry about that. I will try to add them in shortly.

    edit: 2012-05-18@16:41 There I fixed the PK issue and added the FK constraints.

IMN logo majestic logo threadwatch logo seochat tools logo