Design Question

[RadioX]

I am working on laying out a database. Here is what I have so far (in example format of course)

tblOne
usertypeid
usertype

tblTwo
userid
username
userpass
etc

tblThree
groupid
groupname
etc

What I want to do here is allow users to join groups as members, or administrators etc. A user can also be a member of multiple groups and have different access to multiple groups (such as member of one and admin of another)

So on that note i set up another table

relAlltables
userid
usertypeid
groupid

And then write a query to join them all together based on that information

My question is is this a correct way to do this. Is there another simpler way or another way in general to do this.

Thanks

 

[nigelrivett]

Looks good.

To be consistent it should be
usertypename not usertype

Does a user have different attributes depending on what type they are acting as in a group/
If so you may need

userid
usertypeid
attributes

Doesn't matter but I would also swap round the entries in the conjoint table

userid
groupid
usertypeid

userid, groupid defines the group membership. usertypeid is just an attribute of that membership.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[RadioX]

Nigel

Thats for the suggestions. I wanted to make sure I was on the write track.

Thanks Again