Table Structure?

[Stitcho]

I am currently building a database and I am wondering the best way to do it. Basically I need to record booking information about 7 different studios.

The way this will work is that a club member will log into their account, pick the studio they want to book and then fill out a booking form. The booking will need to record their MemberID as well as the booking details.

The fields in the table will consist of: BookingID, MemberID, Studio Name, Booking Date, Booking Time.

Now do I store all 7 studios in the same table or do I have a different table for each studio? I was thinking the later mainly for reporting reasons but I am not sure if this is the best way to go about it or not.

 

[stopher]

Hi
You should definately store all studios in one table (a "studios" table). You can always filter this.

The biggest problem about booking databases is how to prevent a slot being booked more than once (I assume that is required here). If you start to build your relationship view in access then paste a picture of it here, I'm sure you'll get lots of advice about how to progress.

hth
Chris

 

[Stitcho]

Thanks for your reply Chris.
The bookings part is only going to be used for booking enquiries (sorry should have explained that better) but I need to record each of the enquiries in the DB.

I have actually been working on a solution this morning and think I may have cracked it but any suggestions would be welcome.

 

[Grafixx01]

Thanks for your reply Chris.
The bookings part is only going to be used for booking enquiries (sorry should have explained that better) but I need to record each of the enquiries in the DB.

I have actually been working on a solution this morning and think I may have cracked it but any suggestions would be welcome.

Are you letting the members book the slots and studios directly by themselves? Or are they just going to be able to look at like a calendar and view when studios are booked and the times they are booked for?

You're structure looks good so far in the picture you uploaded. But you may have to work on some more details for us to help you out more.

 

[Stitcho]

Are you letting the members book the slots and studios directly by themselves? Or are they just going to be able to look at like a calendar and view when studios are booked and the times they are booked for?

You're structure looks good so far in the picture you uploaded. But you may have to work on some more details for us to help you out more.

Nope they arent going to be able to book a slot. What they will see is a Google Calendar showing all the bookings/time slots. Next to the calendar will be a form they can complete to request a booking at a specific date/time. This will insert into the DB and fire off an email to the studio owners to let them know a new booking request has been received (sorry forgot to mention this will be migrated to MySQL and be based online once iv figured it out).

 

[stopher]

Looks fine. There's a couple of small points though...

All tables should have a primary key. It looks like you haven't yet assigned a PK to tblBandMembers. The PK will be both MemberID and BandID. That will ensure that a member can't be in the same band twice!

"Band" appears in tblBookings. But presumably you mean the band that the member belongs to? In which case, that information is already available through your relationships and you should not store it again. Having said that, I guess a band line-up can change and therefore your booking information would not be able to reflect which band was booked historically. In which case BandID should be in tblBookings and should be related to tblBands. This creates what appears to be a circular relationship but in fact you can only derive the right information through one relationship route bot both (hope that makes sense).

You have EquipmentDescription appearing in tblEquipment and tblEquipmentType. This might confuse you at some point so maybe one should be called EquipmentTypeDescription for clarity.

hth
Chris

 

[Alice_LJ]

Cool. Well done! Thank you for your sharing!

Cool. Well done! Thank you for your sharing!

__________________
jewelry store

 

[Stitcho]

Looks fine. There's a couple of small points though...

All tables should have a primary key. It looks like you haven't yet assigned a PK to tblBandMembers. The PK will be both MemberID and BandID. That will ensure that a member can't be in the same band twice!

"Band" appears in tblBookings. But presumably you mean the band that the member belongs to? In which case, that information is already available through your relationships and you should not store it again. Having said that, I guess a band line-up can change and therefore your booking information would not be able to reflect which band was booked historically. In which case BandID should be in tblBookings and should be related to tblBands. This creates what appears to be a circular relationship but in fact you can only derive the right information through one relationship route bot both (hope that makes sense).

You have EquipmentDescription appearing in tblEquipment and tblEquipmentType. This might confuse you at some point so maybe one should be called EquipmentTypeDescription for clarity.

hth
Chris

Thanks for all your help Chris. The member can be in multiple bands so I wasnt to sure how this would work in the BankMembers table.

The Band field in the Bookings table at the moment is just going to be a text field that they enter when booking and is not mandatory for them to fill in when making a booking. Again I wasnt sure exactly how to tackle this one which is why its just left as a free type field and not linked to anything.

 

[The_Doc_Man]

If you did a search of this forum for "Bookings" and "Scheduling" (in the sense of booking something), you might be pleasantly surprised at the articles available for your consideration.

 

[Stitcho]

Thanks I'll have a search. Im not at that stage yet though, im trying to figure out how to get the bands section to work.

I have added a primary key to the tblBandMembers table called BandMemberID but I dont really know how to link it all through. I think my question my be better directed at a MySQL forum at this point.

 

[Stitcho]

For anyone who may be interested here is the completed DB structure which is up and running and working perfectly (well nearly but thats a PHP issue)