review ERD for website

Question

I am a noob and would like some help with the erd for a website I am building. There are roles for the users that participate in the board. I'm not sure I have that down correctly.

I realize that I posted this erd when I was pretty tired and my explanation is not clear. Let me try this again:

A user creates a board for a friend. This user is automatically given the role as manager. The person they create the board for is given the role as owner.

Anyone the manager invites is given the role as guest.

Each user except the owner can leave messages on the board.

An email is sent to the guests with a token informing them that they have been invited and a link is given for them to go to the board. The email also contains the login information.

The token can be used once, so that is why I have a field in the contributions table called 'token_used'. The user will have to log in the next time.

I am using Ruby on Rails which allows me with some code to create a virtual table for friends. Because a user is a friend and a friend is a user. This way I can create the friendships table and have a virtual friend_id to differentiate the two.

I can't remove the id from the contributions table to user only the user_id and board_id because this table is more than a join table as it has other fields for more information. Rails needs an id for tables like this so I can do other things.

I would appreciate any feedback about this updated version and how I can improve it.

I have removed those duplicate fields and put the roles into another table. I changed the name of the participants table to contributions because I think it makes it clearer. Maybe not.

The board can have only one owner one manager but many guests.

I hope this was clearer.

Thanks for any feedback.

Answer 1

You should write down the relationship between each table. Most of the time this is pretty obvious, but sometimes it helps to clarify things.
Not sure what greetings should do. However, you do have a bit redundancy in your Friendships table by saving someones name,email, password and created_at again.
The next redundant information would be in your Participants table. You do have a roles property including a value for owner. But you already do save the owner information in your Boards table.

I'd suggest to remove the owner property from your Boards table (that would allow to have boards more then one owner), remove the id property from your Participants as the UserID and BoardId are the keys for this table (due to the n:m relationship) as well as name,email, password and created_at from your Friendships table.

Answer 2

For scalability sake I would also make a different table for the roles. It's also reduces the redundancy and gives you a nice overview what type of roles are available.

Combine the participants and the user table and link the boards through an external linking table with the user_id and the board_id. This opens the whole thing up for scalability as well.

I don't understand why the link table for the friendship has the extra information? Is it to define a name for the friendship? Otherwise the friend should also become a user and a friend becomes a new role.

I hope this helped a bit. Good luck!