| Subject | Advice requested on design pattern |
|---|---|
| Author | Mike Ro |
| Post date | 2015-05-19T21:35:04Z |
I am creating a database that consists of 30 - 40 tables.
Each table stores a specific set of data which has a specific set of attributes. There are also attributes common to all tables for example:
Normally insertions and updates would be infrequent but retrieval needs to be as fast as possible. The data is being displayed in a relatively simple client written in C++ and using IBPP.
Q1: I understand that Firebird does not support table inheritance. Therefore is it better to create a 'COMMON_FIELDS' table and then join the 'specialised' tables to it or include the common fields (i.e. the list above) in each and every table?
----
The 'created' and 'modified' audit fields should be updated automatically and obviously I will use a trigger to do this. One advantage I can see of using a single COMMON_FIELDS table is that I can write one stored procedure and call it from the 'before' trigger of each table. Alternatively in the case where the common fields are repeated in each table I could pass the table name as a parameter to the SP.
Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure with a table name from a trigger and have the SP take care of the audit field updates (not exactly sure how to do this yet)?
----
It would be good if the audit fields were not easy to fiddle with (i.e. by someone using FlameRobin or isql). The obvious way would be for them to be 'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in the application.
Q3: Is it possible to hide the SP from a casual observer or would it be better to write a UDF for the obfuscation?
I appreciate that the answer may be "Depends ... " but I would appreciate general guidance or opinions where it isn't possible to provide a definite answer.
Many thanks for the help!!
Each table stores a specific set of data which has a specific set of attributes. There are also attributes common to all tables for example:
- UID (from generator)
- Name
- Description
- DateTime Created (audit field)
- DateTime Modified (audit field)
- User Created (audit field)
- User Modified (audit field)
Normally insertions and updates would be infrequent but retrieval needs to be as fast as possible. The data is being displayed in a relatively simple client written in C++ and using IBPP.
Q1: I understand that Firebird does not support table inheritance. Therefore is it better to create a 'COMMON_FIELDS' table and then join the 'specialised' tables to it or include the common fields (i.e. the list above) in each and every table?
----
The 'created' and 'modified' audit fields should be updated automatically and obviously I will use a trigger to do this. One advantage I can see of using a single COMMON_FIELDS table is that I can write one stored procedure and call it from the 'before' trigger of each table. Alternatively in the case where the common fields are repeated in each table I could pass the table name as a parameter to the SP.
Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure with a table name from a trigger and have the SP take care of the audit field updates (not exactly sure how to do this yet)?
----
It would be good if the audit fields were not easy to fiddle with (i.e. by someone using FlameRobin or isql). The obvious way would be for them to be 'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in the application.
Q3: Is it possible to hide the SP from a casual observer or would it be better to write a UDF for the obfuscation?
I appreciate that the answer may be "Depends ... " but I would appreciate general guidance or opinions where it isn't possible to provide a definite answer.
Many thanks for the help!!