Alter Database

[jotoeri]

Hi all,

i've got a quite heavy proposal, about altering the Database. But maybe its worth to do it now, rather than in a further state of development. I would appreciate your opinions.

Background

Looking into the Database, it took me quite a while to understand the sense of the tables. And especially for the tables of questions and votes, i would have expected some relation, but except the form_id it was hard to find some:

• In the questions-table, the questions are stored just with the corresponding form_id, their text and a app-wide question-id.
• Within the votes-table however (containing the submittted answers), the form_id is still related to the form, while a "vote-option-id" seems to indicate a form-wide question-id, which is not related to the questions-table. Instead, the Questions Text & Type are just stored a second time with the answer, producing kind of a duplicate of the question-table for each submission of the form.

oc_forms_questions.png
oc_forms_votes.png

My Proposal

So - my Proposal would be to alter the database - and now that one is on to do so - to produce a relational database with intuitive naming. Of course - it would be a heavy change to do so, but doing it now, would make further development quite easier, while in a later state of development, there would be more functionality to alter.

A possible target-database could be:

• oc_forms_forms
  -> [form_id, hash, title, description, owner, created, access, expire, is_anonymous, full_anonymous, ans_unique]
  -> unique(form_id)
• oc_forms_questions
  -> [form_id, question_id, type, text]
  -> unique(form_id.question_id)
• oc_forms_options
  -> [form_id, question_id, option_id, text]
  -> unique(form_id.question_id.option_id)
• oc_forms_answers
  -> [form_id, submit_id, question_id, user_id, answer]
  -> (form_id.submit_id) forms a unique identifier.
• oc_forms_notif
  -> [id, form_id, user_id] (just as until now)

Corresponding changes

oc_forms_forms

• Rename table 'oc_forms_events' to 'oc_forms_forms'
  -> The table contains in fact the forms, so why calling it events?
• In new 'oc_forms_forms' rename column 'id' to 'question_id'
  -> Just to use the same naming withing db
• In new 'oc_forms_forms' rename column 'unique' to 'ans_unique'
  -> unique seems to be an (even deprecated) mysql-keyword. The column indicates if a user is allowed to answer the form once or multiple times.

oc_forms_questions

• In 'oc_forms_questions' add column 'question_id'
  -> Adding the column as kind of sub-id to 'form_id'.
  -> (form_id.question_id) makes an intuitive unique identifier for the question.
• In 'oc_forms_questions' rename column 'question_type' to 'type' and 'question_text' to 'text'
  -> Just to have a common naming across the tables (naming without table-name).

oc_forms_options

• Rename table 'oc_forms_answers' to 'oc_forms_options'
  -> Table contains the Options for Radio-Buttons, Checkboxes and Dropdown.
• In new 'oc_forms_options' add column 'option_id'
  -> (form_id.question_id.option_id) forming an intuitive unique identifier for the option.

oc_forms_answers

• Rename table 'oc_forms_votes' to 'oc_forms_answers'
  -> This table contains the submitted answers. In the sense of "There is a question" -> "I give an answer", this naming is more intuitive.
• In new 'oc_forms_anwers' add columns 'submit_id' & 'question_id'
  -> (form_id.submit_id) form a unique submit-identifier, while (form_id.question_id) form a unambiguous relation to 'oc_forms_questions'.
• In new 'oc_forms_answers' rename column 'vote_option_id' to 'question_id'
  -> This column seems to contain already the data of the new 'question_id'.
• In new 'oc_forms_answers' rename column 'vote_answer' to 'answer', as 'votes_' is now deprecated.
• In new 'oc_forms_answers' remove columns 'vote_option_text' and 'vote_option_type'
  -> These are stored within the table 'oc_forms_questions', related by (form_id.question_id).

What i can't surely answer

• Should the tables still contain a Column 'id', beeing an id within each table, or could the respective combination like (form_id.question_id), (form_id.question_id.option_id), (form_id.submit_id) directly be used as unique identifier within the tables?
• The formerly tables 'oc_forms_answers' and 'oc_forms_questions' contain a column 'timestamp', which seems to me not to be used yet. I left them out for now, but if necessary, one would have to include them.
• The transition from currently used Forms-App-Installations to the new database could be tricky, as the new scheme would have to be applied to the old submitted data.

That is quite heavy, so why should we do so:

• It produces a true relational database, avoiding duplicate information and connecting the data.
• The new naming is much more intuitive, which makes development much easier.
• Such changes should be done as early as possible, as further development relies on or would have to be adapted.

So - this all makes sense to me. - What do you think about? ;)

Greets,
Jonas

[marcelklehr]

Looking into the Database, it took me quite a while to understand the sense of the tables.

I was a little baffled by the chaos as well. I support your effort to clean up the db schema!

It produces a true relational database, avoiding duplicate information and connecting the data.
The new naming is much more intuitive, which makes development much easier.
Such changes should be done as early as possible, as further development relies on or would have to be adapted.

👍 💙

-> unique(form_id.question_id)
[...]
-> unique(form_id.question_id.option_id)

I tend to not like compound ids and think it would be best to have app-wide question_ids, option_ids and answer_ids. In this case, form_id would be optional for options and answers as they are already related to the via the questions. This is debatable, though, of course.

oc_forms_answers
-> [form_id, submit_id, question_id, user_id, answer]
-> (form_id.submit_id) forms a unique identifier.

I would split this up further into a submissions table that contains the user id, form_id and a timestamp, perhaps, and an answers table that contains submission id, question_id and answer. Less redundancy that way.

The transition from currently used Forms-App-Installations to the new database could be tricky, as the new scheme would have to be applied to the old submitted data.

I agree. Tech debt is tough, but that's no reason not to clean it up.

[jotoeri]

I tend to not like compound ids and think it would be best to have app-wide question_ids, option_ids and answer_ids. In this case, form_id would be optional for options and answers as they are already related to the via the questions. This is debatable, though, of course.

I would split this up further into a submissions table that contains the user id, form_id and a timestamp, perhaps, and an answers table that contains submission id, question_id and answer. Less redundancy that way.

This both sounds useful. 👍

And adding to the list:

• In new table 'oc_forms_forms' change columns 'is_anonymous', 'full_anonymous', 'ans_unique' into proper boolean Columns.

[jotoeri]

I gave it a first try on questions-table. Works for me on SQLite, MySQL and PostgreSQL.

Branch alter_Db/oc_forms_questions

@rullzer @jancborchardt The Migration-Script became a quite interesting construct to store and restore the data from Db. But it works... :D Is this a possible/proper way to go?
So is it worth to continue on the other tables with this ansatz?
MigrationScript

This script is just for migrations now, in the end i would propose to delete the initial Db-Creation and for fresh installations directly to create the newly structured Db. But that will be one of the last steps...

[jotoeri]

TargetTables.md

[marcelklehr]

Mmh, I'm not sure about those dummy classes. I can see why you'd want them, but it makes things really complex and may even break when classes you depend on in there are changed. I defer to @rullzer on this :)

[jotoeri]

Unfortunately it broke already on PostgreSQL Database when renaming the table. The autoincrement-index did not follow, when reinserting the old rows.
So the Question could be opened a bit more to how to migrate the data into completely new tables... Loading the whole data in combination into variables and then restore? Would result in quite much buffered data during migration?

[jancborchardt]

@rullzer @skjnldsv your call on this – the rework is the time to do it. :)