MuratYaman.co.uk

Firebird SQL Server has always been a “proper” database system since it forked from Borland’s famous Interbase. I just love it!

In some big database applications, I found that the developers preferred to duplicate a set of tables for probably performance reasons.

For example, in most accounting databases, there is a notion of company so that you can create multiple companies and manage its clients, suppliers, transactions etc. separately. Also, for critical changes in the way you do business or because of the changes in the whole country, you can leave it aside as historical data, and create a new company to start afresh.

So, if we have tables for client and supplier accounts, invoices, invoice details (items, products) and so on and so forth, they are duplicated for each company. For instance, the table “TBL_ACCOUNT_AB” is for company ABC (just a reference code for your company) and the table “TBL_ACCOUNT_XY” is for another company (XYZ). Likewise, you may have 50 tables, if you have 5 companies and 10 tables in your set.

You may say, I thought as well: why do we not duplicate the whole database? Well, we may not need the whole database to be duplicated and it has other implications, such as managing the user list, etc.

Here comes the power of Firebird:

Let’s define a simple set of tables for company ABC:

CREATE TABLE TBL_ACCOUNT_AB (
  ACC_ID          INTEGER NOT NULL
, ACC_NAME     VARCHAR(50)
, CONSTRAINT   PK_ACCOUNT_AB PRIMARY KEY (ACC_ID)
);

CREATE TABLE TBL_INVOICE_AB (
  ACC_ID          INTEGER NOT NULL
, INV_ID           INTEGER NOT NULL
, OTHER_DATA  VARCHAR(50)
, CONSTRAINT   PK_INVOICE_AB PRIMARY KEY (INV_ID)
, CONSTRAINT   FK_INVOICE_AB FOREIGN KEY (ACC_ID)
                      REFERENCES TBL_ACCOUNT_AB (ACC_ID) 
);

..and duplicate these for company XYZ:

CREATE TABLE TBL_ACCOUNT_XY (
  ACC_ID          INTEGER NOT NULL
, ACC_NAME     VARCHAR(50)
, CONSTRAINT   PK_ACCOUNT_XY PRIMARY KEY (ACC_ID)
);
 
CREATE TABLE TBL_INVOICE_XY (
  ACC_ID          INTEGER NOT NULL
, INV_ID           INTEGER NOT NULL
, OTHER_DATA  VARCHAR(50)
, CONSTRAINT   PK_INVOICE_XY PRIMARY KEY (INV_ID)
, CONSTRAINT   FK_INVOICE_XY FOREIGN KEY (ACC_ID)
                      REFERENCES TBL_ACCOUNT_XY (ACC_ID) 
);

To track the current company record, let’s have a simple parameter/settings table:

CREATE TABLE TBL_SETTING(
  ID       INTEGER DEFAULT 0 NOT NULL   /* Primary Key, always=0 */
, CMP    VARCHAR(2) DEFAULT 'AB'         /* Company Abbreviation */
, CONSTRAINT PK_SETTING PRIMARY KEY (ID)
);

It’s very simple to create a stored procedure to get data from our tables. But, on this one, the trick is automatically switch between the sets and get data from that set. Here are 2 samples:

1. For the main table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SET TERM ^ ;
CREATE PROCEDURE SP_ACCOUNT
RETURNS (
    ACC_ID    INTEGER,
    NAME      VARCHAR(50))
AS
DECLARE VARIABLE CMP VARCHAR(2);
DECLARE VARIABLE SQL VARCHAR(255);
BEGIN
    SELECT CMP FROM "TBL_PARAM" INTO :CMP;
    SQL = 'SELECT ACC_ID, NAME FROM TBL_ACCOUNT_' || CMP;
    FOR EXECUTE STATEMENT SQL
    INTO :ACC_ID, :NAME
    DO
        SUSPEND;
END ^
SET TERM ; ^

2. For the subtable:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SET TERM ^ ;
CREATE PROCEDURE SP_INVOICE
RETURNS (
    ACC_ID         INTEGER,
    INV_ID          INTEGER,
    OTHER_DATA VARCHAR(50)
)
AS
DECLARE VARIABLE CMP VARCHAR(2);
DECLARE VARIABLE SQL VARCHAR(255);
BEGIN
    SELECT CMP FROM TBL_PARAM INTO :CMP;
    SQL = 'SELECT ACC_ID, INV_ID, OTHER_DATA from TBL_INVOICE_' || CMP;
    FOR
        EXECUTE STATEMENT SQL
        INTO :ACC_ID, :INV_ID, :OTHER_DATA
    DO
        SUSPEND;
END ^
SET TERM ; ^

Now, all this is for not duplicating the effort whenever we need these 2 tables:

WRITE IT ONCE, REUSE IT!

Another excellent feature of Firebird is to be able to use stored procedures in SELECT statements:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SET TERM ^ ;
CREATE PROCEDURE SP_ACCOUNT_INVOICE (
    CRIT VARCHAR(50)
)
RETURNS (
    ACC_ID         INTEGER,
    NAME           VARCHAR(50),
    INV_ID          INTEGER,
    OTHER_DATA VARCHAR(50)
)
AS
BEGIN
FOR
  EXECUTE STATEMENT
    'SELECT A.ID, A.NAME, I.INV_ID, I.OTHER_DATA
     FROM SP_ACCOUNT A
     INNER JOIN SP_INVOICE I ON A.ACC_ID = I.ACC_ID
     WHERE A.NAME LIKE ''%' || :CRIT || '%''  '
INTO :ACC_ID, :NAME, :INV_ID, :OTHER_DATA
DO
  SUSPEND;
END ^
SET TERM ; ^

Can you do this in M$ SQL Server? NO!

You have to spend so much time to get the similar effect. If you want to reuse your stored procedures, you must create FUNCTIONS (returning tables) in M$ SQL, in which you can NOT use dynamic SQL (using EXEC or similar command). This means you have to stick to stored procedures ALL the time and you must duplicate what we did on SP_TEST whenever you need.

For example, you may want to create 10 reports based on your client accounts table. This means: you must write 10 stored procedures and repeat the same block of statements to get the account details!

Sometimes, in M$ SQL, I use VIEWS and I must fix the table names:

CREATE VIEW VW_TEST
AS
SELECT *
FROM TBL_TEST_AB

and when the change comes, it IS a waste of time to go through all the VIEWS and recompile them with the new extension:

CREATE VIEW VW_TEST
AS
SELECT *
FROM TBL_TEST_XY

THIS IS TERRIBLE!