Postgresql – Flashcards and templates

database-designpostgresqlrails

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?

Best Answer

Your app requires an an infinite amount of ways to store an infinite amount of arbitrary data. At that point you're critically limiting what you can do with a schema. You may as well just store the environment, and an array of templates.

CREATE TABLE flashcards (
  id          serial  PRIMARY KEY,
  environment jsonb,
  templates   jsonb   CHECK ( jsonb_typeof(templates) = 'ARRAY' )
);

Now, in the client (browser/javascript), you call (using pugs but you could also use Handlebars or even compile in into an Angular Component)

pug.compile(templates[a])(environment)

Using this kind of schema you can meet the requirements of your application in storing and rendering flashcards, but I have no idea what kind of data querying you'll be doing on them.

Related Question