Trouble getting started with SubSonic

Question

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.

Answer 1

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.

Answer 2

I'd say to use SimpleRepository: http://www.subsonicproject.com/docs/Using%5FSimpleRepository

I'm about to update it with Auto-Migrations for ManyToMany associations which will generate the tables/constraints you need. I'm putting it through it's paces right now so I need a few weeks.

You can email me for the code if you like (without migrations) which is myname @ gmail.