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.
- 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
- A true/false column
is_templatetells if the entry is a template.
- When an item is used, a copy of its record is added to the table (but with
is_templateset to false), possibly with changes made by users.
- An additional FK column
collection_idstores 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:
- Both tables have exactly the same columns, except for:
- a FK column
collection_id, which is present only in the table
- columns related to authoring and editing (author and time), which are present only in the table
- a FK column
- When an item is used, its record is copied from
foo_instancewith 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:
- The table
foostores the data (the columns that are present in both a template and an instance).
- The table
foo_templatestores only author/time data and a FK
- The table
foo_instancestores only has a FK column
collection_idand a FK
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_instance would require using
Is there any better approach for this type of situation or is some of the above approaches good enough?
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_templatemarks 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:
is_template: Indicates whether the desktop is a template; the default is 0 (not a template).
To create a template, use the
CREATE_TASKprocedure but set
is_templateparameter to TRUE.
The only difference between a project that is a template and a regular project is simply a checkmark in a column named
is_templatein the projects table.
is_templateis the only difference between a template and a mailing.
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.