2

Suppose I have a table that stores items of a very specific type. Items of this type are stored in a bank for future use, and they can be changed specifically for a situation without being changed in the bank. They can also be used in bulk. When an instance is created, it does not need to "know" what template was used.

Examples:

  • A bank of warning messages to be sent or printed -- the users can optionally change the text when they use a message.
  • A bank of exam questions that teachers can use in exams or exercise lists -- they can alter the contents when they use them.
  • A bank of predefined signup forms -- elements can be added or removed when a form is used.

In all cases, the element ("template") in the bank should not be changed.

How can I design the database model to handle this type of situation? I have searched on SE and Google, but I had no luck.

I've thought of some possible approaches, all of them assuming the existence of a separate table collection for bulk use (e.g. multi-part form, exam with many questions):

1. Storing everything in a single table foo.

  • A true/false column is_template tells if the entry is a template.
  • When an item is used, a copy of its record is added to the table (but with is_template set to false), possibly with changes made by users.
  • An additional FK column collection_id stores the id of the collection in which the item is used, or null if it is a template record.

I think this approach is not good because a single table would store data of different types (after all, a template and an instance are not the same thing) and simple queries would be unnecessarily complicated (such as SELECT ... FROM foo WHERE ... AND NOT is_template).

2. Using two tables: foo_template and foo_instance.

  • Both tables have exactly the same columns, except for:
    • a FK column collection_id, which is present only in the table foo_instance.
    • columns related to authoring and editing (author and time), which are present only in the table foo_template.
  • When an item is used, its record is copied from foo_template to foo_instance with the changes made by the user.

This approach seems better than the previous one, though having two tables whose column sets are almost equal does not look good for me.

3. Using three tables: foo, foo_template and foo_instance.

  • The table foo stores the data (the columns that are present in both a template and an instance).
  • The table foo_template stores only author/time data and a FK foo_id.
  • The table foo_instance stores only has a FK column collection_id and a FK foo_id.

Thus, we no longer have two tables with the same columns, since all the data is in the table foo. However, it also does not seem appropriate to me because queries would be overcomplicated (almost every query to foo_template or foo_instance would require using JOIN with foo.

So...

Is there any better approach for this type of situation or is some of the above approaches good enough?

[EDIT]

I have searched for "is_template" to check if my first approach is used, and here are a few things I have found (in other situations, but with the same idea of storing templates and instances of some type of data):

is_template marks the task as a template. Physically, there is no difference between a task and a template; however, a template cannot be executed. Possible values are: true and false. Source

is_template: Indicates whether the desktop is a template; the default is 0 (not a template).

Source

To create a template, use the CREATE_TASK procedure but set is_template parameter to TRUE.

Source

The only difference between a project that is a template and a regular project is simply a checkmark in a column named is_template in the projects table.

Source

The field is_template is the only difference between a template and a mailing.

Source

So I think my first idea is not that bad, since it is used in many places, even by Oracle in a specific case. At the moment I am considering using it, but I am definitely open to new ideas.

Thank you

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.