Database schema design implementation feedback

Question

I'm having difficulty on designing a database schema design with the right relationships. I'm currently trying to design a database system where a question can be answer via a live_stream. The general idea is that a user can ask a question. Then a user can answer the question and set a live_stream. Many users can join that live_stream. Here is the diagram. https://dbdiagram.io/d/5f2a06ce7543d301bf5de918

enter image description here

I have added a link to the schema design where you can navigate the relationships. But basically the idea is as follows.

• A user can create many questions. user 1 -> * question

• A question belongs to a user

• A user can like a question user 1 -> * likes,

• A question can have many likes by users. question 1 -> * likes

• A question has one answer question 1 -> 1 answer

• and an answer belongs to a user user 1 -> answer

Here is something I have struggle with. Basically I have created a join table for a many to many relationship between users and answers. Using live_streams as the join table. The way I see it is that many users can watch the explanation of the live_stream for the answer and the answer can have many users watch the live_stream. Also I added presenter to the live_streams table. The presenter is also a user.

• Many users can join a live stream users 1 -> * live_streams

• A live stream belongs to an answer answers 1 -> * live_streams

• A user is a presenter of the live stream user 1 -> 1 live_stream

I'm still a but skeptical about the way live_streams table works as a join table between users and answers. Is still clear to me that many users can join a live stream. but not sure if the relationship between answers and live_streams should be a one to many, but I see it as live_stream belongs to answer.

The user presenting the live stream has to be the person who answered the question. So if the answer belongs to a user, that same user should be the presenter in the live_streams table. The answers table works more as setting the time, description and name of when the live stream is going to take place.

Is this correct? I'm open to feedback as to how to improve the design or if there is any errors in the logic.

• user212533: Does the user presenting the live stream have to be the person who answered it? And are we sure each question will have at most one answer, or is it one accepted answer.
• Steven Aguilar: @bbaird correct the user presenting the live stream has to be the person who answered it. Answer is more of a conceptual thing, which means the user that creates an answer is going to do the live stream.
• Steven Aguilar: For your second question And are we sure each question will have at most one answer, or is it one accepted answer. I was thinking it would be easier at first to just have one answer.
• user212533: Okay, I think I know what you are doing. Are you limited by a ORM that is really bad with composite (multi-part ) keys?
• Steven Aguilar: I will be dealing with a ORM, but honestly, I haven't design a system in a while so my implementation might have some errors.

Answer 1

Ok, given there are no restrictions on the behavior of someone asking a question, we can forgo a lot of trouble migrating the UserId through the data model.

I don't think you're too far from the mark, just some unnecessary Id columns that aren't providing value.

This should satisfy the requirements as you've described them:

enter image description here

Some notes:

1. I changed Like to Vote since that gives more flexibility.
2. If we do need to restrict the behavior of the person asking the question, we would need to use their UserId in the primary key, or change the primary key from QuestionId to something like (UserId,AskDatetime) or (UserId,QuestionNumber).
3. If you do choose to allow more than one answer per question, you would change the primary key of Answer to (QuestionId,UserId) (although you might want to rename UserId to something like AnswererUserId). To denote an answer as accepted, you'd need to create an AcceptedAnswer table where QuestionId is the primary key and AcceptedAnswer would have the relation to LiveStream.
4. The one 'oopsie' that could occur with this data model is logging that the presenter (the person who answered the question) also attended their own Livestream. If you were doing attendee counts, you'd need to query Answer to make sure you don't count them. If that is very mission critical, you'd move the UserId of the answerer to the primary key of Answer and migrate that through LiveStream to LiveStreamAttendee and place a check constraint to ensure the UserId of the attendee is not the UserId of the answerer.