[Database] What's the best way to store "weird" custom data

[Drown]

Hello everyone,

 

First off, sorry for the not-so-explicit title, I will try to describe the situation as cleary as possible.

 

I'm making a simple website where users can create tests. A test is made of a series of pages (typically 5-6) with multiple fields (from 3 to 10), and each field consists of 3 radio buttons (yes, no, N/A). Basically, for one example test with the maximum number of pages and fields, we have 6 pages of 10 fields, times three options, so 180 possibilities for that test only. There are a couple of tests, and I would estimate the number of different combinations of results at around 500-600.

 

I have no idea what the best way is to store the results. Why do I want to store them? Because I'd like to allow the users to save their progress after each completed page, so that they could continue later. Then, once the test is done, they get a note/% that depends on the answers - like any test. How can I store the results (ex: page 1 - field 1 - answer = yes .... page 1 - field 2 - answer = n/a) in a database?

 

I had the idea to have a 60-digit code (for a test of 6 pages / 10 fields per page) which is originally set to 0000000[...]0000 (basically, 60 zeros) where 0 stands for no answer yet, and then I would adjust the first digit to 1 if he answered yes to field 1 of page 1, 2 if he answered no, 3 if he answered N/A. (it would be a 60 char string basically)

 

However, considering that each test consits of a different number of pages with a different number of fields, each test category would need its own way to manage that code, and I have no clue how I could do that. I'm not even sure that the solution is even doable to start with.

 

What is a good way to store such results in a database?

 

If you need more information let me know!

 

Thanks all for your help.

[Ciccioo]

(assuming you're willing to work on a relational database)

 

your string idea doesn't look bad to be, because you would have all the results of a single person for a single test in a single char(60) type field which will just need a single fetch to estrapulate

the Results table could be something like

(person id, test id, answers string)

with (person id, test id) as primary key

 

you could have a Tests table from which you could retrieve the data about the specific test you're checking, to know how many pages and how many fields in each page there are

does every page have a different number of fields?

 

the only alternative i can think about right now is the more pure relational way (imo) in which you store every answer for every field in a different row of a Answers table that would be something like

(person id, field id, answer)

but i think that it would be much more work for no really significant gain

[Drown]

@Ciccioo

 

Thanks for the help! I think that the best thing to do is start working on it (for something simple, ex: 2 char string instead of 60 char string) to see if it works in practice, and how much work is involved.

 

 

does every page have a different number of fields?

 

Yes, unfortunetly. Well, for the same "test category" (ex: test_finance) all the pages will have a fixed number of fields (ex: always 3 fields for page 1, always 2 fields for page 2, always 5 fields for page 3), but then another test (ex: test_cooking) will have a different number of fields (ex: always 2 fields for page 1, always 7 fields for page 2, etc).

 

 

the only alternative i can think about right now is the more pure relational way (imo) in which you store every answer for every field in a different row of a Answers table

 

That's the other possibility yes. It's less complex (only the name of the field, the result, done) and doesn't require custom functions to "decrypt" it (like the char string) but it's more work as you need a couple of tables with several dozens of entries each.

 

How big is the performance impact of having several hundreds extra entries in a databse? Most of them would be TINYINT (0 for null, 1 for yes, 2 for no, 3 for N/A).

 

Thank you again for your help.

 

 

 

[Ciccioo]

Thanks for the help! I think that the best thing to do is start working on it (for something simple, ex: 2 char string instead of 60 char string) to see if it works in practice, and how much work is involved.

doing it for any N number of chars should take the same amount of time if you write code flexible enough (which language?)

 

Yes, unfortunetly. Well, for the same "test category" (ex: test_finance) all the pages will have a fixed number of fields (ex: always 3 fields for page 1, always 2 fields for page 2, always 5 fields for page 3), but then another test (ex: test_cooking) will have a different number of fields (ex: always 2 fields for page 1, always 7 fields for page 2, etc).

so, you can describe all of this with a table like

(category id, page number, fields number)

so if you query all the entries for category_id = finance, the amount of results will be the number of pages, and every row will tell you the number of fields for that specific page

 

How big is the performance impact of having several hundreds extra entries in a databse? Most of them would be TINYINT (0 for null, 1 for yes, 2 for no, 3 for N/A).

i don't think that you have to worry about performance as long as you stay under the millions entries, as long as you use indexes right and as long as you use fixed-size fields

 

 

i don't think that the second method is more simple either, as you would need other tables to list the fields of every test and so on, and you would have to do some more joins

if you're sure that you can impose the limit of 60 questions for each test, i would go for the first solution

[Drown]

 

doing it for any N number of chars should take the same amount of time if you write code flexible enough (which language?)

 

I'm using PHP and MySQL, with a bit of jQuery for some stuff.

 

 

i don't think that the second method is more simple either, as you would need other tables to list the fields of every test and so on, and you would have to do some more joins

if you're sure that you can impose the limit of 60 questions for each test, i would go for the first solution

 

I'll start working on that then.

 

Thanks for your help, it's really appreciated.

[Ciccioo]

I'm using PHP and MySQL, with a bit of jQuery for some stuff.

I'll start working on that then.

Thanks for your help, it's really appreciated.

you're welcome!

just be sure to use char(60), not varchar or text or anything else, as those would make your records of variable length, which is a performance killer

not that it would be a problem as long as the program is not used intensively by a lof of people, but you know, it's just a better done job

[Dredgy]

@Drown Take the value of the input as an array, then serialize() it with PHP and store it.

 

EDIT: Terribly worded response on my part, I'll give you a demonstration (bit rusty and this isn't tested, but should work):

 

HTML:

<form action="form.php" method="post"><fieldset>    <input type="radio" value="yes" name="answer[0]" />    <input type="radio" value="no" name ="amswer[0]" />    <input type="radio" value="yes" name="answer[1]" />    <input type="radio" value="no" name ="amswer[1]" />    <input type="radio" value="yes" name="answer[2]" />    <input type="radio" value="no" name ="amswer[2]" /></fieldset></form>

PHP:

<?php//Remember to do your validation and sanitization of results here.$answers = $_POST['answer'];/*$answers will be similar toArray(     [0] => 'yes',     [1] => 'no',     [2] => 'yes')*/$answersToStoreInDatabase = serialize($answers); //Makes the array a string for easy storage?>

[Drown]

Thank you @Dredgy, this looks like a simple solution.

I haven't spent much time working on this (it's a side project) but I'll get to work soon and we'll see how it goes.