Page 1 of 2
database logic
Posted: Fri Nov 28, 2003 10:13 am
by penciLneck
Hello, I'm quite a n00b *holds hands up*, but I have a query about how to approach this project I'm doing. I'm building an updatable events calender using php & mysql got most of the way with editing info in the columns of each field and displaying it. but have just realised that each entry/event has to display that its on multiple dates and venues. how do I concieveably get the admin user to enter that data from the form, (multiple textfields?) and then when the clientside page of listings is displayed sort them accordingly, without duplicate entries.
any advice/help would be much appreciated.

Posted: Fri Nov 28, 2003 10:52 am
by microthick
I would create two tables:
TABLE 1: Events (EventID, Name, Description, etc.)
TABLE 2: Schedule (EventID, StartDate, EndDate, Venue)
The Admin User would first add an Event. Then, by using a button like "Add New Event Session" he could add details about a new location, etc. and repeat this for all the event sessions.
So basically, you'd have two different types of forms.
On the client side, I would allow the events to be displayed by venue or by date or by event. This could be done fairly easily with simple selects.
Posted: Fri Nov 28, 2003 11:24 am
by penciLneck
thanks muchly microthick, so how do I link the schedule with the events in the events table, with ID?
Posted: Fri Nov 28, 2003 11:26 am
by penciLneck
also is there a way to let them add the date manually - like with drop downs or is the data they enter in a text field say 12.01.03 and 7:30 valid enough to sort in the display.
Posted: Fri Nov 28, 2003 11:34 am
by microthick
Yes, you'd link the two tables via the EventID column.
For dates, I usually add just a normal text input field with a JavaScript calendar beside it.
This way, users can either specify dates by typing them in, or by selecting the date from the calendar.
You can get many JavaScript calendars here:
http://www.hotscripts.com/JavaScript/Sc ... index.html
You just have to make sure that you parse that date properly with PHP in your action page so that it's suitable for insertion into your database.
Posted: Fri Nov 28, 2003 1:43 pm
by penciLneck
right, gotcha. Many thanks for your thorough and swift replies.

Posted: Fri Nov 28, 2003 3:28 pm
by McGruff
Posted: Sat Nov 29, 2003 8:12 am
by penciLneck
cheers, great chapter this - exactly what I needed. Still working through it and have already realised my system cannot work in the way set out above. If I've understood the chapter so far - I would need many more tables as I have a time, a date, and a location for each event, and my events are all on in different locations at different times over the course of a few months. so I guess this means I would need a time table, a date table, and a location table each with several columns and then somehow linked to eachother with different keys and relationships. Or have I got this completely wrong? I can't see another way of the user just editing one event at a time and all the locations, times etc.
Posted: Sat Nov 29, 2003 1:50 pm
by microthick
penciLneck wrote:cheers, great chapter this - exactly what I needed. Still working through it and have already realised my system cannot work in the way set out above. If I've understood the chapter so far - I would need many more tables as I have a time, a date, and a location for each event, and my events are all on in different locations at different times over the course of a few months. so I guess this means I would need a time table, a date table, and a location table each with several columns and then somehow linked to eachother with different keys and relationships. Or have I got this completely wrong? I can't see another way of the user just editing one event at a time and all the locations, times etc.
I doubt you'd need a date table or a time table.
But a location table might come in handy.
In the location table you could store the City, Address, MaxOccupancy etc.
Posted: Sat Nov 29, 2003 2:24 pm
by penciLneck
I guess the question I'm now trying to ask, is how would the relational linking between each table work? The problem I see is that there are too many variables, I want a admin user to be able to set the date and time and location for each event to be displayed as text at the client level. but in order for it to be sorted by the date that the events are on - you would have to have a table with columns like date1 date2 date 3... up to say 6 and order it by the first date row chonologically. the locations and times would have to be in a separate table with a similar construction - then I guess they are linked by event id and on the form each separate table is filled up by its own textfield. Is that right?
or have I lost the plot?

Posted: Sat Nov 29, 2003 2:40 pm
by McGruff
At first glance, I'd agree with microthick.
An event table could store start date and end date. These (probably) are valid as "event" data. They'd be in different cols so you don't break one of the simpler normalisation rules about storing more than one value per col.
However, let's say all your events correspond exactly to public holidays. In that case, dates can be viewed as a separate entity rather than as event properties. A fully normalised database would store dates in a public_hols table, with each event linked to the appropriate holiday/s.
I expect venues will not be event-specific (although they could be) so again they would get their own table.
Relationships are fairly easy to set up. A one to many is just a matter of adding an integer id column. A many to many will need a separate join table.
You can set up some indexes to help the JOIN queries you'll need and you can test queries with EXPLAIN to see if they are as efficient as they can be (see the mysql manual, if that's what you're using).
It's worth spending the time to get the db design right. It's much harder to make changes later on once you've started scripting.
Posted: Sat Nov 29, 2003 2:53 pm
by McGruff
penciLneck wrote:I guess the question I'm now trying to ask, is how would the relational linking between each table work?...
...
....
Got interrupted while making my last post so didn't see this.
(1) This creates a one-venue-to-many-events relationship:
events_table:
evid | venid | start_date | end_date | description | etc
venue table:
venid | venue | notes | etc
(2) Or for a many-to-many, create a join table:
j_events_venues
evid | venid
Not sure I fully understand your needs yet but maybe that's a starting point.
Posted: Sat Nov 29, 2003 3:12 pm
by penciLneck
thats great. I really appreciate it, I think I'm slowly learning
just let me absorb your last posts, and I'll let you know what happens.
I'm not sure if I need a one to one or a one to many, my events will look like this:
----------------------
title
----------------------
image | event text
----------------------
location, date, time|location2,date2,time2|etc to 6
----------------------
contact number
----------------------
this is how each event would look when displayed on the page.
I guess the tables would look like this:
events_table
------------------------------------
eventID|title|entry|company|contact|image
location_table
------------------------------------
eventID|venue1|venue2|venue3|(up to 6)
date_table
------------------------------------
eventID|date1|(up to 6)
time_table
------------------------------------
eventID|time1|(up to 6)
is that right?
Posted: Sat Nov 29, 2003 3:27 pm
by McGruff
If the same event can have multiple venues, maybe try an events table and an event_instances table. The latter would store venue, date and time.
Very often in programming you are looking for something held in common and working out an way to deal with that which avoids anomalies ie finding a way to write it once and then share it out.
Posted: Sat Nov 29, 2003 3:34 pm
by penciLneck
the same event can have multiple venues,dates, and times - is my version right, or does each table need a different relationship with eachother?
actually just thought of something else, how do I know how many locations to display in the page query, if the admin user has only filled in 3 locations, because the event is only at 3 locations, if theres nothing in venue 4-6 and the same with date and time - won't that produce an error?