4

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

1 Answer 1

2
+25

There is no absolute answer for this. It depends on whether you think templates and instances "are really" the same thing or different. This is not determined by science but by craft, a bit of philosophy, and a deep understanding of the business domain.

As rule-of-thumb I would say two things are different if they have

  • independent meanings to the user community
  • little overlap in their attributes
  • significantly different lifecycles
  • differing sets of operations which can manipulate them
  • each is used by a distinct set of users

The greater the divergences the greater the confidence that these two things are indeed separate entity types. The smaller the divergences the more confident we are the two things are simply synonyms for the same entity type. Again, this is a judgement call, not a check list. One of these rules may be super important in your use case and overrides the influence of the others.

So it all comes down to the level of abstraction you wish to apply. If templates and instance documents are all really just the same thing then a single table (called Documents perhaps?) will suffice. Add columns is_template and template_id to facilitate processing. This is your option 1.

If templates and instances are different from each other but all templates are just a template no matter what it is a template of you have separate tables called Templates and Instances. This is your option 2 (I think, though I'm a little confused by the foo_ prefix).

Your option 3 is where you treat templates and instances as sub-types. This is common if

  • there are a few common attributes to the sub-types and many unique ones
  • other tables in the system which can reference either a template or an instance

Option 3 is a finessed implementation of option 1.

If a template for a warning message is a different thing to a template for exam questions then you have tables warning_message_template, exam_question_template etc. This is orthogonal to whether templates and instances are the same type.

Looking at the opening rules-of-thumb and applying comments from the question it seems

  • template and instance have independent meanings: "a template and an instance are not the same thing"
  • there are many common attributes and a few distinct ones
  • it seems reasonable that the lifecycles differ; a template can be used for years but an exam question's only useful until results are published
  • instances uniquely can be sent to recipients; otherwise I presume they are both edited/ versioned/ saved like a document so very similar operations
  • I'd guess the same people who write templates use them to make instances

Balancing these out it seems to me that option 2 is preferred as it separates the two types. Option 1 does not acknowledge the understanding that templates and instances are seen as distinct.

Do not fear having the same columns in two tables. Any one object will only be in one of these. There will be no data redundancy.

Some additional thoughts:

If you need an instance to reference the template it is derived from - a foreign key - this is much simpler with Template and Instance as different tables.

Each implementation can be presented as the others with views and an adjustment to unique key definitions.

For related questions with different balance of considerations see this and this answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.