Newbie needs help with Nomalization

tikal

I thought I understood normalization but I can't think how to resolve many-to-many relationships.

I want to create a series of diaries for various groups and users:

Users: Individuals who can have their own diary and can belong to none or many Groups.

Groups: Have many members but only one diary. All Group members have view privileges and one or more may be an Owner of the Group and the Group Diary (i.e. multiple administrators).

Diaries: User diaries can be viewable by other Users if the owner grants a view permission. A Group has one diary that can have many members with view only priviledges and many Owners(administrators).

I started with three entities Group, Diary and User although I wasn’t sure if User is really just a Group of one. This means there are many-to-many relationships between Group and User, and between Diary and User. I tried coming up with other entities like Group Member List, Group Owners, Diary Owner etc but always ended up with a many-to-many relationship.

What is the name for an entity/table that breaks up many-to-many relationships?

Suggestions on how I should break these down gratefully received. As are any URL’s to normalization tutorials that explain removing many-to-many relationships well and with examples.

Many thanks,
Steven.

jstarkey

Here you go:

http://phpbuilder.com/columns/barry20000731.php3

Check out the comments as well - there's a lot of good info in there.

pete_bisby

You said that each User has a Diary, and that each Group also has a Diary. So, each Group can be defined as a User.

Between User and Diary you can have an entity called 'Viewing Privileges', which will help you to define which User/Group has viewing/owner privileges to which Diaries.

So, you have a one-to-many relationship between User/Group and Viewing Privileges, and a one-to-many relationship between Diary and Viewing Privileges.

Hope this nudges you in the right direction.

edwardsbc

I think you are looking for what I call a "join table". This is an intermediate table which contains primary keys from two other tables:

Table: groups
Fields: groupid, description, etc...

Table: jtbl
Fields: jtblid, groupid, diaryid

Table: diary
Fields: diaryid, description, etc...

You can now create an entry in jtbl for each diary a group can access. You can retreive the relavant relationship with JOINs. This is nice in that you can go both ways. You can create a select to see all diarys belonging to a group, or you can create a select to see all groups with access to a diary.

tikal

Thx for the replies.

If I went down the route of using the Permissions table itself as the join/bridge table, and then wanted to extend my little app to include say a 'to do' list would the following be good/valid?

Table: User
Fields: userID, userName, ...

Table: Diary
Fields: objID, diaryLabel, ...

Table: ToDo
Fields: objID, listLabel, ...

Table: Permissions
Fields: permID, objID, userID, permType, ...

So:
User to Permissions is a one-to-many
Diary to Permissions is a one-to-many
ToDo to Permissions is a one-to-many

I wanted to have a single 'permissions' table but doesn't this way run the risk of a User joining a Diary that had the same objID as a 'to do' list they already had 'permissions' on, which would cause data anomilies?? (correct phrase?)

The other way would be to have a separate 'Permissions' join/bridge table between the User and each type of component/object (Diary, To do list, URL Manager etc.) but to have a Users permissions scattered all over the schema seems wrong for some reason!?

apparently confused by basic set theory

Many thanks for any help 🙂