Structure/Normalization questions

[Pikachu2000]

It's been a very long time since I've done much coding, and even then I wasn't a database guy (beyond being able to write a query if I needed to). 

I may be over complicating this, but I'm starting a small project for myself and want to use a DB for it. It will be a quiz/test engine, holding possibly as many as 4000-5000 questions. It won't likely ever be any larger than that.

It will need to hold:

• Question ID
• Question Type (either multi choice 1 answer, multi choice multi answer, or drag n drop)
• Question Text
• The text for each answer option or drag n drop item (average of 4 per question)
• A way to denote correct answer option(s)
• Filesystem link to a statically displayed image, if one exists for the Q
• Optional filesystem link to an image that can be opened by a clickable button on the question page
• Possibly one or two other pieces of info that I haven't thought of yet, lol

I'm thinking I need probably three or four tables.

One for question text and type

One for the answer text options, with a foreign key to the Question ID

One for image info, with a field to denote whether it's static or buttoned with an FK to Question ID

Possibly another table to hold the IDs of the correct answers with an FK to QID again? 

Does that sound about right, or have I completely missed the mark?

[requinix]

Generally, the first step for something like this is to figure out what sorts of "entities" you have. Don't think about it in terms of database tables: you're modelling the real world right now, not what you put into the software.
These things tend to stand out on their own, like you could look up the data for each one individually. "A question" and "an answer" are the two most obvious things; you could think of "an image used in a question" as one as well, but I don't.

Then you figure out relationships between your various entities and the pieces of data - "has one of", "has many of", that stuff. If you could have more than one of a thing then it almost always needs to be a separate entity (if it wasn't already).

So here, I think

1. Questions
   • ID
   • Type
   • Text
   • Image path
   • Whether the image is inline or opens separately
2. Answers
   • ID
   • Question FK
   • Text
   • Maybe a priority/sort order for arranging the answers
   • Whether it's the/a correct answer

The questions and the answers are obviously their own logical entities. I don't think the images are, like I said, unless you wanted to reuse images for multiple questions. You could still do it if you really wanted to, of course.

The relationships are "each question has one-or-more answers" and "each answer belongs to one question" (I assume). That means answers must be their own entities, but that was already established. The fact that it's one-to-many means you don't need an association table and can just use regular FKs; if you were to decide that "each answer could belong to multiple questions" then you have a many-to-many and be forced into creating that third table. Likewise, I wouldn't say you need a separate table to associate a question's correct answers: the relationships there are "each question has one-or-more correct answers" and "each correct answer belongs to one question" (unless not, but this one's even less likely), which is identical to their general relationship and so fits into that system naturally without needing anything more than an "is a correct answer" flag.

[Pikachu2000]

Ok, thank you. The reason I was thinking of a separate images table is that only about a third of the questions will have any image at all, and some may have one type, the other type or possibly even both types. I didn't want to leave a bunch of empty fields in the question table, but if that doesn't matter I'm fine with it. My thinking was along the same lines with a table for correct answers, but again if it doesn't matter, it doesn't matter.

 

EDIT: It's also possible any one question could have an unknown number of either/both image types.

Edited August 18, 2023 by Pikachu2000

[requinix]

10 minutes ago, Pikachu2000 said:

Ok, thank you. The reason I was thinking of a separate images table is that only about a third of the questions will have any image at all, and some may have one type, the other type or possibly even both types. I didn't want to leave a bunch of empty fields in the question table, but if that doesn't matter I'm fine with it. My thinking was along the same lines with a table for correct answers, but again if it doesn't matter, it doesn't matter.

I've heard opinions that if a piece of data (read: column) will often be null then it should be moved into its own table. I don't agree.

 

10 minutes ago, Pikachu2000 said:

EDIT: It's also possible any one question could have an unknown number of either/both image types.

Ah, then that means a one-to-many relationship and you're forced into creating that third table.

[Pikachu2000]

26 minutes ago, requinix said:

I've heard opinions that if a piece of data (read: column) will often be null then it should be moved into its own table. I don't agree.

 

Ah, then that means a one-to-many relationship and you're forced into creating that third table.

The part about nulls was what I remember hearing from several people too, but I don't know enough about DB structures to agree or disagree, haha. 

 

Anyhow, this is what I've cobbled together for now. Any suggestions, or is it an OK starting point?

[Barand]

That's one half of the database. What you are calling "answers" I prefer to think of as "options".

The "answers" (selected options) will be provided by those who take the quiz/test and stored. This gives another couple of entities, answer and student (assuming a student test scenario)

[Pikachu2000]

Thanks, Barand. Yes, I see where options would be more accurate. This is something I'm making for my own use, at least for the time being. My Cisco Systems lapsed quite a while ago, and I'm studying to take the tests again. I find that doing things like this as part of the studying process helps me retain more of the information.

I suppose if I feel I've done an amazing job of it, I might offer it to others in the future. I'll structure it so adding the user related tables in the future will be possible.

[gizmola]

Barand as usual offers an expert solution.  

Looking at your original schema model, please don't include a prefix for the table name like q_column etc.  

Also don't use Enums.  They violate a basic tenet of relational database theory (any field/attribute in a row should contain a single value).  If you really want category and type, those are foreign keys to separate tables.

In cases where the PK is a char, I will tend to name that column "code", just to be clear that it's not a sequential key.  For example, I often have fairly static type tables, like "status" where the allowable values are things like "new", "active", "deleted", and I'll use a char(1) and 'N', 'A', 'D' for those values in a status table with "code" and "description" as the only fields.

I personally have done what Barand did (using table_id) many times, primarily to make it easier for the design tool(s) I typically used, but most ORM's like it if you just name your table pk "id".  When you add a foreign key, then make that "tablename_id".  

A lot of ORM's will pick up on naming conventions for keys, so it makes things easier, as the ones I've used will default to the assumption that the "id" field is the primary key for the table.

Not a huge fan of these types of columns, but created_at, and updated_at are good for timestamps like your "q_added".

 

[Pikachu2000]

Hi Gizmo! Thanks for the input. I dumped the prefixes from the field names. You're right, they're unnecessary. I don't have any PKs that are anything other than INT and AUTOINCREMENT. With the relatively small number of records I expect and my lack of database expertise (and the fact it's been about ten years since I've attempted to do any coding, lol) I'm gonna avoid using separate tables for the category and type values. Maybe if I feel more confident as I work my way through this, I'll change my mind . . .

[Pikachu2000]

Anyhow, I hope everyone has been well for the time I've been inactive. It's good to see a lot of the same names around here.