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.

Ah, this is very valuable. I will take your advice.

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.

This makes sense, and will probably do something similar. I am thinking about having a table that shadows Firebird user accounts. When the user logs in from the app they would actually access the database with a less restricted account but the business logic would enforce restrictions (i.e. no user access to the audit fields). If the user logs in from a reporting tool they will use their 'real' (Firebird) user account which will have Firebird access control limitations.

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.

Thank you for this tip, I have never tried this so will do some experimentation.