Q1: We have an application with around 400 table and we use this structure for all tables. I.e. apart from the payload columns, all tables have these housekeeping fields. However our app framework populates these tables as the app logs in with it’s own single user. Due to a complex security and permissions model, the app has it’s own user model that the DB does not know about. The advantage of this is that we have a base class in the app domain (C#) that implements these properties. All DataObjects then derive from this. Our app is OLTP and the advantage of having the app populate the date fields is that for testing, we can centrally alter the timebase the app runs at without having to mess with server time and the consequences of that.



Yes FB is a RDBMS and not OO DB. In a previous project I used ‘inheritance’ where I put common fields in a ‘base’ table and other fields in a ‘derived’ table and then a view on top. The 2 tables are then linked via a PK with same value. The problem I had was when I queried the view, it only used indexes of one table, depending on wich one was first in the select in the view. It ignored any indexes from the other table. So this fell flat. This was in FB 2.1. I would not advise doing this.



Q2: Our approach is to have as few triggers / SPs in the DB. All updates and exchanges with the DB through our generic data access layer. The only place where we use triggers is to manage a RowVersion column after insert and update and reject concurrent row updates. I.e. where the users or system updates stale data. In our experience, SPs and triggers become messy very quickly and is hard to debug. Been bitten once too many times.



You also simply don’t grant access to casual users to the tables. The app uses a user with all grants in place. Our convention is to use a table name such as CUSTOMER_. Then we on top of that we put a view called CUSTOMER. The view also brings in some columns from ‘lookup’ table such as CURRENCY, etc. Normal users using reporting tools etc only get granted select access on the view and not the underlying table.



Q3: You can clear the RDB$PROCEDURE_SOURCE field in the RDB$PROCEDURE system table containing the SP and trigger definition. But take care to keep the scripts somewhere for backup.







From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 19 May 2015 11:35 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Advice requested on design pattern





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!!







[Non-text portions of this message have been removed]