Re: Concept help required

George, > DL Neil (I presume you have a first name tucked away inside there), =David > Your comments are appreciated. I am becoming more and more comfortable with > what I am doing with MySQL/PHP. =I have found it to be an excellent combination (even on Windows) and have used them as the means of a gradual introduction to the LAMPs platform. > With reference to the 3-box trick, I thought that the bulk of my work would > be inthe centre box but as you point out that would be inefficient if the > work can be pre-processed in the RDBMS box. =Yes, the simplistic diagram has served its purpose! The right-hand box is 'done' (as far as design is concerned) - per earlier comments. Thus it would be tempting to move one step left/to the center. There are two ways to approach a design: 'bottom-up' or 'top-down'. Many coders have taught themselves and tend towards 'bottom up', ie start with the first line of code and iterate/improve their implementation until the program actually does what seems to be required. Project managers and designers prefer to work top-down: show me the whole picture and then let's get it split up into component parts until those parts are small enough to be a single, easily implemented unit. =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) > A simple explanation of my Filemaker system follows. But first, a > description of what the service provides might help. > > Our members (50+ uk universities) can request material (usually book > chapters or journal articles) to be delivered electronically (or rarely, by > paper) to their students. We handle copyright clearance through the UK > rights agency (CLA) and through publishers/authors. We pass the prices back > to the clients via the web interface and the client accepts/declines. We > then source originals from the British Library which are digitised by a > bureau and put into PDF before a front page is attached (currently automated > using Applescript but hope to use PDFlib in future) and delivered to the > university. We invoice monthly for items completed, not by course. We have a > success rate of 60% mainly due to the reluctance of publishers having their > material mounted on the web. =thanks for this, now I have a better view of where you are going. =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... > Now the system: (number of current records in parentheses) > > Transactions (15000+) > [contains a record for each requested extract with workflow and cost > information] > Course (900+) > [Holds data for courses such as student numbers, dates etc] > Bib_source (6000+) > [Book or journal data held here at title level] > Bib_extract (9000+) > [Chapter/article level data held] > Publishers (18000+) > [Rightsholder details data bought in] > Customers (50+) > [Client details] > Buyers (200+) > [Individuals who can make purchasing decisions at clients] > Illustrations (150+) > [Illustrations require special handling and there can be several per > extract] > Invoices (300+) > [data taken from Transactions and Customers to produce PDF invoices] > Userlog (4000++) > [log of users accessing main system] > Weblog (500+) > [covers whole site and started in December] > Staff (10+) > [Staff names, addresses, emails etc] > Scanrates > [CLA-provided table to store pre-priced material - covers about 40% of > requests] > Helpdesk-general > [General helpdesk alloowing LAN-wide staff access and direct responses to > users] > Helpdesk-transactional > [As above but specifically set up to handle problems about individual > transactions] > > I run a dual site with a main 'Live' service and a Training service allowing > users to play with the processes before they get near the real thing. =and now a third environment: for development, and a fourth: for system testing... > I'll need to leave now to pick the kids up from school as the wife is ill. > I'll see your comments tomorrow when I get back in. =I have similar issues. I'll be around tomorrow (Wed) morning (GMT), but unlikely to get back during the afternoon. =Regards, =dn > ----- Original Message ----- > From: "DL Neil" <PHPml@DandE.HomeChoice.co.uk> > To: "George Pitcher" <fmdev@yahoo.co.uk>; <php-db@lists.php.net> > Sent: Tuesday, January 08, 2002 1:07 PM > Subject: Re: [PHP-DB] Concept help required > > > > George, > > > > > > =As a general comment, it is always dangerous to "replicate" when > shifting > > > platforms, better to reverse engineer > > > > and then implement anew and taking advantages of the strengths of the > new > > > tools. This particularly when moving > > > > into the relational field... > > > Perhaps the use of the word 'replicate' was wrong. I am in fact > > > re-engineering based on my knowledge of how the whole operation is > performed > > > (as I designed and built the original Filemaker/Lasso system) and trying > to > > > preserve the look and feel of the web pages. > > > > =makes perfect sense > > > > > > =you will need to describe the 'internal calculations' before this > > > question can be easily/sufficiently answered. > > > > However many people fail to appreciate that the (My)SQL language > offers a > > > lot of power/functionality. In your > > > > case you are going for the PHP combination so I will be quite > surprised if > > > you 'run out' of functionality! > > > The original Filemaker (FM) databases use calculations stored > internally. > > > You define a field to store the result of a calculation. This could be > > > something quite complex or a static number (or string) or data from a > > > related database. Filemaker requires a separate database to represent > the > > > equivalent of a table in standard SQL databases. Some calculation fields > can > > > be indexed and some cannot (esp those containing related data). I expect > > > that I can replace these calculations with functions which I define. > > > > =it sounds as if you are still getting to grips with the advantages and > power that SQL and relational databases > > bring to 'filing' tasks. There also is a terrible possibility of > terminology pollution/confusion. When I last > > looked at FM (many, many moons ago), I consigned it to "file 13" as being > too much of a 'shoebox' style > > 'database', and my being more interested in something PC-ish that would > run something more like a > > table-relational model (if not SQL), eg Paradox or even Access (make signs > for protection and mutter > > incantations to ward off the evil eye...) The problem with 'shoe box' > packages was that they prefered > > single-file solutions - it was difficult/impossible to 'relate' two files, > except by producing procedural code > > in the package's language. Thus the data itself did not define the > linkage, as it does in the relational view of > > the world (which I was more comfortable with coming from a mainframe-view > of the world). Relational databases > > are made up of multiple related tables (for table you can read "file", in > MySQL). Shoe box databases are files. > > Thus there is no "multiple" and without the code no 'relating'. Does > > this > make sense? Is it a fair portrayal? > > That being the case, you need to jettison your current understandings of > some terms and FM concepts, to be able > > to take on board MySQL and relational technology and terminology... > > > > =let's try drawing a picture to 'see' the model you are contemplating > using to upgrade your system: imagine > > three boxes in a row across the page. Label the left-hand one "MySQL", the > center one "PHP", and the one on the > > right-hand side "HTML" or "browser". We can then add the following > functional descriptions: 'holds the data', > > 'handles the processing/calculations', 'displays output and collects user > input'. Working at this level it > > should be really easy to now add a diagram for the existing FM setup; and > further to draw correspondences > > between 'existing' and 'proposed'. However this is really simplistic, > mainly because you can shift a lot of > > 'calculation' that FM requires/embodies, out of the central box and over > to "MySQL". For example, the table > > relationships, eg a link between the name of a 'package' in a list/table > of packages, and the names of the > > (multiple) books within the package in a list/table of books. The > 'functionality' can be implemented using the > > power of the SQL language (see talk of dates etc, below), but much will be > 'hidden' within the standard > > functionality of the RDBMS engine and/or implicit in the relational model. > (yes, back to the 'more reading' > > theme). > > > > =If you want to persue this discussion a bit further, could you list the > current FM 'databases'/files and > > (briefly) describe how they 'fit' together? > > > > > > =as you can see, without giving a little more information, it is very > > > difficult to give a satisfactory answer. > > > > How about listing your table definitions/schema. Almost any retrieval > > > operation that does not select all of the > > > > records in a table will speed up when indexes are employed. If speed > is a > > > concern then that argues against the > > > > earlier suggestion of PostGres. > > > I think that the table definition list would be far too long for this > list. > > > Speed is an issue as at the moment I am doing all this under my own > steam in > > > my own time (partly to extend my skills) and hope to be able to persuade > my > > > bosses that this would be a beneficial move (they are very > conservative). > > > Performance improvements would help. > > > > =you are talking as if there are numerous queries. What's wrong with > > > performing a join, or am I missing some > > > > significance? > > > No but I probably am. I have been working almost exclusively with > Filemaker > > > for the past 7 years moving from Mac to Win NT in the process, with a > short > > > flirtation with MS Access and ASP. I'm really a SQL newbie and am > gradually > > > getting to grips with what is possible. JOINs are alien to me and I'll > ned > > > to read up and experiment with them to see how they work and how I can > best > > > use them. > > > > =as above. If you are at a (?Scottish) university, you shouldn't have too > much trouble finding sources. > > Elsewhere on this list you will see "MySQL" by DuBois recommended, also > Welling and Thomson's "PHP and MySQL Web > > Development". However for your purposes almost any under-grad text > introducing relational databases and SQL will > > probably suit. If you don't understand joins and the concepts offered, > then you are completely missing out the > > point/power of relational technology; and what on earth we've been > rabbiting on about... > > > > > > > >Dates > > > > > >I played around with my learning site over the holidays and found > that > > > I was > > > > > >not able to easily handle dates between the format required by > users > > > > > >(dd/mm/yyyy) and that used by MySQL (yyyy-mm-dd) and therefore I > wrote > > > > > >functions to parse the data both ways. All the example I could find > on > > > Dates > > > > > >used 'today' as the example. I want to be able to play around > > > > > >with > > > stored > > > > > >dates. Is my function method the correct way or is there another > way? > > > > > Yes - but why can't we convert the world to that oh-so-simple date > > > format > > > > > of year,month,day which sorts and indexes so beautifully and is > > > completely > > > > > unambiguous. > > > > > > > > =Are you talking about functions implemented in PHP? Refer above, my > > > earlier comments on the power of SQL, check > > > > out: > > > > > > > > SELECT DATE_FORMAT( colNm, format ) FROM tblNm > > > > > > > > in the manual at 6.3.4 Date and Time Functions. I'll be surprised if > the > > > long list of 'formats' doesn't give > > > > you more than enough options to keep (even Uni students) quiet! > > > > > > I wrote my own functions to handle dates in the way I am comfortable > with. > > > In dbdate() $input is the date pulled from MySQL and in revdate() $input > is > > > the dd/mm/yyyy date being grabbed from the users form data. > > > > > > function dbdate($input) > > > { > > > $today = explode("-",$input); > > > $month = $today[1]; > > > $day = $today[2]; > > > $year = $today[0]; > > > $p_date = $day . "/" . $month . "/" . $year ; > > > return $p_date; > > > } > > > function revdate($input) > > > { > > > $ztoday = explode("/",$input); > > > $day = $ztoday[0]; > > > $month = $ztoday[1]; > > > $year = $ztoday[2]; > > > $revdate = $year . "-" . str_pad($month, 2, "0", STR_PAD_LEFT) . > > > "-" . > > > str_pad($day, 2, "0", STR_PAD_LEFT); > > > return $revdate; > > > } > > > > > By the way, my site is for university staff not sudents. > > > > =by exercising supreme self-control I shall not make any smart comment > here...I've worked in/for my Uni, as well > > as being a (lousy) student. > > > > > I welcome any comments and hope to learn by implementation. > > > > =Sorry - this exercise was so unnecessary in that all of this > functionality could be handled by MySQL - however > > there is no such thing as 'waste' when you're in a learning situation. You > have learned quite a bit about the > > power and capabilities of PHP, which I'm sure is making you think of > numerous advantages over the possibilities > > offered by FM. > > > > =Returning to our 'diagram' for a moment, it is prevailing wisdom that the > more 'processing' you can shift from > > the traditional 'central box' to the (RDBMS) 'box on the left', the > > more > efficient will be the final result > > (read: speed). Remember that whilst the purpose of a 'file' is to > 'contain' data, databases are designed to > > 'process' data as well. For example (using the two tables drawn from my > weak understanding of your application, > > as above) the 'packages' table is something of a 'summary' view of the > data contained in the 'books' list (in so > > far as a particular sub-set of the books pertain to a single 'package'). > Thus if we have package X, it is > > child's play in MySQL to ask the system to retrieve the names of all of > the books (A, B, and C) in that package > > for you: > > > > Table: tbl_packages > > 1 X > > 2 Y > > 3 Z > > > > Table: tbl_books > > 1 A > > 2 B > > 3 C > > 4 D > > 5 E > > 6 F > > > > Relational Query (in SQL): > > SELECT packageName, bookName FROM tbl_books, tbl_packages > > WHERE tbl_packages.packageNr = tbl_books.packageNr AND > tbl_packages.packageName = "X" > > > > giving: > > X A > > X B > > X C > > > > (which you can then pick up in PHP to 'massage' and output as a report in > HTML for display in the > > client-browser... - for the purposes of the example I have determined "X", > but it could quite happily have come > > from a previous user input/the browser, into PHP which checked and > formatted it before throwing the generated > > query at MySQL) > > > > BTW: The first half of the WHERE clause defines the 'join', ie shows how > the two tables are to be 'related'. > > This sort of definition can be replicated ad-nauseum so that many > tables/lists can be related to each other. > > > > =Regards, > > =dn