any advice/help would be much appreciated.
database logic
Moderator: General Moderators
-
penciLneck
- Forum Newbie
- Posts: 15
- Joined: Fri Nov 28, 2003 10:13 am
- Location: Daarsit
database logic
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.
any advice/help would be much appreciated.
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
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.
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
-
penciLneck
- Forum Newbie
- Posts: 15
- Joined: Fri Nov 28, 2003 10:13 am
- Location: Daarsit
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
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.
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
General guide to database design: http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf
-
penciLneck
- Forum Newbie
- Posts: 15
- Joined: Fri Nov 28, 2003 10:13 am
- Location: Daarsit
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
I doubt you'd need a date table or a time table.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.
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
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?
or have I lost the plot?
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.
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.
Got interrupted while making my last post so didn't see this.penciLneck wrote:I guess the question I'm now trying to ask, is how would the relational linking between each table work?...
...
....
(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
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:
I guess the tables would look like this:
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:
this is how each event would look when displayed on the page.----------------------
title
----------------------
image | event text
----------------------
location, date, time|location2,date2,time2|etc to 6
----------------------
contact number
----------------------
I guess the tables would look like this:
is that right?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)
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.
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
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?
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?