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:
* UID (from generator)
* Name
* Description
* DateTime Created (audit field)
* DateTime Modified (audit field)
* User Created (audit field)
* User Modified (audit field)
Most tables will contain a few thousands of records, some of them may be
largish blobs such as photos but mostly it will be plain text and HTML.
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!!
