Multiple junction tables

Question

I'm designing ms sql videocchat database and it's hard for me to design multiple many-to-many relations between two tables. My project requirements:

• There are users and rooms
• any user can create new room
• Each room holds a debate
• Each room can have observers or members (participants) of the debate
• Each room has one or more moderator (person who opens the room is a moderator by default and then he can nominate other users in the room to be moderators too)
• Each moderator has to be a member or observer (just like all users in room)
• There is a chat in each room where both observers and members can write messages
• only members can do videostreaming
• moderator can ban any user in the room (kick him out so that if he's on the banned list he can't go back to the room)
• banned list should contain information about which moderator gave a ban for which user and for what reason

I've ended up with database below but i feel like there is too much redundancy in my design(information about relation many to many between room and user) but at the same time I don't have an idea to simplify this. sql diagram picture

[edit]

UserStatus is information if user in room is observer or member

isStreaming entity is to be deleted (UserStatus describes it allready)

RoomStatus can be ignored

Answer 1

The only redundant tables IMO are Moderators and Banned.

Moderator is merely an attribute of a user (room user to be specific). You can just have one column, is_moderator in Room_Users table.

Banned is not required unless you want to use it as a log or there's a requirement to ban user at global level. In your case user will be banned at a room level. You can have columns banned_by and ban_reason in Room_Users which would have values only if room user is banned, else NULL.

As a side note: You need to re-think of your naming standards unless what you have used already is part of your organization's standards. Use room_user or RoomUser. This is purely my opinion and you need not follow it blindly.

Also I suggest on using Singular name for your tables. Table name should go with what an individual records indicate. User instead of Users. This makes more sense when your application code uses a property that holds collection of table records. For example, public IEnumerable<User> Users { get; set; } vs public User User { get; set; }. Use singular name for SQL tables is not just my opinion but kind of a standard too. You can do further reading on Google.