2

I have a little question about database design :

Let's say that I have a "group" (of users) table. In this table there are basic fields with information about the group (creation date, name, etc) and a field that should contain the list of users of this group.

I have a "users" table, that contains information about users (really?). This table is not linked with the group table because a user is not forced to have a group.

In a group, a user can be "president" (only one) or "scrutineer" (several)

So in the "group" table, there will be a "president" field which contains the president user ID. And i need a "scrutineer" field that will contain the list of scrutineers IDs.

I don't know how to handle this list. Maybe store every ID in one text field, separated by a character (';' or '-')...

Making another table seems strange, because it's a really little part of the site.

How would you resolve this?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
FLX
  • 2,626
  • 4
  • 26
  • 57

3 Answers3

8

I don't know how to handle this list. Maybe store every ID in one text field, separated by a character (';' or '-')...

Don't do this. See Is storing a delimited list in a database column really that bad?

Making another table seems strange, because it's a really little part of the site.

That is the correct, normalised, way to represent a many-to-many relationship.

If a user can only be in at most one group (a many-to-one relationship), you could store their group membership in the users table (leaving the column NULL if the user is not in any group) and either flag in that table whether their membership is that of a president or scrutineer, or else assume scrutineer unless they are also the president in the groups table. However, in this case I'd probably still be tempted to stick with the additional table and define a UNIQUE constraint over the user column.

Community
  • 1
  • 1
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Looks like I was wrong, but what if it was a really big project ? If i do that, it will end with hundreds of tables ! Maybe my questions seems stupid, but I worked only for little projects. This is my first big thing and i'm affraid about performance problems. – FLX Jul 19 '13 at 14:13
  • @FC': Why would you end up with hundreds of tables? There would only be the three, no matter how many users & groups. – eggyal Jul 19 '13 at 14:15
  • In this case yes, this is a simplified example from my project. But I will apply this to every parts of the site, and I'm affraid of the number of table that I will have to deal with PHP. I read a bit about normalization, and you're right, but I don't know, I have the impression that I'm not doing the right thing. – FLX Jul 19 '13 at 14:24
  • @FC': It doesn't sound like it... large projects can indeed have a great many tables. There's nothing *per se* wrong with that. – eggyal Jul 19 '13 at 14:27
  • Ok, thanks. I accept your answer and upvote the others that were also good . – FLX Jul 19 '13 at 14:35
3

Assuming a user can belong to multiple groups, you end up with a many-to-many relationship. This is normally solved by creating an association table.

Groups
id, name

Users
id, email, etc.

Users_Groups
group_id, user_id
John Cartwright
  • 5,109
  • 22
  • 25
1

Like some have said, it's many to many, so what you need is an intermediary table to store the relationship the user has with a group. Every group a user is a member of is an entry in the intermediary table--which I'd call "membership", which is where I'd store the kind of membership. Presidency, etc., describes a user's relationship to a group, so a membership table is the logical place to store.

This kind of architecture becomes less daunting when you get comfortable with slightly more complex queries (such as joins)

Compare to your architecture--getting the ID of the president of a group

SELECT PresidentID FROM Groups WHERE GroupID=...

to:

SELECT UserID FROM Membership WHERE IsPresident=TRUE AND GroupID=...

Or the same query, only for IsScrutineer=TRUE--returns the UserID of all Scrutineers. Then this becomes much simpler to iterate over the members, no parsing involved.

User:
  UserID

Group:
  GroupID

Membership: (user is member of a group)
  GroupID
  UserID
  IsScrutineer
  IsPresident
j.i.h.
  • 815
  • 8
  • 29