On 02/05/13 09:47, dice_in_trouble wrote:

> Thanks Nrom for the reply. I also thought of this idea, wherein I make a separate table for the balance then link it up to the other 2 tables. But I don't know how to make table relations. I am using flamerobin to build up my firebird database. Can you give me a simple example of making a relationship between 3 tables?

Start FlameRobin and comment to the database with the appropriate user.

Right click TABLES and select Create New.

In the dialogue that appears, type the following:

CREATE TABLE client
(
client_id bigint not null,
client_name varchar(100) not null,
client_address varchar(1024),
constraint pk_client primary key (client_id)
);

CREATE TABLE balance
(
client_id bigint not null,
balance bigint,
constraint pk_balance primary key (client_id),
constraint fk_client_balance foreign key (client_id) references
client(client_id)
);

CREATE TABLE collection
(
collection_id bigint not null,
client_id bigint not null,
payment bigint not null,
date_received timestamp default current_timestamp not null ,
constraint pk_collection primary key (collection_id),
constraint fk_client_collection foreign key (client_id) references
client (client_id)
);

Press F4 to execute the statement.

Commit;

The client name and address are not in an ideal format, but for this
example, they will suffice.

You now have three tables that are in a parent child relationship with
client being the parent and two child tables - balance and collection.

The FK constraints mean that you cannot accept a payment for a client
you don't have, and, you cannot create a balance for a client you don't
have.

Also, there's probably no need to have a FK between collection and
balance (balance being the parent) because the two FKs that exists
already mean that you can't have a payment for a balance that doesn't exist.

This "design" (I use the term loosely as I haven't really designed it,
just whipped it off the top of my head) means that you can set up
clients without any balances or collections. Which is correct - if a
client has yet to make a payment, they have no balance.

Your code, business rules etc will determine when a balance gets created
- when the customer is created, or when a payment arrives and is the
first one for the client.

equally, you are unable to delete (or change the client_id) for a client
while there are existing balance or collection records for that client_id.

Now, obviously, there will need to be triggers and sequences to get the
client_id and collection_id values (they are surrogate keys as opposed
to natural keys) so:

Under the CLIENT table, right click the CLIENT_ID column and select
Properties.

In the form that appears, under autoincrement, click create new
generator and create trigger.

Click execute and then commit.


Do similar for the COLLECTION table.

If the tables don't have the ability to "drop down" the columns, double
click the table to force a refresh. Close the window that appears and
then you should be able to drop down the list of columns for the tables.


Now you can insert data into the CLIENT table and also create BALANCEs
and COLLECTIONs for that client.

You should note that the primary key of the BALANCE table is actually
the primary key of the CLIENT table. This is not a problem. The
client_id is unique and allows the balance for that client to be easily
found, once you have found the client's id.


HTH

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767