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