Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query
Good morning, you make a real hit! />The database for Company A should have all the tables used by the users in Company A, including the low-volatility ones like your/ />Municipalities table. If Comapny B needs Municipalities, it should have its own copy. //_ _/ /_>You can create a DML script _//for this low-volatility table and run it over each database.../ You have answered to the question that I have turned some days ago, but I don't know how to do. What it means "low-volatility"? without "consistency"? To create a temporary "vew" to utilize as a table to join? Can you explain me the concept, even with an example? I think that the problem (always for me) is to utilize the table of the second database in the same transaction of the table of the first database... I thank for the devote time, perhaps the question is "a little banal"... (^_^) I cordially greet Antonio BIANCA Il 18/09/2018 00:27, Helen Borrie hele...@iinet.net.au [firebird-support] ha scritto: > > Antonio, > > > I don't understand as you have been able to believe that I have treated > > a database as was a spreadsheet!!! I know what is a table, a database > > (database as "set of tables", database as "file of type database"), a > > relation, a key, an inedex, a... I've studied on your book. > > > When I speak "to draw a table" for a program, I intend to set various > > "tables" with the appropriate fields (name and type) and the > > relationships with the fields of other "tables": so the database is the > > whole organized of these "tables" and relations. Right? > > So far, so good. > > > if it is not correct, I go to be a carpenter... > > else > > - a client ask me to write a program for a single scope, the archives > > (the "tables" in the database!) must be able to contain the data related > > to that type of activity and I draw (create, write) a dabatase with the > > correct tables; > > Yes, one client (Company A) one database > > > - different clients ask me different types of programs: FOR ME, a > > database must be created (drawn) for each type of program; > > For everyone else, a database is created for each client organisation > (one database for Company A, one database for Company B, and so on. > > > - two or more programs can have some identical "table" (e.g. > > municipalities); > > - (1) I can create apart a unique database of "common tables" for the > > "tables" used by all the programs; > > The database for Company A should have all the tables used by the > users in Company A, including the low-volatility ones like your > Municipalities table. If Comapny B needs Municipalities, it should > have its own copy. You can create a DML script for this > low-volatility table and run it over each database; or use a > replication tool if it is called for. > > > (2) the same "table" can be repeated > > in all the databases; > > That is what I referred to as "spreadsheet". Those old-style desktop > fielsystems predated spreadsheets and lent themselves to that model of > application, due to explicit table locking. That does not make it a > sensible or correct approach for a transactional RDBMS such as > Firebird. > > > - it could be created an only database for all the programs; for every > > new program, new "tables" can be added in this unique database, while > > the "table" existing can be used by the new program; > > No, all tables should be available to all progrms and to one another. > If you have the same data in more than one table, you have redundancy, > the big enemy in data management. Related to this, if you are using > actual data as keys, you have intrinsic redundancy. > > If you want to prevent certain USERs or ROLEs from accessing certain > tables, you use SQL privileges. > > > - if you write dozens of programs (very small, normally, very great), > > the only database becomes not manageable, *but it doesn't behave > problems*. > > "not manageable" FOR ME as administration of the variations to the > > single fields: Firebird is able of to manage millions of tables and > > relationships, I can't, is its purpose!! > > Firebird is designed to ensure the ACID rules (Atomicity, Consistency, > Isolation, Durability). it won't prevent you from breaking those rules > but it won't heal your wounded data for you, either. > > > You pursue a purpose of general relations, that is the purpose of DBRMS; > > I pursue a purpose of management USING Firebird and its capability to > > relate: it is not the same thing! FOR ME, naturally!! > > Actually, every database is part of a management system of one kind or > another, so you are not alone in this world. You can design a system > to store data according to rules and structures that make it safe and > smart: THAT is the purpose of a RDBMS. Apparently, you want to follow > the spreadsheet model, in which a single set of data is exclusive to a > single application. So - you maintain multiple instances of the same > data by hand and say your prayers. > > > From this my
Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query
He, Helen, I don't understand as you have been able to believe that I have treated a database as was a spreadsheet!!! I know what is a table, a database (database as "set of tables", database as "file of type database"), a relation, a key, an inedex, a... I've studied on your book. When I speak "to draw a table" for a program, I intend to set various "tables" with the appropriate fields (name and type) and the relationships with the fields of other "tables": so the database is the whole organized of these "tables" and relations. Right? if it is not correct, I go to be a carpenter... else - a client ask me to write a program for a single scope, the archives (the "tables" in the database!) must be able to contain the data related to that type of activity and I draw (create, write) a dabatase with the correct tables; - different clients ask me different types of programs: FOR ME, a database must be created (drawn) for each type of program; - two or more programs can have some identical "table" (e.g. municipalities); - (1) I can create apart a unique database of "common tables" for the "tables" used by all the programs; (2) the same "table" can be repeated in all the databases; - it could be created an only database for all the programs; for every new program, new "tables" can be added in this unique database, while the "table" existing can be used by the new program; - if you write dozens of programs (very small, normally, very great), the only database becomes not manageable, *but it doesn't behave problems*. "not manageable" FOR ME as administration of the variations to the single fields: Firebird is able of to manage millions of tables and relationships, I can't, is its purpose!! You pursue a purpose of general relations, that is the purpose of DBRMS; I pursue a purpose of management USING Firebird and its capability to relate: it is not the same thing! FOR ME, naturally!! From this my problem is born, but I can change my formulations. I need to think. Thanks for the devote time : you must also work... Best regard Antonio BIANCA Il 15/09/2018 07:16, Helen Borrie hele...@iinet.net.au [firebird-support] ha scritto: > > Antonio, > > > I'm agree with you: all tables in a unique firebird file is the best > choice. > > > My problem is that I've numerous programs that utilize one firebird file > > each. > > That problem is easy to solve. Place all of the tables in one > database and use an alias for that database in every application. > > > There are tables that are drawn for a specific program, but there are > > some tables that are "common" to all programs (example: > > "municipalities"). An user can utilize one program, two programs, four > > programs, and so on, on the same computer and each program hav its > > database.fdb: if I must vary a value of one "common tables", I must send > > an update for every program and to each user; but if the "common tables" > > are in a unique, separate database, I can update one file only. > > This is not a database design. You are treating "database" and > "spreadsheet" as though they were the same animal. Very definitely, > they are not the same. > > Think "relational", because Firebird is a relational database system. > Tables (a.k.a. relations) can be *related* to one another by way of > foreign keys. Data from tables containing compatible fields (columns) > can be connected during run-time queries by JOINs or UNIONs. > > > There are also "static" tables containing storical movements, and is not > > necessary periodically to back-up them. > > At the same time, it does no harm for them to be included in the > backup of your current data. It makes sense for them to be in the > same database as the active tables if you need to refer to them from > your applications. > > > These are the reasons that push me to look for a solution, I hope to > > have been clear. > > The thing that seems clear to me is your confusing "tables" with > "databases". The whole point of using a relational database is to > have all of the interrelated data available to each individual client > connection. > > > For release 2.5 and not 3.o, I have been studyng Firebird for some > > months, and I've not experience: I now plan the job to develop, later I > > will verify the new releases. > > The question of whether you use 2.5 or 3.0 is not relevant to your > problem. Spend some more time understanding how a relational database > works - and forget your preconceptions from previous work you have > done using spreadsheets or fiel-based data storage systems. > > Helen > > --- > This email has been checked for viruses by AVG. > https://www.avg.com > > --- Questa e-mail è stata controllata per individuare virus con Avast antivirus. https://www.avast.com/antivirus
Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query
Antonio, > I don't understand as you have been able to believe that I have treated > a database as was a spreadsheet!!! I know what is a table, a database > (database as "set of tables", database as "file of type database"), a > relation, a key, an inedex, a... I've studied on your book. > When I speak "to draw a table" for a program, I intend to set various > "tables" with the appropriate fields (name and type) and the > relationships with the fields of other "tables": so the database is the > whole organized of these "tables" and relations. Right? So far, so good. > if it is not correct, I go to be a carpenter... > else > - a client ask me to write a program for a single scope, the archives > (the "tables" in the database!) must be able to contain the data related > to that type of activity and I draw (create, write) a dabatase with the > correct tables; Yes, one client (Company A) one database > - different clients ask me different types of programs: FOR ME, a > database must be created (drawn) for each type of program; For everyone else, a database is created for each client organisation (one database for Company A, one database for Company B, and so on. > - two or more programs can have some identical "table" (e.g. > municipalities); > - (1) I can create apart a unique database of "common tables" for the > "tables" used by all the programs; The database for Company A should have all the tables used by the users in Company A, including the low-volatility ones like your Municipalities table. If Comapny B needs Municipalities, it should have its own copy. You can create a DML script for this low-volatility table and run it over each database; or use a replication tool if it is called for. > (2) the same "table" can be repeated > in all the databases; That is what I referred to as "spreadsheet". Those old-style desktop fielsystems predated spreadsheets and lent themselves to that model of application, due to explicit table locking. That does not make it a sensible or correct approach for a transactional RDBMS such as Firebird. > - it could be created an only database for all the programs; for every > new program, new "tables" can be added in this unique database, while > the "table" existing can be used by the new program; No, all tables should be available to all progrms and to one another. If you have the same data in more than one table, you have redundancy, the big enemy in data management. Related to this, if you are using actual data as keys, you have intrinsic redundancy. If you want to prevent certain USERs or ROLEs from accessing certain tables, you use SQL privileges. > - if you write dozens of programs (very small, normally, very great), > the only database becomes not manageable, *but it doesn't behave problems*. > "not manageable" FOR ME as administration of the variations to the > single fields: Firebird is able of to manage millions of tables and > relationships, I can't, is its purpose!! Firebird is designed to ensure the ACID rules (Atomicity, Consistency, Isolation, Durability). it won't prevent you from breaking those rules but it won't heal your wounded data for you, either. > You pursue a purpose of general relations, that is the purpose of DBRMS; > I pursue a purpose of management USING Firebird and its capability to > relate: it is not the same thing! FOR ME, naturally!! Actually, every database is part of a management system of one kind or another, so you are not alone in this world. You can design a system to store data according to rules and structures that make it safe and smart: THAT is the purpose of a RDBMS. Apparently, you want to follow the spreadsheet model, in which a single set of data is exclusive to a single application. So - you maintain multiple instances of the same data by hand and say your prayers. > From this my problem is born, but I can change my formulations. > I need to think. If you are unfamiliar with the ACID rules and normalization, look them up. Helen --- This email has been checked for viruses by AVG. https://www.avg.com
Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query
14.09.2018 20:08, 'Stellarancia.com' ni...@stellarancia.com [firebird-support] wrote: > My problem is that I've numerous programs that utilize one firebird file > each. All these programs can work with the same database. In contrast to DBF, Firebird server can be used by many programs without locking of the data file. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query
Antonio, > I'm agree with you: all tables in a unique firebird file is the best choice. > My problem is that I've numerous programs that utilize one firebird file > each. That problem is easy to solve. Place all of the tables in one database and use an alias for that database in every application. > There are tables that are drawn for a specific program, but there are > some tables that are "common" to all programs (example: > "municipalities"). An user can utilize one program, two programs, four > programs, and so on, on the same computer and each program hav its > database.fdb: if I must vary a value of one "common tables", I must send > an update for every program and to each user; but if the "common tables" > are in a unique, separate database, I can update one file only. This is not a database design. You are treating "database" and "spreadsheet" as though they were the same animal. Very definitely, they are not the same. Think "relational", because Firebird is a relational database system. Tables (a.k.a. relations) can be *related* to one another by way of foreign keys. Data from tables containing compatible fields (columns) can be connected during run-time queries by JOINs or UNIONs. > There are also "static" tables containing storical movements, and is not > necessary periodically to back-up them. At the same time, it does no harm for them to be included in the backup of your current data. It makes sense for them to be in the same database as the active tables if you need to refer to them from your applications. > These are the reasons that push me to look for a solution, I hope to > have been clear. The thing that seems clear to me is your confusing "tables" with "databases". The whole point of using a relational database is to have all of the interrelated data available to each individual client connection. > For release 2.5 and not 3.o, I have been studyng Firebird for some > months, and I've not experience: I now plan the job to develop, later I > will verify the new releases. The question of whether you use 2.5 or 3.0 is not relevant to your problem. Spend some more time understanding how a relational database works - and forget your preconceptions from previous work you have done using spreadsheets or fiel-based data storage systems. Helen --- This email has been checked for viruses by AVG. https://www.avg.com
Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query
He, I'm agree with you: all tables in a unique firebird file is the best choice. My problem is that I've numerous programs that utilize one firebird file each. There are tables that are drawn for a specific program, but there are some tables that are "common" to all programs (example: "municipalities"). An user can utilize one program, two programs, four programs, and so on, on the same computer and each program hav its database.fdb: if I must vary a value of one "common tables", I must send an update for every program and to each user; but if the "common tables" are in a unique, separate database, I can update one file only. There are also "static" tables containing storical movements, and is not necessary periodically to back-up them. These are the reasons that push me to look for a solution, I hope to have been clear. For release 2.5 and not 3.o, I have been studyng Firebird for some months, and I've not experience: I now plan the job to develop, later I will verify the new releases. Best regards Antonio BIANCA Il 12/09/2018 23:42, Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] ha scritto: > > Hi, > > 1. Why not single Firebird database with all tables? > 2. Why not recent Firebird which is FB3 not FB2.5? > > Pozdrawiam, > > Karol Bieniaszewski > > --- Questa e-mail è stata controllata per individuare virus con Avast antivirus. https://www.avast.com/antivirus
ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query
Hi, 1. Why not single Firebird database with all tables? 2. Why not recent Firebird which is FB3 not FB2.5? Pozdrawiam, Karol Bieniaszewski
