Re: Concept help required

George, =I was pleased to hear from Miles. There are likely to be as many suggestions for how things should be done, as there are developers. I'll be interested to hear from others - personally (cf on the list) if the comments might 'interfere'/argue a case (and thus risk muddying your waters) or 'in public'. > > =Prevailing wisdom in system design suggests that the data should be > 'designed' first, and 'code'/processing > > only later (relational or structured design philosophy, even > object-oriented design). Accordingly I recommend > > considering which parts of your current files should be converted into > MySQL tables, and what might need to be > > added/subtracted to ensure that the relationships between tables is > adequately expressed/because that makes > > other 'old data' unnecessary. As you would seem to have identified your > data, and grouped/categorised it into > > tables, you might be able to go straight into the process of 'normalising' > your data - a series of > > steps/techniques which enable you to analyse the data and structure it > into a 'relational' form. (if you are not > > familiar with this term: it's back to the books) > Actually the Filemaker system is fairly 'normalised' having been developed > over a couple of years, our previous web developer (commercial partner) > pulled out of our service an I stepped in to deliver the goods and in doing > so, went through the analysis and re-design process. It may well be that > there could be some marginal improvement in doing it again. I'll have a look > anyway - its part of the learning process. =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... =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). =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 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). =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! =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! > > =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? =Regards, =dn PS off to take the CO to the hospital now. Back tonight/tomorrow.