Comments/suggestions Calendar table structure

Hi fellas/ladies,

So I’ve got to program a calendar in PHP that will track availability for activity bookings.

I can program the calendar no problem, I’m just not 100% certain about the database design. Your input would be greatly appreciate. I may or may not be way off the mark here.

I’m thinking I have 3 tables
calendar_dates
-id (int 11, PRI)
-date (date)

calendar_lookup
-date_id (int 11, PRI)
-product_id (int 11, PRI)
-booked (int 11) (Incremented by application everytime a booking is made)
-available (int 11) (Manually set availability)

products
-id (int 11, PRI)
-title (varchar 255)
-…

the first thing i would do is get rid of the id for the calendar table

there is absolutely no need for it, just let the date itself represent the date

what does “Incremented by application everytime a booking is made” mean? and why is this necessary?

also, what are “products” in this context?

Hey Rudy,

The product is surfing lessons.
There are only a specific amount of slots that can be booked each day
I was going to use the booked field to track how many people are booked on for a specific day. The available field is the amount of slots set to available.

Cheers,

Trev

Well thanks for that Rudy! I think I’m going to buy your book now!

Also, the REFERENCES function only works with InnoDB and I’m using myisam. Would I therefore just drop the following line:

, FOREIGN KEY ( product_id ) REFERENCES products ( id )

it’s actually pretty efficient right now…

CREATE TABLE products
( id INTEGER NOT NULL PRIMARY KEY 
, title VARCHAR(255) NOT NULL
);

CREATE TABLE calendar_lookup
( product_id INTEGER NOT NULL 
, FOREIGN KEY ( product_id ) REFERENCES products ( id )
, bookingdate DATE NOT NULL
, PRIMARY KEY ( product_id , bookingdate )
, booked SMALLINT NOT NULL DEFAULT 0
, available SMALLINT NOT NULL DEFAULT 0
);

you can leave that line in – mysql will scan it for errors, but then ignore it

So what do you think of the structure? It feels like it could be done more efficiently but I’m not sure how.

okay, that makes sense now

i just couldn’t wrap my head around multiple customers purchasing a product

:slight_smile: