database layout question

Question

database layout question

Blair Dee

SSCommitted

Points: 1648
More actions
April 25, 2005 at 2:39 pm

#164250

i am in the process of creating a company intranet and i would like the ability to create groups and i am thinking how to layout my database tables.

i was thinking something like this:
tblUsers:
ID
Username
Password
Fname
Lname
Email
tblGroups:
ID
Name
Descrip
but what i am wondering is the best way to assign a user to groups. do i add a groups field to tblUsers which holds a comma separated list of group memberships?
or do i have a 3rd table like this:
tblGroupMemberships:
ID
userID
GroupID
it seems like this would require my application to make more calls to the database so perhaps its not the best way? so is the comma separated group list in the user table the best way or is there another way i am overlooking?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SumOfDavid Ten Centuries Points: 1357 More actions · Answer 1

Use the 3rd table (called a many to many, or relationship table). This way it's easy with one select to see which members are in a group, or which groups a member belongs to. Plus, it's truly normalized that way. It's also possible to retrieve the results as properly formatted xml which you can then cache with your application.

David

ron k SSCertifiable Points: 6103 More actions · Answer 2

"...so is the comma separated group list in the user table the best way..."

Bite your tongue! Pretty much listen to what David said although I think you want your third table to just be:

UserId

GroupId

Adding the extra ID (I assume as a PK) would only allow for duplicate UserId/GroupId entries. I think you want the Primary Key to be the combination of UserId & GroupId

SumOfDavid Ten Centuries Points: 1357 More actions · Answer 3

Actually, my preference is to keep the extra ID. I usually will have it be an IDENTITY value. I DON'T use it as a Primary Key though. I do as Ron K suggested and use the combination of UserID & GroupID as the Primary Key.

What the extra ID allows you to do is to have one field in which you can reference a row easily. It's easier to say DELETE FROM <table> WHERE ID = xxx, vs DELETE FROM <table> WHERE UserID = xxx AND GroupID = xxx.

Just my personal preference though.

David

Blair Dee SSCommitted Points: 1648 More actions · Answer 4

thanks everyone for your replies - that helps a lot but i do have another question.

it will probably seem dumb but i am (sort of) new to the concept of querying multiple tables in one query. i know thats basic stuff but i just haven't had to to do that much until recently.

anyway -

Blair Dee SSCommitted Points: 1648 More actions · Answer 5

wow that's weird - the second of my previous post didn't post. here's what i meant to say:

thanks everyone for your replies - that helps a lot but i do have another question.

it will probably seem dumb but i am (sort of) new to the concept of querying multiple tables in one query. i know that's basic stuff but i just haven't had to to do that much until recently.

anyway -

if i wanted to find out the names of the groups userID 7 belonged to, in the past i would split it into 2 queries like this:

1) select * from groupMemberships where userID=7.

2) then i would loop through the results of the first query and each time through have a second query that says:

select name from groups where id = query1.groupID

how would i go about combining those 2 steps into one query that would return the list of group names?

ron k SSCertifiable Points: 6103 More actions · Answer 6

Select g.name

From tblGroups g

Join tblGroupMemberships gm

On gm.groupId = g.GroupId

Where gm.UserId = 7

Or the same thing turned around

Select g.name

From tblGroupMemberships gm

Join tblGroups g

On g.GroupID = gm.GroupId

Where gm.UserId = 7

SumOfDavid Ten Centuries Points: 1357 More actions · Answer 7

With the relationship (many to many) table, it's probably best to first create a generic view. Then your queries would look something like this:

View

CREATE VIEW v_UserGroup
AS
SELECT
u.ID UserID,
u.UserName,
u.Password,
u.FName,
u.LastName,
u.Email,
g.ID GroupID,
g.Name,
g.Desc
ug.ID UserGroupID
FROM tblUsersGroups ug
RIGHT OUTER JOIN tblUsers u
ON ug.UserID = u.ID
RIGHT OUTER JOIN tblGroups g
ON ug.GroupID = g.ID

This view will give you all your data. The reason for the outer joins is that you will be able to see Groups that have no users, and Users that don't belong to a group. You may want to change it to an inner join if your requirements are different.

You then only need to have a query do something like:

1) SELECT Name, Desc FROM v_UserGroup WHERE UserID = 7 (This will get you the groups that user 7 belongs to)

2) SELECT FName, LName FROM v_UserGroup WHERE GroupID = 2 (This will get you all the users for Group 2)

David

Blair Dee SSCommitted Points: 1648 More actions · Answer 8

ron k -

when you say select g.name i assume g represents tblgroups?

and in the next line, the from clause you say from tblgroups g. why the g after the table name?

SumOfDavid Ten Centuries Points: 1357 More actions · Answer 9

The g represents an Alias. Instead of having to type tblGroups all the time, I can just type g. You'll also noticed I aliased the two different ID fields. (u.ID = UserID, g.ID = GroupID)

David

Blair Dee SSCommitted Points: 1648 More actions · Answer 10

so in the from clause where you say tblgroups g, that is what tells the server that g is an alias for tblgroups?

ron k SSCertifiable Points: 6103 More actions · Answer 11

Yes! From Books online.

FROM

Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements.

Syntax

[ FROM { < table_source > } [ ,...n ] ]

< table_source > ::=

table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]

| view_name [ [ AS ] table_alias ] [ WITH ( < view_hint > [ ,...n ] ) ]

| rowset_function [ [ AS ] table_alias ]

| user_defined_function [ [ AS ] table_alias ]

| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]

| < joined_table >

....