David, > =I'm feeling a little 'at sea' here, because we had established that you want to learn more about "joining", yet > you recognise the word "normalisation" straight off. I don't want to insult you by 'talking down'/teaching > grandma to suck eggs... Please be sure it is not another one of those words that has a particular definition in > the relational world, but was subjugated by wiley marketing people at FM to mean something 'just a little > different' (alternatively that my ignorance of the product is the issue). Feel free to realign my approach...
No need to feel at sea. After an earlier response from either you or Miles, I read up on normalisation. > =Continuing on, in such a situation I am always inclined to try to 'cut corners' (against the 'standard' > structured analysis/book approach) - bet this comment gets some of our list colleagues gagging, already thinking > I'm long-winded in my methods. Because, as an outsider, I would not have such an intimate understanding of the > data items and their inter-relationships as you do, I would probably attempt to take the existing data > structures (the tables, and the list of fields/columns in each) and restate/extend these into something called a > ELH diagram (entity life history) - the theory of which you will find in any competent structured analysis and > design or SSADM text (the latter relevant to your location, SSADM being a British Government initiative, if > you'll pardon the oxymoron). I have done these flow charts in the past to demonstrate how my current system should work at a higher level. > =The purpose of an ELH diagram is to take a piece of data (in your case, because we are assuming/checking > normalisation, I'd 'cheat' and work at the table level - rather than something more atomic like the data-item > level). Once again we draw boxes (I have some wonderful s/w for doing these tasks, but it is M$). A single Visio? > label/box at the top, featuring the name of the data-unit, and I would guess a minimum of three (must be my > favorite number!) boxes in the next row, representing the arrival/creation of the data, its use within the > system, and the removal of the data from the system once its usefulness has subsided, respectively. The third > row of boxes represents the actual, individual events in the life-cycle/daily operation of the system, and how > they alter/update/use the data. Lines drawn between the boxes show how these events relate and where there may > be some iteration. > > =This is an analysis/checking tool. As such it bores the socks off most techies. However it is most useful to > ensure that data is being used properly, and for a consistent purpose. In theory it can also be used to check > the data structure because by following the uses to which a piece of data is put during its life, you can ensure > that it is being represented using the 'best' data type for the purpose. It can certainly be used to ensure that > you have the best design of relationships between multiple data items - both in the single table/normalisation > sense, but most especially in the relationships between tables. As I go, my diagrams accumulate a bunch of notes > around the margins with lead-out lines heading back into/from a point in the diagram - reminding me to check > things as the design (or in your case, verification) proceeds. Methodically iterating across dozens of diagrams, > the notes are removed - and the system data coalesces. > > =One of the great things about doing this, is that in examining where the data is coming from, going to, and how > it is being used; you are also making a list of all of the db queries that you will need to contain within your > system - yes you've guessed it, I have another bunch of boxes scattered around my diagrams (in a different > color, just to be petty, er, pretty) which note these needs. If you 'get into' a design book, they will talk > about other related diagrams, eg data-flow diagrams, which would normally be used to contain much of the info > I'm putting into margin-notes - like I said, I'm short-cutting and I think you'll also get away with it because > of your level of knowledge. Feel free to disagree (either way). > I will be going through the stages you have mentioned to map out the processes and data requirements. I think that I know the process enough to do this without reference to the 'users' considering that at present this is for purely self-development purposes. > =Once the diagrams are finished, or for a little light relief (of the diagram boredom) as I go along, I then use > a db admin tool for MySQL to start building the SQL code, and testing them against some sample data. Yes, I > could use the MySQL command line, but I am a fan of MySQL-Front because as well as the stark content of diagrams > and the code-view of the SQL DDL statements, the package gives me a visual representation of the columns/data - > another opportuntity for any incongruities to penetrate this thick skull... Oh, and you've probably guessed it, > whilst I use the diagram to settle on names for the .SQL files (and the tables, in design mode), I also return > to annotate the diagrams with the names of the .SQL files (just to keep track of what's done/ToDo) - old project > managers never die, they just keep on the critical path! > I too am using MySQL-Front as I like to be able to see the whole picture when I want. > =That stage has another embedded benefit, when you come to write/test the PHP code, you already have SQL > prototypes to copy-paste in! (that will require minimal testing, and that should obviate any debug step to > ascertain if a fault is at the PHP level or with MySQL) > > =Whether mixed in with the above, or done as a separate, progessive phase of the project, after the diagrams > comes the implementation in SQL. You should have a separate .SQL file to (re-)create every table (the first line > of which is a DROP IF), and one for every query or DML expression. As highlighted by Miles, a few msgs ago we > talked of the three conceptual boxes: MySQL, PHP, and HTML/browser. Then I made the comment that the more > processing that can be carried out in MySQL (cf PHP, as one might first assume) the more efficient will be the > system. Thus by developing the SQL queries in a separate stage, you will attempt to build in as much logic under > the "MySQL" box as possible - and not be 'distracted' into doing stuff in PHP! > One area where I remain a bit thick is when it comes to moving the functionality from the middle (php) box to the left (MySQL) box. How is it done in MySQL? Is it a case of restructuring the queries to handle the calculation or is there a pseudo-Stored Procedures (cos I know that MySQL doesn't use Stored Procedures)? > > > =Have I misunderstood? It seems to me that you are not offering this data > > to "the web", ie I can't get to it; > > > you are only offering it to the copyright fee-paying clients. Hence the > > publishers' argument seems > > > illogical/ignorant... > > Yes, exactly, but they control what happens and we are not mature enough as > > a service to hit them over the heads yet. > > =revenge is a dish best sampled cold? > However, my job here depends on the service succeeding in raisng funding for continuation beyond July this year else I'll be looking for a company willing to employ someone with a good overview and no extensive skills! George --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.307 / Virus Database: 168 - Release Date: 11/12/01 _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
