Finding a linking table for a DB UML

Databases
1

I’m working on a small college DB project which requires creating a UML type diagram for a DB. My project will be on a forum DB and have tables and attributes as follows (simplified version);

Member I[/I]
Username I[/I]
Password I[/I]
Registration IP Address I[/I]
ID
…

Usergroup I[/I]
Name I[/I]
ID I[/I]
…

Because there is a many to many relationship between these tables (each member can be in more than one usergroup and each usergroup can have more than one member), I need to insert a “linking table” between them.

I’m considering using “Display Usergroup” as the linking table (with name and ID as attributes), on the basis that each member can only have one Display Usergroup and each Display Usergroup can only have one Usergroup.

Does that sound logically correct, or am I on the wrong track re the linking table?

Thanks!

2

you’re on the right track with the linking/association/relationship table, but your cardinality is a bit off

“each member can only have one Display Usergroup” – nope, multiple

“and each Display Usergroup can only have one Usergroup” – correct

i would name the table Usergroup_Members – and it would have only two attributes, member and usergroup

3

Thanks for your reply r937.

Why do you say “multiple”? My understanding was that a member can only have one Display Usergroup at any one time, thus making it a one to many relationship?

Thanks for the suggestion, I do like that idea.

BTW, I’m reading your Simply SQL book on Kindle and it is very helpful.

4

that understanding was not conveyed very well

in your first post you said “each member can be in more than one usergroup”

a user would have multiple rows in “Display Usergroup” or Usergroup_Members, the relationship table – one for each usergroup

but even if you stipulate “only one at a time” i would still model it the same way

5

My apologies r937, you are right, I didn’t explain it very well in my first post. The original two tables I was working with were as follows;

Member I[/I]
Username I
Password I
Registration IP Address I
ID[/I][/I][/I][B][I][I][I]
…

[/I][/I][/I]Usergroup (table)
[/B]Usergroup Name I
ID I
…[/I][/I]

Members of a forum can be in more than one “Usergroup”. However only one of those usergroups will be displayed on their profile and this usergroup becomes their “Display Usergroup”. That’s why I felt that a “Display Usergroup” would be a good table to use to link the two tables above. The Display UserGroup table might look like this.

Display Usergroup (table)
[I]Member Username (Foreign Key attribute from the Member Table)
Display Usergroup Name (attribute)

[/I]​Does that seem logical?

6

almost :wink:

if the tables have ID columns, presumably those will be autonumbers of some kind, likely primary keys, then your linking table should use the IDs as foreign keys, not names

7

Thanks for your help. I’m taking your advice and doing it that way.

With regards to my Usergroup table;

Usergroup (table)
Name (attribute)
ID (attribute)

The “Name” attribute is unique, i.e. no two Usergroups will have the same “Name”. The same applies to the Username (attribute) in the Member table, i.e. no two Members can have the same Username. How can I justify including ID attributes given that both of the tables have unique attributes that could conceivably be used as primary keys?

8

  1. saves space on the relationship table, thus more rows per physical block on disk, thus faster reads, plus, the indexes are smaller, thus faster searches

  2. allows a username or membername to be changed without needing to cascade the changes to the relationship table

9

Thanks a million r937, just what I needed!