Database design - reusable elements (templates)

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).

1. 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 ar

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.

StackExchange Database Administrators Q#138370, answer score: 2