Field contains foreign IDs for different tables

Question

I am developing a php/mysql driven facebook game. I am stuck on an element the table design. When a user completes a task I want to trigger any number of events.

I was thinking of something like so:

tbl_events
*event_id - serogate primary ID
*task_id - foreign ID of the task just completed
*event_type - what type of event e.g is it a facebook stream publish or a message to the user or does it unlock a new element of the game?
*event_param - this is where it gets tricky...

the event parameter is a problem for two reasons,

1) it will contain different foreign ids... dependent on the event_type and thus it will not be possible to join to x table. Meaning I would have to call two queries.

2) Most events require a single id or text, however some events require multiple parameters - like the facebook stream publish.

Answer 1

You could add multiple columns that are nullable. Then you can still satisfy the foreign key requirements by using the columns that apply to the different event types.

tbl_events
*event_id 
*task_id 
*event_type 
*event_param_for_message_to_user
*event_param_for_unlocking_game_feature
*event_param_1_for_facebook
*event_param_2_for_facebook

Answer 2

Generally speaking, you would make an Events table with just the criteria common to ALL the event types. Then you would make event_type tables with the fields specific to a particular event type.

Given that set-up, your PHP could have an Event Factory that can parse your input strings and determine which type of Event to instantiate. All the various Event types would use the same interface but would do what was required for their particular type.