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" <[EMAIL PROTECTED]>
> To: "George Pitcher" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> 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
--
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]
