Database schema design implementation feedback

database-designschema

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

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.

Best Answer

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:

Some notes:

I changed Like to Vote since that gives more flexibility.
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).
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.
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.

Related Solutions

Design Tagging module in the database

What you describe in your second paragraph sounds like the Entity Attribute Value pattern.

This is a known pattern for dealing with the type of data you are talking about. When you need to be able to be extensible, drive the types from the data and allow you to start tracking new types as you grow. It allows you stay flexible and agile and be a little more forgiving and generic in your database design and schema.

This pattern works, even in relational databases, but it does take more work to get good performance, and can cause you headaches down the line depending on how many rows, how many inserts you are doing and how frequently you query it. This SO Question talks about some of the pitfalls nicely in the firs answer listed with 20 votes. I have seen this model work well, especially in environments where the new types and entities really do come in and we want to let that be more application driven or user driven but we were fighting with performance more often in that model than a traditional relational database model but we needed the flexibility and we made it work. Definitely look at that SO question and do some more research on EAV and whatever DBMS you are using to find examples from others who have tried the same.

User system database design

Having the users and lost passwords in the same table implies a 1-1 (or 1-0/1) relationship; 1 user has 1 lost password (or none, if the column is nullable).

If that's the cardinality of the relationship, having them on the same table is correct. The school of thought that refuses nulls (which depending on the day, I adhere to), says that if the relationship is 1-0/1, you should:

users
  usersID primary key

lostPasswords
  usersID primary key references users(users_id)
  -- lost password columns here, non-nullable

The relationships look fine, however, I would suggest you use a diagramming tool which shows which are the columns referenced. Surprisingly, I've only found SchemaSpy doing this: http://schemaspy.sourceforge.net/sample/diagrams/summary/relationships.real.compact.png

Best Answer

Related Solutions

Design Tagging module in the database

User system database design

Related Question