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