Re: [firebird-support] Re: how to use the same column from a single table to other tables?
On Fri, May 3, 2013 at 8:07 PM, dice_in_trouble dice_in_trou...@yahoo.comwrote: You wrote: the client id also doens't auto increment. I asked How do you generate client identifiers? You responded What do you mean by client identifiers? I would like to summarize all the data in a certain column. Summarizing data can be done with computed columns, or with triggers. Triggers introduce a chance for deadlocks but perform better if you have a high read:write ratio because the sum doesn't have to be computed on reference. However, you also mentioned that your client_id field is not generated automatically. One way people get in trouble with Firebird is using something like setting an identifier column with an expression like identifier = MAX(identifier) + 1 I wondered if that was how you were creating unique identifiers. Good luck, Ann [Non-text portions of this message have been removed]
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Ann Harrison aharrison@... wrote: On Fri, May 3, 2013 at 8:07 PM, dice_in_trouble dice_in_trouble@...wrote: You wrote: the client id also doens't auto increment. I asked How do you generate client identifiers? You responded What do you mean by client identifiers? I would like to summarize all the data in a certain column. Summarizing data can be done with computed columns, or with triggers. Triggers introduce a chance for deadlocks but perform better if you have a high read:write ratio because the sum doesn't have to be computed on reference. However, you also mentioned that your client_id field is not generated automatically. One way people get in trouble with Firebird is using something like setting an identifier column with an expression like identifier = MAX(identifier) + 1 I wondered if that was how you were creating unique identifiers. Good luck, Ann [Non-text portions of this message have been removed] Thanks for the reply. Unfortunately I just put a char on the id and without a null option. I encountered once a problem because I used the number 1 already but have it deleted on the record, but I cannot use the number 1 again. Anyways I was planning to make the client ID automatically generates the numbers so that I will not mix up if the time comes that I have lots of data already encoded to the database. Regarding your answer regarding the summary. I honestly can't understand anything from deadlocks to triggers and identifiers. Thank you. Sincerely, Cedris
Re: [firebird-support] Re: how to use the same column from a single table to other tables?
Evening Cedris, When I'm in the office on Tuesday, ill send you some info. A crash course in Firebird. Cheers, Norm. Sent from my HTC [Non-text portions of this message have been removed]
Re: [firebird-support] Re: how to use the same column from a single table to other tables?
I'll se what I can come up with. Cheers, Norm. Sent from my HTC - Reply message - From: W O sistemas2000profesio...@gmail.com To: firebird-support@yahoogroups.com Subject: [firebird-support] Re: how to use the same column from a single table to other tables? Date: Sat, May 4, 2013 22:13 Norman, if it don't disturbe you I want such course,too. Greetings. Walter. On Sat, May 4, 2013 at 5:06 PM, nor...@dunbar-it.co.uk nor...@dunbar-it.co.uk wrote: ** Evening Cedris, When I'm in the office on Tuesday, ill send you some info. A crash course in Firebird. Cheers, Norm. Sent from my HTC [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links [Non-text portions of this message have been removed]
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, nor...@dunbar-it.co.uk Norman@... wrote: Evening Cedris, When I'm in the office on Tuesday, ill send you some info. A crash course in Firebird. Cheers, Norm. Sent from my HTC [Non-text portions of this message have been removed] Good day Norm, Thank you very much. Have a nice day. Best regards, Cedris
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Norman Dunbar Norman@... wrote: On 02/05/13 11:15, dice_in_trouble wrote: Hi Cedris, Thank you for this wonderful post. Will try this now. Thank you again. You are welcome. I suggest you purchase a book on Firebird, as has been suggested, or at the very least, a decent beginner's guide to database design which will help you understand the subject. I have plans to do a Firebird Database Design manual for the documentation, but as with many things, time is not on my side! So, don't wait for me! One other thing, you might be wondering why I've put the COLLECTION table's payment column as a bigint rather than a floating pint of some kind? That was just for simplicity. Currency is a difficult subject to get exact values in when using a floating point data type (. It's best to use a fixed point data type of some kind (decimal) or count in pennies (in my case) (bigint) rather than in pounds and pennies. However, the data type DECIMAL(18,4) - means 18 digits wide with 4 of them appearing after the decimal - is useful for a CURRENCY type, so you could create a domain. CREATE DOMAIN CURRENCY AS DECIMAL(18,4); In the UK, that's £99,999,999,999,999. - which is about my hourly rate! ;-) Seriously, it's a value between -2**63 and +2**63 -1. Why 18,4? If you are dividing, for some reason, monetary values, the extra precision will help avoid too many rounding errors. DECIMALs are good for counting currencies, but beware if the currency you are counting (or accumulating) could become very large - see above. The Italian Lira (as it was) for example, could soon fill a CURRENCY type. The Euro, might take a bit longer! And a DECIMAL is limited to 18 digits. Have fun. Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767 OIC. Thank you very much norm for the informative and very useful post and example you have given me. I would love to buy a book on firebird but I don't have a credit card, and I cannot find one here in our country. I'm from the Philippines BTW. And also regarding database design what book is advisable for beginners like myself? Regarding the big int, floating int, etc. I can't still understand them. I just put decimal in the value for integers because I have a TButton that automatically computes the balance left (which was my first design). That formula was executed successfully, however, I have difficulty updating my collection (because it updates daily.) and btw how can I include formulas like from excel to my firebird database? I would like to sum up the total payments made in the collection and the balance on the clients table, which will appear at the bottom and cannot be edited unless I change or add a value to the balance and payment.
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, dice_in_trouble dice_in_trouble@... wrote: --- In firebird-support@yahoogroups.com, Norman Dunbar Norman@ wrote: On 02/05/13 11:15, dice_in_trouble wrote: Hi Cedris, Thank you for this wonderful post. Will try this now. Thank you again. You are welcome. I suggest you purchase a book on Firebird, as has been suggested, or at the very least, a decent beginner's guide to database design which will help you understand the subject. I have plans to do a Firebird Database Design manual for the documentation, but as with many things, time is not on my side! So, don't wait for me! One other thing, you might be wondering why I've put the COLLECTION table's payment column as a bigint rather than a floating pint of some kind? That was just for simplicity. Currency is a difficult subject to get exact values in when using a floating point data type (. It's best to use a fixed point data type of some kind (decimal) or count in pennies (in my case) (bigint) rather than in pounds and pennies. However, the data type DECIMAL(18,4) - means 18 digits wide with 4 of them appearing after the decimal - is useful for a CURRENCY type, so you could create a domain. CREATE DOMAIN CURRENCY AS DECIMAL(18,4); In the UK, that's £99,999,999,999,999. - which is about my hourly rate! ;-) Seriously, it's a value between -2**63 and +2**63 -1. Why 18,4? If you are dividing, for some reason, monetary values, the extra precision will help avoid too many rounding errors. DECIMALs are good for counting currencies, but beware if the currency you are counting (or accumulating) could become very large - see above. The Italian Lira (as it was) for example, could soon fill a CURRENCY type. The Euro, might take a bit longer! And a DECIMAL is limited to 18 digits. Have fun. Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767 OIC. Thank you very much norm for the informative and very useful post and example you have given me. I would love to buy a book on firebird but I don't have a credit card, and I cannot find one here in our country. I'm from the Philippines BTW. And also regarding database design what book is advisable for beginners like myself? Regarding the big int, floating int, etc. I can't still understand them. I just put decimal in the value for integers because I have a TButton that automatically computes the balance left (which was my first design). That formula was executed successfully, however, I have difficulty updating my collection (because it updates daily.) and btw how can I include formulas like from excel to my firebird database? I would like to sum up the total payments made in the collection and the balance on the clients table, which will appear at the bottom and cannot be edited unless I change or add a value to the balance and payment. the client id also doens't auto increment.
Re: [firebird-support] Re: how to use the same column from a single table to other tables?
On Fri, May 3, 2013 at 2:16 PM, dice_in_trouble dice_in_trou...@yahoo.comwrote: and btw how can I include formulas like from excel to my firebird database? I would like to sum up the total payments made in the collection and the balance on the clients table, which will appear at the bottom and cannot be edited unless I change or add a value to the balance and payment. Look at any information you can find about computed fields. the client id also doens't auto increment. How do you generate client identifiers? Good luck, Ann ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links [Non-text portions of this message have been removed]
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Ann Harrison aharrison@... wrote: On Fri, May 3, 2013 at 2:16 PM, dice_in_trouble dice_in_trouble@...wrote: and btw how can I include formulas like from excel to my firebird database? I would like to sum up the total payments made in the collection and the balance on the clients table, which will appear at the bottom and cannot be edited unless I change or add a value to the balance and payment. Look at any information you can find about computed fields. the client id also doens't auto increment. How do you generate client identifiers? Good luck, Ann ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links [Non-text portions of this message have been removed] Ann, What do you mean by client identifiers? I would like to summarize all the data in a certain column. Thanks. Cedris
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Norman Dunbar Norman@... wrote: Morning dice_in_trouble, If I understand you correctly, you have or wish to have the following: CLIENT TABLE: CLIENT DATE BALANCE ADDRESS COLLECTIONS TABLE: TIMESTAMP PAYMENT CLIENT BALANCE And if you update the client table you want the client and/or balance updated on the collections table, and if you update the collections table, you wish for the client and/or balance on the client table to be updated. I see a problem, you could end up in a circular loop where an update to one table causes an update to the other which updates the first which updates the second which .. I think your design, as I understand it, is wrong. In a relational database you should not be duplicating information, this is what normalisation does for you, it helps you design your tables correctly. Data should be updated once, in one place and that update will be visible everywhere. Off the top of my head, I would suggest something like the following is needed: CLIENT TABLE: CLIENT_ID CLIENT_NAME ADDRESS ... BALANCE TABLE: CLIENT_ID DATE? BALANCE COLLECTIONS TABLE: TIMESTAMP PAYMENT CLIENT_ID CLIENT_ID in BALANCE and COLLECTION will be a foreign key referencing the client_id in the CLIENT table. Also, CLIENT_ID in the COLLECTIONS TABLE will be a foreign key referencing the BALANCE table. I'm not sure why you would need a DATE field (which is most likely a reserved word, but you can check that for me!) in the CLIENT table. The balance for each client, if your processing is correct, is as of right now. So, you can now update the client details in one place, the CLIENT table. Payments update the COLLECTION table, and, if necessary, a trigger could then add the payment into the BALANCE for that CLIENT_ID. However, if a payment by cheque, for example, needs 4 days to clear, you may have to do some batch processing to update cheques that cleared into the BALANCE table. It depends on your needs. Maybe you would have a PAYMENT_CLEARED flag in the COLLECTIONS table, and when that was updated to show that the payment had cleared, then a trigger would update the BALANCE table for that client. You might have a PENDING_PAYMENTS_BALANCE on the BALANCE table to total up the payments that have been made, but have not yet been cleared for that particular client. It all depends on what your requirements are. HTH Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767 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?
Re: [firebird-support] Re: how to use the same column from a single table to other tables?
Hi, On 02/05/2013 10: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? You would use alter table for this, not sure how one uses this in flamerobin as I don't use it to generate my databases (I use Python/SQLAlchemy and after I designed my ORM declarative classes SQLAlchemy does the creation for me. Sometimes I do it manually for testing something then I use IBExpert (just because I new it before flamerobin), it has an option create foreign key which then walks you through the process. I am no expert but to me your question makes me think that you might need to read up a bit more about databases, SQL and Firebird, you should be able to find tutorials with a google search. Obviously the FB documentation is also not to be forgotten. http://www.firebirdsql.org/en/reference-manuals/ Maybe invest in the Firebird Book, e.g. the second Edition Quote: The Firebird Book (Second Edition) is now available as a print on demand book (at createspace) in three parts, Volume 1: `Firebird Fundamentals https://www.createspace.com/4203352 Volume 2: `Developing with Firebird Data https://www.createspace.com/4206843 Volume 3: `Administering Firebird Servers and Databases https://www.createspace.com/4206991 Regards Paul EndQuote Werner [Non-text portions of this message have been removed]
Re: [firebird-support] Re: how to use the same column from a single table to other tables?
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
Re: [firebird-support] Re: how to use the same column from a single table to other tables?
On 02/05/13 11:07, Norman Dunbar wrote: Start FlameRobin and comment to the database with the appropriate user. Which should, of corse, read: Start FlameRobin and connect to the database with the appropriate user. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
[firebird-support] Re: how to use the same column from a single table to other tables?
--- In firebird-support@yahoogroups.com, Werner F. Bruhin werner.bruhin@... wrote: Hi, On 02/05/2013 10: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? You would use alter table for this, not sure how one uses this in flamerobin as I don't use it to generate my databases (I use Python/SQLAlchemy and after I designed my ORM declarative classes SQLAlchemy does the creation for me. Sometimes I do it manually for testing something then I use IBExpert (just because I new it before flamerobin), it has an option create foreign key which then walks you through the process. I am no expert but to me your question makes me think that you might need to read up a bit more about databases, SQL and Firebird, you should be able to find tutorials with a google search. Obviously the FB documentation is also not to be forgotten. http://www.firebirdsql.org/en/reference-manuals/ Maybe invest in the Firebird Book, e.g. the second Edition Quote: The Firebird Book (Second Edition) is now available as a print on demand book (at createspace) in three parts, Volume 1: `Firebird Fundamentals https://www.createspace.com/4203352 Volume 2: `Developing with Firebird Data https://www.createspace.com/4206843 Volume 3: `Administering Firebird Servers and Databases https://www.createspace.com/4206991 Regards Paul EndQuote Werner [Non-text portions of this message have been removed] Thanks Paul for the advice. I have read some of the documentations on the firebird site, but I am having a hard time understanding them, and I have posted here hoping I might get some help on how to make my database as I want it. Regards, Cedris
[firebird-support] Re: how to use the same column from a single table to other tables?
--- 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
Re: [firebird-support] Re: how to use the same column from a single table to other tables?
On 02/05/13 11:15, dice_in_trouble wrote: Hi Cedris, Thank you for this wonderful post. Will try this now. Thank you again. You are welcome. I suggest you purchase a book on Firebird, as has been suggested, or at the very least, a decent beginner's guide to database design which will help you understand the subject. I have plans to do a Firebird Database Design manual for the documentation, but as with many things, time is not on my side! So, don't wait for me! One other thing, you might be wondering why I've put the COLLECTION table's payment column as a bigint rather than a floating pint of some kind? That was just for simplicity. Currency is a difficult subject to get exact values in when using a floating point data type (. It's best to use a fixed point data type of some kind (decimal) or count in pennies (in my case) (bigint) rather than in pounds and pennies. However, the data type DECIMAL(18,4) - means 18 digits wide with 4 of them appearing after the decimal - is useful for a CURRENCY type, so you could create a domain. CREATE DOMAIN CURRENCY AS DECIMAL(18,4); In the UK, that's £99,999,999,999,999. - which is about my hourly rate! ;-) Seriously, it's a value between -2**63 and +2**63 -1. Why 18,4? If you are dividing, for some reason, monetary values, the extra precision will help avoid too many rounding errors. DECIMALs are good for counting currencies, but beware if the currency you are counting (or accumulating) could become very large - see above. The Italian Lira (as it was) for example, could soon fill a CURRENCY type. The Euro, might take a bit longer! And a DECIMAL is limited to 18 digits. Have fun. Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
