DL Neil (I presume you have a first name tucked away inside there),
Your comments are appreciated. I am becoming more and more comfortable with
what I am doing with MySQL/PHP.
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.
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.
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.
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.
Regards
George
----- 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]
---
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]
