Creating a set of players stored in SQL column

Question

Is there a way to store a set of players in a column in a SQL database?

I have an events table and I want to store all players who have entered that event in the table under a column named players.

The entrants will all be from the users table.

I have found the set attribute but this looks like you need to specify specific values which the set can contain. I know these at the moment, but in the future, the users table is likely to change.

so I want to,

• add a column to the events table called players that contains a set of users
• this should be empty initially but then as players are entered into that event, they are added to the set.
• from the java side of things, when I read the data from the database, it will take the event and display the name and then show all the users who are in the players set.

Is this the way I should be doing it? or is there a better way? i.e storing the users in an event in a set, storing this in some way in the database, and then reading this data back into the set in java before displaying it?

I am thinking I can store the id from the users table as a foreign key so in the set it will for example have users 1,3,4,7 in the set of players. Then when I get that data back I can display the users name in the web app.

Thanks

Answer 1

You have a multi-valued attribute. That is, a team has players, but there are multiple players in a given team.

But SQL naturally wants each column to store one value in a given row. This makes it easier to add a member with INSERT by creating a new row, or removing a member with DELETE of one row.

Because of the assumption that a column stores one value, the FOREIGN KEY constraint does not support comma-separated lists. Neither do simple expressions using = to compare a column to another value for searching or joining.

You should read my answer to Is storing a delimited list in a database column really that bad? for more examples.

Instead, a multi-valued attribute needs to be stored on multiple rows of a second table.

CREATE TABLE players (
  event_id INT NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (event_id, user_id),
  FOREIGN KEY (event_id) REFERENCES events (id),
  FOREIGN KEY (user_id) REFERENCES users (id)
);

Now you can add many users for a given event. A given user may attend many events. It's easy to insert a player, or remove a player. It's easy to count how many players for a given event, or how many events a given user has played at. And lots of other operations.

This is way better than using comma-separated lists.

This is the table design you arrive at if you follow rules of relational database normalization.