R – Trouble getting started with SubSonic

activerecorddomain-driven-designormsubsonic

I have a domain model in my head, and i am having problems building a SubSonic compatible db schema, it would realy help me get started if you could tell me how you would go at it for this example with 3 entities (could be SqlServer OR MySql doesn't matter to me)

Subject– representing an educational subject (e.g. Trigonometry, Calculus).
Props- Name

Technique– representing a technique used to solve an exercise (e.g. Law of cosines, Pythagorean theorem)
Props- Name, FatherSubject

Exercise– representing a certain question.
Props- Subjects, Techniques (only those used to solve the exercise), DifficultyLevel (enum- easy, meduim, hard), Answer (int).

So:
Many to Many- Exercise -> Subject, Exercise -> Technique
One to Many- Subject -> Technique.
And DifficultyLevel is an enum.

I'll be sure to contribute to the docs once i'll start to get the hang of it.

Best Answer

The easiest way is to just build bridge tables between the many-to-many relationships.

Table: Subject
Columns: Subject_ID (PK)
         Name (UK)

Table: Technique
Columns: Technique_ID (PK)
         Name (UK)
         Subject_Name (FK)

Table: Exercise
Columns: Exercise_ID (PK)
         Difficulty_ID (FK)
         Question_Text
         Correct_Answer_ID (FK)

Table: Difficulty
Columns: Difficulty_ID (PK)

Table: Exercise_Answer
Columns: Answer_ID (PK)
         Exercise_ID (FK)
         Answer_Text

Table: Exercise_Technique
Columns: Exercise_Technique_ID  (PK)
         Exercise_ID (part of UK, FK)
         Technique_ID (part of UK, FK)
  • Subject to many Techniques
  • Exercise to many Techniques (via Exercise_Techniques)
  • Exercise to many Answers

By separating out the answers to another table and not using the correct answer ID as a int (as 2nd answer), you can randomize the answers and still know which one is the correct answer by comparing the answer_id value with the exercise.correct_answer_id.

Related Topic