Re: Concept help required

Hi George, > > =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... > 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. =ex-Navy guys are always "at sea" - however they never let me near the ships, but I guess that's another story... > > level). Once again we draw boxes (I have some wonderful s/w for doing > these tasks, but it is M$). A single > Visio? =yes - but as part of my venturing into LAMPs, I have been pointed to an equivalent package under Linux. > > =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 ... > 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. =understood - and so whilst you may not 'improve' the system design (the primary objective when developing on a 'green field' site) it will help in your conversion to SQL/relational technology, as per below. > > =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... > 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)? =correct MySQL does not CURRENTLY have stored procedures, but it is under active discussion (too late for us, but then...). =many people have a very shallow understanding of SQL - particularly [he says generalising like crazy] people who 'fall into it' from (say) PHP programming. Indeed my own initial training course [mumble, mumble] years ago majored on SELECT, charged through INSERT and DELETE, and settled lightly on DML. However there is enormous power in the SELECT statement that belies the usual course topics of SELECT *... and SELECT colName, colName, ... and a bit of format control/changing the column names/labels. In my training course, and many others I've seen since, token gestures are made so that even throwing in MAX(), MIN(), and AVG() seems more an illustration of (the more narrow) GROUP BY clause than it does of the SELECT statement. [rant, rave,...] This shallow understanding means that 'they' will tend to do too much in PHP (assuming they know it better) in preference to SQL - at a cost of efficiency/execution time. =let's make this answer a 'game of two halves': firstly, if you followed my earlier point, after producing ELH diagrams, (my)/the next step is to start writing SQL queries. Thus one tries to pack as much functionality into the SQL statement, as is possible. Each SQL query will feed some response 'back' to the PHP code (that in the finished product, will first call it). Thus if you throw together the system's SQL calls in a previous development step, the only PHP functionality required is that which cannot be accomplished within MySQL - so my terminology may be flawed/deceptive, it is not that I'm taking stuff out of the PHP code (I haven't written any yet/at this stage), it's that it never gets in there in the first place! Remember the mantra: prevailing wisdom says that if you have a choice of doing something in SQL or PHP, do it in SQL. =there's an interesting problem on the list (in fact both PHP and PHP-DB) posed by Brian Tully "need help looping through each record with a query -stumped". It is a much smaller/self-contained example than your own. He has presented his 65-line, mainly-PHP code in his statement of the problem. It provided a brain-starting challenge for me this morning, and I have opened my big mouth to suggest that we could get it down to a much less complex single SQL call and one or two nested loops of PHP. To do this, I have requested some clarification of the business rules governing his case. If it suits you, and assuming he gets back to me, I will work through it. Could we then use this as an example of how to shift functionality out of PHP (the 'middle box') and into SQL (the 'left-hand box')? > 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! =powerful motivation indeed. Let's see what we can do... =dn