Mysql Database design - checklist/quiz application

Question

hello,

I'm currently 'learning' codeigniter, php and mySql all at once as I try and create a "checklist" type of website, where visitors sign up, create a project and are asked a list of questions, where they must input certain fields

I'm a little stuck on my mySQL DB-design... (completely stuck actually)

what it comes down to is...

1 project can have one of 3 types. "type a/b/c"

Type a has 20 questions

Type b = type a + 15 questions

Type c = type b + 15 questions...

So basically I'd like to be able to easily say, Question A is Type A AND/OR Type B but not C.

Now, I don't know if what I'm doing is correct, but I'm basically making two tables and then setting a relationship between them "many to many" (i think?) n:m in mySQL workbench

ProjectType
id
ProjectTypeName

Questions
ID
Title etc...

ProjectType_has_Questions
ProjectType_id
Questions_id

---

Also, every question "CAN, not must" have 3 results (current facts) and 3 corrections (improvement on results).

I don't know if this is making any sense to anyone here, sure as hell is baffeling me :-/

any thoughts?

ps: I tried to attach an image but seeing as this is my first time using the site this wasn't allowed.

Answer 1

I'll respond to what I understand here. Had a few other questions that I commented on in your question.

If your project types will always aggregate -- that is, Type C is always a superset of Type B, which is always a superset of Type A -- then for the questions you can just specify the biggest project that that particular question will be included in. Say, if a question will appear in Types A and B, then designate the question as Type B. If it will appear in all three types, designate it as Type C.

You're committing yourself when you do this, though. A more flexible way to do this would be to have a linking table that includes the question ID and the project type ID. Each question can have one or more entries in this table.