I need to create a database system wherein normal users can create questions with, only 2, belonging answers and these questions can then get a up- or down-vote by people visiting the site.
I am wondering in which way it would be best to create this database, specifically the tables required and their relations.
Within this site, there will be, at least, two user groups. Normal users and institution users. Institution users should be able to create a profile for their institution, normal users should then be able to choose an (already created) institution to belong to.
Each institution will per time basis have one question (with answers) assigned to them depending on which question on the site that has the most up-votes from normal users belonging to that institution.
The answers will, per question, be voted on. This will happen outside the database via sensors and the sensor will send numerical information representing number of votes each answer receives.
I am fairly new to creating databases and this is the first one I am creating alone. I have, below, tried to set up the tables that I think are relevant in this database system and their relations.
Tables:
User: UserID, UserName, UserEmail, Password
Question_Votes: QuestionID, UserID, UpVotes, DownVotes
Question: QuestionID, QuestionText
Institution_Users: UserID, InstitutionID
Institution: InstitutionID, InstitutionName, InstitutionEmail, InstitutionPassword, InstitutionAddress, InstitutionInfo
Institution_Questions: InstitutionID, QuestionID, IsActive
Institution_Question_Answers: InstitutionID, QuestionID, SensorAnswerID
Sensor_Answers: SensorAnswerID, SensorID, Answer1ID, Answer2ID
Sensor: SensorID, SensorLocation, SensorNumber, SensorAnswer1, SensorAnswer2
Answer1: Answer1ID, AnswerText
Answer2: Answer2ID, AnswerText
I have tried to normalize the database as much as possible, but being new to this, I might have failed miserably or made it more complicated than it should be. I might have made many mistakes, please do point them out, it's the only way I'll learn!