I'm having trouble with an appropriate DB design for flashcards that display their data with one of numerous templates.
A user has many flashcards. They pick the card template during creation. There are only 4 templates, but up to infinity in the future.
My current DB design is as follows (all id fields are indexed with a foreign key - but not all will be filled on every flashcard):
create_table "flashcards", force: :cascade do |t| t.datetime "created_at" t.datetime "updated_at" t.text "fields_n_data" t.integer 'template_type' t.integer 'word_id' t.integer 'example_sentence_id' t.integer 'meaning_id' t.integer 'grammar_id' t.integer 'image_id' t.integer 'user_id' end
Some use user-inputted data, some use data from our DB. Some have just 2 fields (front, back), while others have numerous (up to infinity in theory). Some pertain to a combo (or none) of: 1) a word, 2) a meaning, 3) a grammar point (from which information can be auto-filled).
fields_n_data stores all manually inputted information in an array of key/value pairs, where each key is the name of the field, and each value is the data to be filled in that field.
When time to display, I retrieve the appropriate template, iterate through the display fields - for each, I first check if the foreign key object is present, and if so I retrieve and fill using that info; if not, I go to the fields_n_data to get the value of the appropriate key.
This is working, but feels quite wrong, is a pain in the butt, and won't allow a user to create their own template in the future. Is there a better way?