[Resolved] users and groups - db design question

Cailean

I don't have any code yet so this is a bit of an abstract design question...

I have a set of authors and a set of groups. Each group has a number of pages that the authors have access to for adding/updating content.

When setting up my db tables and columns, what is the best way to store who has access to what? Groups can have more than one author and authors can have access to more than one group.

So far, I have a table called authors and one called groups both of which have 'id' columns. The only way I have come up is to have a column in the authors table called 'access' which would contain a comma-separated list of the group ids that author has access to. When the data is pulled an array would be created and a simple matching function would be used to determine what groups the user has access to. Is there a better way?

AstroTeg

You sound like you have a many-to-many relationship between authors and groups. This type of relationship needs 3 tables. One for authors, one for groups, and the last one to store the relationship between the two (usually 2 columns, one column being the ID of the author and the 2nd column of the group that author belongs to). To handle multiple authors to multiple groups, you just keep adding new rows defining 1 group and 1 author per row.

You never want to try storing some sort of delimited data in a field (unless of course you have a really good reason). This is a sign you have a database design problem. The workaround is to add an additional table to define the relationship.

Cailean

Originally posted by AstroTeg
To handle multiple authors to multiple groups, you just keep adding new rows defining 1 group and 1 author per row.

Ok, so it would look something like:

Author | Group
-------------------
  1    |   1
  1    |   3  

  1    |   5
  2    |   3
  2    |   6
  3    |   7

Once the author is identified, SELECT every row that matches the author id from the DB and then give access to the group ids that are returned? Sounds good to me!

You never want to try storing some sort of delimited data in a field (unless of course you have a really good reason). This is a sign you have a database design problem.

Which is precisely why I asked! 😉
My way just felt wrong... :queasy:

Thanks for the speedy help!