Multiple foreign keys per record in sql?

I'm creating an application (using PHP / Codeigniter / MYSQL) for tracking volunteers at events. I'd like multiple volunteers to be able to sign on to each event. I pl开发者_Go百科an on doing this using a table called signup which looks something like this:

TABLE SIGNUP
============

VolunteerId         EventId
-----------         -------
    12                223
    13                223
    15                223
    12                235
    13                235
    19                235

Both columns are foreign keys (to the primary keys of the volunteer table and event table respectively).

Is there a better way to do this? Should I use a compound-key as the primary key?

Honestly, I don't see a problem with the way you've set it up. Tables like this are commonly used to establish one-to-many relationships between different objects. I'm doing something similar in a table that references counties and cities in a given state. (Some cities span multiple counties.)

Database design best practices state that you should declare a primary key for a table. You don't have to do this; you can technically declare a table without a primary key. However, note that many DB engines will simply create a primary key for you behind the scenes if you don't specifically declare a key; this, however, may not be ideal for every situation (and generally isn't). Specifying a primary key of your choice is good for database optimization and organization.

Due to this, I'd say that you might as well use a compound key as your primary key for your many-to-many table instead of creating a separate index column. In this situation, this will satisfy the table requirements (as a db engine will make a primary key for you regardless) and it will prevent multiple occurences of the same pair, which won't do you any good in a many-to-many reference table.

Short answer: Go with the compound primary key - primary key(VolunteerID, EventID). You shouldn't go wrong.

One use for a compound UNIQUE key would be to prevent the same volunteer/event pair from appearing twice in the table. There's no need for a primary key for this.

A good discussion on why compound primary keys should be avoided: What are the down sides of using a composite/compound primary key?

Given the table you've described you have three choices

1 - lunchmeat317

SIGNUP
-------
VolunteerId (PK)
EventId (PK)

2 - Ted Hopp

SIGNUP
-------
VolunteerId (AK1)
EventId (AK1)

3 - ic3b3rg

SIGNUP
-------
SignUpID (PK)
VolunteerId (AK1)
EventId (AK1)

As Thomas pointed out the main difference between 1 and 2 is that Unique doesn't stop the following.

   VolunteerId EventId
   ----------- -------
   null        null
   null        null

However if these fields don't allow nulls to begin with (and the shouldn't) then they're exactly the same.

You could also add, as ic3b3rg suggests a Surrogate key (SignUpID). But as CJ Date notes (and I'm paraphrasing) introducing an artificial, surrogate, nonvolatile key will often be a good idea, but since its often difficult to determine volatility there's no formal way to know when you really need it.

That said as long as this table is is ...

• Tracking that volunteers have signed up for events
• There won't be any other attributes that have a functional or join dependency to R(VolunteerId, EventID)

... then in the immortal words of Yogi Berra "When you come to a fork in the road, take it" Meaning all three choices are valid and the choice probably won't impact your system one way or another.

Personally this is how I typically do it.

SIGNUP
-------
SignUpID (PK)
VolunteerId (AK1) (Not Null)
EventId (AK1) (Not Null)