Multiple users per event

Hi,

I'd like some advice on the design of a MySQL database that details an event and details about this event. Basically I have a table which lists time,date,location etc, but for each event recorded in this table there are up to 15 or so participants.

What is the best way of storing and retrieving this data?

Adding a field per participant to the table would contravene normalisation rules, but then if the participants are listed in another table, with a reference to the first table, how do you then doa SELECT to retrieve information about 1 event and all the participants in one single query?

Any information much appreciate, thanks,

Zammee

I'd say the best way of accomplishing that task will be having one field that is named participants and it will store each person that can be delimited by a certain characater or characaters. For example: John Doe|Mary Jane|Bill Gates (the | is your delimiter) You could use anything, but don't use a space if you store people by full name.

Then you select that event from the table and then you will have your server side language break it up. With PHP you can use either explode() or split(). Using split() will allow you to use regular expressions.

Or you can have a completely different table that stores the people by event ID. And you select the people involved in that event by the event ID using MySQL's WHERE clause. Hope this helps.

You've got the right idea... The way you would select both tables is like this:

SELECT events.name, users.name WHERE users.eventId = events.eventId

That's just an idea... I'm not sure what you're table/field names are... But if you're confused and need help, let me know!

Thanks Frankie,

What I'd like to achieve is to retrieve and all the associated s in one result, so in PHP all the information for one event, including all the users and the event details, would be stored in an array in the usual fashion.

A crude way of performing this might be but clearly this isn't a very good way of doing things!

Kind regards,

zammee

If I understand correctly, try this:


SELECT event.name, users.name WHERE users.eventid = event.eventid

That will display all the users that correspond to a specific event.

Frank,

Sorry not to have made myself a bit clearer :-

your method works of course, but you get one result row for each combination of event/user, so 2 events with 10 and 5 users each respectively would give 15 result rows.

What I'd ideally like is to have one result row per event, each row including all the users as well as the name of the event, so that for the numbers above there would be 2 result rows, although I don't know how the columns would look - this is where I'm stuck

This might not be a feasible proposition though, just let me know if I'm trying to do the impossible!!
Thanks very much,

zammee