--- In firebird-support@yahoogroups.com, Norman Dunbar <Norman@...> wrote:

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

Thank you for this wonderful post. Will try this now. Thank you again.

Sincerely,

Cedris