Database table that has many-to-many and one-to-many relationship

Question

In the interest of learning more about database design im was drawing up a database model, i choose to draw a simple database model for a social network website to keep a little more interesting than your average student/teacher/class models.

The question i have is about the relationships between the different tables.

Im not that good drawing these text database drawings like other peoples has on stack exchange ill try to just list the tables and explain the relationships, if its unclear i can try to draw a text drawing.

Database tables:

• User
• Friend
• Group
• Newsfeed

User has a one-to-many relationship to Friend and Group based on that one user can have many friends and a user can be a member of several groups and a group can have many users.

Friend has a many-to-many relationship with Group based on that one friend can be a part of many groups and a group can contain many friends. There is a one-to-many relationship to Newsfeed based on that one friend can have many newsfeeds.

Group has a many-to-many relationship with Friend based on that one group can contain many friends and one friend can be part of many groups. Group has a one-to-many relationships with Newsletter based on that one group can have many newsfeeds.

So now there is one many-to-many relationship and a one-to-many relationship in one table point to two other tables, is this correct ? Some part of this feels wrong, especially the Friend part but maybe im just misunderstanding something here. This might be a stupid database model but i need to ask stupid questions sometimes in order to get smarter at something. Ive read about and watch some videos about database relationships and to be they seem easy but when drawing this database model im getting confused since i suddently end up with a many-to-many and a one-to-many relationship in one table which seems weird.

Answer 1

This is how I'd start:

Let's say we have two groups, Group A and Group B.

groups
    id              unsigned int(P)
    name            varchar(30)
    ...

+----+---------+-----+
| id | name    | ... |
+----+---------+-----+
|  1 | Group A | ... |
|  2 | Group B | ... |
| .. | ....... | ... |
+----+---------+-----+

Let's say Group A has two newsfeeds and Group B doesn't have any:

newsfeeds
    id              unsigned int(P)
    group_id        unsigned int(F groups.id)
    name            varchar(30)
    ...

+----+----------+--------------------+-----+
| id | group_id | name               | ... |
+----+----------+--------------------+-----+
|  1 |        1 | Interesting Things | ... |
|  2 |        1 | Other Information  | ... |
| .. | ........ | .................. | ... |
+----+----------+--------------------+-----+

Let's say we have three users: Bob, Mary and John:

users
    id              unsigned int(P)
    name            varchar(30)
    ...

+----+------+-----+
| id | name | ... |
+----+------+-----+
|  1 | Bob  | ... |
|  2 | Mary | ... |
|  3 | John | ... |
| .. | .... | ... |
+----+------+-----+

A "Friend" is really just another user so let's create a table that allows many-to-many relationships between two users. My sample data shows that Bob is friends with Mary and John while Mary is only friends with John. (user_id and friend_id form the Primary Key)

users_friends
    user_id     unsigned int \_ (P) (F users.id)
    friend_id   unsigned int /      (F users.id)

+---------+-----------+
| user_id | friend_id |
+---------+-----------+
|       1 |         2 |
|       1 |         3 |
|       2 |         3 |
| ....... | ......... |
+---------+-----------+

Users can belong to many groups and each group can have many users so we need to have a table that gives us that many-to-many relationship. In my example data we see that Bob is a member of Group A and Group B while Mary and John are only members of Group B. (user_id and group_id form the Primary Key)

users_groups
    user_id     unsigned int \_ (P)(F users.id)
    group_id    unsigned int /     (F groups.id)

+---------+----------+
| user_id | group_id |
+---------+----------+
|       1 |        1 |
|       1 |        2 |
|       2 |        2 |
|       3 |        2 |
| ....... | ........ |
+---------+----------+

Finally we need a table that shows the relationship between newsfeeds and users. I haven't entered any example data here but this table works exactly like the users_groups table. Tables like this are called many different things, you can read more about them at Wikipedia. (user_id and newsfeed_id form the Primary key)

users_newsfeeds
    user_id         unsigned int \_ (P) (F users.id)
    newsfeed_id     unsigned int /      (F newsfeeds.id)

Answer 2

IMO, when thinking about relation modeling, one should remember about the 'direction' of the relation, otherwise it gets very confusing and also, one should remember every 'many to many' relation must have to be modeled using 'one to many'. Anyway, take a look here http://screencast.com/t/sJbPrvO53MS

even though it took a min to read the question...this was interesting problem...