CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
bumperbox
bumperbox

Reputation: 10214

Database structure feedback

I have an app that collects data about schools, teams and players. there can be school, team and player level customized questions.

I have come up with 2 database designs, and i am just interested on the pros and cons of each design, before I go down that path.

Design 1

This design has separate tables for everything. the database design seems clear cut, but I expect there will be a lot of replication in the code to support the 3 sets of tables which are essentially the same.

Tables:

SchoolQuestions (id, questiontext, length, is_required)
SchoolAnswers(id, school_id, school_question_id, answer)

TeamQuestions (id, questiontext, length, is_required)
TeamAnswers(id, team_id, team_question_id, answer)

PlayerQuestions (id, questiontext, length, is_required)
PlayerAnswers(id, player_id, player_question_id, answer)

Design 2

This design stores everything in 2 tables.

The Questions.type field in is an ENUM('SCHOOL', 'PLAYER', 'TEAM')

In the Answers table, only one of the schoold_id, team_id or player_id can be not null.

This seems like the simplest solution, but does have redundant columns, so seems a bit messy

Tables:

Questions (id, type, questiontext, length, is_required)
Answers(id, school_id, team_id, player_id, question_id, answer)

Any feedback is appreciated, or suggest an improved design if you have a better solution.

Upvotes: 1

Views: 424

Answers (4)

samir chauhan
samir chauhan

Reputation: 1543

Definitely design-2 is better. But I think you can remove school_id, team_id, player_id fields from the answers table, since you have question_id there. With question_id you can relate answers to the question table. Well thats my inital thought. Please correct if I am wrong..

Upvotes: 0

Alex Aza
Alex Aza

Reputation: 78457

Design 2 is definitely better.

Also for the field type you might want to create a separate table, that will have 3 rows, and add foreign keys to Questions table to this Type table.

You will have a lot of problems with Design 1:

  • What if you decide to extend questions with one more field? Will need to update more than one table's schema
  • What if new type is needed? Will lead in new table creation.
  • You will not be able to build separate queries to query different tables instead of having one
  • You will need to union tables if you want to find out how many questions total

[EDIT] Missed player_id, team_id and school_id columns.

You could create Owner table, that would have owner type (school, team, player) referencing Type table. This way you would have only one OwnerId column in the Answers table.

Upvotes: 3

idstam
idstam

Reputation: 2878

I like the second one.

Since you know the type you can also remove the three ID columns and replace them with one generic ID column.

That way you can add questions for trainers or parents without changing the database.

Edit: ... and as other has suggested, add a table for Type.

Upvotes: 1

TheITGuy
TheITGuy

Reputation: 722

I personaly prefer Design2. My opinion however, is to have a separate table for QuestionType, and have a foreign key in Questions, to reference the particular question type of a question. Just helps to separate it out a little more. Secondly, i suppose, i am not a big fan of ENUM sort of types in the database.

Just my initial thoughts.

Good luck.

Upvotes: 0

Related Questions