Bob, > > There are some RDBMS-es that implement SQL to the point of apparently setting up a >construct that links tables - > > foreign key based data integrity being the first example that springs to (my) >mind. However relational logic > > suggests that the way to set up a linkage between tables is to show this in >dynamic logic - your SQL statement, > > eg > > > > SELECT * > > FROM tbl1, tbl2 > > WHERE tbl1.PrimaryKey = tbl2.ForeignKey; > > > > will produce a result-row for every intersection 'match' between the two tables. > > > > One of the MAJOR advances of relational databases over their predecessors was this >move 'away' from structural > > 'connections'. However these have been maintained as 'features' by some, usually >to promote speed/efficiency.
First off, I'll happily discuss this with you, if you want. I won't argue because we'll end up agreeing! At issue in the original conversation was not database theory, but re-training a person's thinking. The discussion was for the benefit of a Portugese guy who was protesting his abilities in English - a tall task for the explanation of technical philosophies or complex theory! You seem to have a background involving an overview/theoretical study of databases - like myself (first at Uni, and later falling victim to various IBM and Oracle training courses). You and I can chat about referential integrity, constraints, and the differences between them and various types of joins; but it's fairly rarified air up there! It is my observaton that many people coming to MySQL/this list, are 'graduating' from Access, FileMaker, Paradox, dBase-derivatives and suchlike (largely/originally) 'personal' systems. Consequently when they ask questions like: 'I can define two tables in SQL, but where is the 'line' that shows me how they relate to each other', the answer needs to be first related back to such a tool/the person's background knowledge; and then brought (sometimes kicking and screaming) 'forward' into the brave new world of unadulterated SQL/RDBMS. > Speed/efficiency is a minor issue. Not true the guy is into gaming and perceived response time is a major issue to gamers. On the more mainstream RDBMS front, see elsewhere (here or perhaps PHP-DB list) for a discussion about how Google searches appear to be so many order of magnitude times faster than MySQL fulltext retrievals (gripe, gripe). Within RDBMS practice, about the ONLY reasonable justification for de-normalisation is speed. Secondly, the reason why a number of applications don't use RDBMS is still speed - back when we were working with indexed files, ISAM and VSAM, IDMS, etc, it was a long-held belief (and an IBM mantra, for example) that one used 'efficient' file systems for transaction processing systems, and 'copied over' the data captured there (perhaps nightly) to a DB2/SQL database for 'inefficient'/expensive "user access". To me this is one of the grand attractions of MySQL - that by being able to strip away many of the non-aerodynamic features of RDBMSes (as are unnecessary to the particular application), you can achieve raw speed - and that when you must have referential-integrity (for example), you can ask the RDBMS to 'activate' such a facility. (now if I could just get Views...) Integrity constraints are used to > prevent people from writing queries that violate relational integrity > rules; e.g. create orphan records. I have to admit, I really did gloss over this - intending "efficiency" of the database application to include the dislocation of orphans, and ending up displaying an incomplete response to a query because of such, etc, etc. And integrity constraints are > unrelated to joins, except in the most incidental way. I concur - in SQL. However in a bid to 'update' their products, others use the term to define a fixed/permanent relationship between the tables, that goes beyond the logical because one can presume the join and talk about the 'second table' without formally mentioning its name - as you would in a SQL query. When such users graduate to SQL they ask questions like "how do I 'set' the join", and/or, the other way around "I joined these tables 'last time', but now they're not joined any more". I dispute the "incidental" part. The relationship of a FOREIGN KEY back to the 'main' table IS a stated relationship/join between the tables. It is not 'exclusive' to be sure, but if you think it worthwhile doing, then MOST of the time that will be where/how the two tables are joined when they are used together... It is possible > to use them to create joins, but that's not why they're built into > databases. Absolutely, and that's the way it should be - that's the way CODASYL wrote it - but as we all know, the problem with SQL is that it is not a restricted/regulated standard, and when moving from one SQL to another it is not the 'standard' bits that are the problem, but the extras and 'add-ons'. That was the primary issue (as I read it) of why the original question asked, was phrased the way it was. =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
