database logic

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

database logic

Post 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. :)
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post by penciLneck »

thanks muchly microthick, so how do I link the schedule with the events in the events table, with ID?
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post 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.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post by penciLneck »

right, gotcha. Many thanks for your thorough and swift replies. :)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post 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.
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post 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? :)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post 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?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
penciLneck
Forum Newbie
Posts: 15
Joined: Fri Nov 28, 2003 10:13 am
Location: Daarsit

Post 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?
Post Reply