Postgresql design advice

Dev Shed Forums Sponsor:

May 6th, 2012, 04:25 PM

dcalladi

Registered User

Join Date: Apr 2012

Posts: 3

Time spent in forums: 1 h 45 m 45 sec
Reputation 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

May 18th, 2012, 04:11 PM

wcboyd

Registered User

Join Date: Mar 2009

Posts: 7

Time spent in forums: 1 h 48 m 42 sec
Reputation Power: 0

So they are booking "courts" or "fields" for sports use?

Thanks,

Craig

May 18th, 2012, 04:31 PM

wcboyd

Registered User

Join Date: Mar 2009

Posts: 7

Time spent in forums: 1 h 48 m 42 sec
Reputation 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.

Postgresql design advice

Developer Shed Advertisers and Affiliates