Some advice on DB design for simple booking system

Question

So I have the task to create a DB model for a simple booking system and I have not clue so I ask for help here. What I have done until now is as image shows but I don't know if this is enough to build a simple booking system from scratch. I tried Google looking for some DB designs and find nothing helpful, any advice? What did yours think about my model? Something missing?

UPDATE (based on answers) Ups I forget to tell what the requirements are and here we go:

• sport_field can be booked for given hours and just in one day meaning for example users can't book a sport_field for two days or two and half a day but users can book for one, two, three and more hours. (hours is a valid period from 9:00 AM to 11:00PM hours outside this times isn't valid - maybe I'll hide using programming)
• there will be a wait list and what is this? A queue for sport_field based on availability (maybe I'm lost here too since this is the main behavior of booking systems)

• sport_field will remain booked for one day maximum so for example users can book the sport_field and administrators will wait for payment, if payment is not successfully then sport_field will be available once again during the next day.

• price is fixed for each sport_field it will never change and price is hourly

• active means to me if the sport_field is reserved for a period or specific hour, maybe my design is wrong and I'm unsure
• users table comes from another system I'm just adding the booking part to a huge system but I need to use their tables

UPDATE 2

Based on suggestions I improve the model to this:

UPDATE 3

New changes based on suggestions:

Solution

User table is not really relevant, since you can't change it and for booking purpose, all you care about is the user ID.

I would add a starting and ending available time to sports fields. For example, can I book a field at 3:00am? I would also change the price field name to price_hourly, just to make sure future people know its meaning..

You don't really need active in sports field based on your description. You can tell if a field is active for a date range by querying the booking table. If you keep the active flag in the sports table, it is redundant. Is the field active if the flag says true, but no booking entry exists???

If the fields are at various locations, you might want to add longitude and latitude for mapping and direction views to the fields.

You booking table should probably have a status column (i.e. reserved, paid_for, requested, etc.)

A wait list could then be all entries in the booking table with requested status.

Some ideas to help you get started...

A view is a wrapper around a query call. Can be used to hide fields, for example, if you didn't want everyone to know the price, you might create a view like:

CREATE VIEW sports_field_view
AS
SELECT name,description,starting_hour,ending_hours 
FROM sport_field

This way, you can give people access to the table, while hiding information in it.

The view I was suggesting, is something like this

CREATE VIEW sports_field_view AS 
SELECT sports_field.name,description,starting_hour,ending_hours,
       book_status.name as Booked,Booking.Start_time,booking.end_Date
FROM booking
JOIN sports_field ON booking.sports_field_id=sports_field.id
JOIN book_status ON booking.status_id =book_status.id

Note, I don't work with mySQL much, so the CREATE VIEW syntax might be slight off

Comments

[Reynier]

I added some info, what fields or tables did you suggest based on that info? Feel free to ask something if you don't understand

[Reynier]

I updated the model based on your suggestions, can you take a look and tell me how it looks like? What else you will add? I miss something?

[Sparky]

Looks good, only thing I might add is a Requested date/time column to booking, in case multiple people request a field, you need to decide priority. I would also suggest creating a view for the books, showing the active status (based on the look-up in bookings) and the book type, based on the new status table...

[Reynier]

Hi once again, I added the field as you suggested but get lost about add the view, can you explain me what you try to tell me with that?

[Sparky]

Minor issue, move the requeseted_on into the booking table, the user requested to book a field. I'll add the view concept into the answer above