Best way to organise this Database, for an events based site

[eluser]JamieBarton[/eluser]
Hi guys,

I'm struggling with getting my head around a database, I've got one set-up at the moment, but I don't think I'm doing it right, so I'm looking for a new approach and start fresh.

The web app I'm trying to create is for an events based site, which stores the event details such as date and time, then the venue for the event and the city of the where the event/venue is.

I want to be able to go to an address like:

- eventwebsite.com/events/los-angeles/ = This will shows all events within los-angeles as the City.

- eventwebsite.com/events/los-angeles/event-name = This will show the event

- eventwebsite.com/events = This will show all events... Later be able to sort them by date, upcoming events etc..


Because the site is going to be interactive, users can sign-up and post events to the website, however if a venue doesn't exist, the user must still be able to post the event, but just type in the venue, if it's not on the list..

Then I need to store the City of the event..

So, would I store the City along with the venue, because really the venue isn't going to move Cities, however, if the venue doesn't exist on our website, there is no way to find the City, so should there be two fields for City & Venue and store them just in a varchar, instead of linking it to an ID of the City or Venue from their own tables..? OR i could just get a pop-up to say create a venue if it doesnt exist.

I'd still like to know how to organise everything by by city/venue

Really struggling to grasp how to create the database because users must still be able to post events, if the venue doesn't exist (as made by the admin).

Have a look at http://eventvibe.com which is along the same sort of lines..

Any help would be appreciated..

[eluser]fraserk[/eluser]
I have developed a similar site. Going Clubbing

I have a table for Events, Venues, and Events Dates. I join all the tables by event_id. When a use try to post and event, if the venue is not in the database they can add it themselves.

[eluser]JamieBarton[/eluser]
What sort of stuff did you store in the event dates table?

[eluser]fraserk[/eluser]
I needed the ability to create repeating events, Weekly and Monthly. SO I just stored the dates of the events.

[eluser]Victor Michnowicz[/eluser]
I like to go crazy with normalization. I would probably do something like this:

Code:

States
-----
id
abbr
name

Cities
-----
id
name
state_id

Venues
-----
id
name
city_id
user_id (default to NULL if not created by a user)

Events
-----
id
date
time
venue_id

If a user can not find the right venue, he can select the correct city and state and fill in the venue name himself.

I know you can get free databases will all US cities and zip codes for free online. I have used one before and this may have been it.

Keeping track of what venues are user-inputed may be handy down the line depending on how you want to develop the application. For example, you could only display non-user-created venues in the event creation section.

There is a ton of room for advanced functionality too. Like what if a lazy person does not want to scroll through all the venues in San Diego and hits "other" and just types in the name of the venue that is already in the database? You could do a check for all matching venue names before you add a new one to the database.

And auto completion is always cool too...