Mapping the data to MySQL
It would have been nice to move on to XSLT, XPATH and XQUERY but it was noticed that many of you are still trying (and struggling) to crunch the original CSV file to feed MySQL.
Let's take a step back to the suggested ER model and consider why this may not be a good idea.
Can you notice some of the design decisions and subtleties in this model?
- field names are short and unique across the model
- foreign key names are reflections of primary key names
- the year is included as part of a composite primary key in the ward_tmode entity - why?
Once the table (SQL) schemas have been mapped to MySQL (use workbench), we will need a program to generate & execute the INSERT statements.
But there's a problem getting at the data given the structure of the original CSV file.
Pulling the (unique) ward details and (unique) transport types from the CSV file isn't too difficult but generating the inserts for the join table "ward_tmode" is much trickier. We will have to generate keys.
Or is it?
We could insist that our first instinct was right and stick with the original CSV file. Processing this isn't impossible after all. We could use fancy PHP or MySQL counters and loops and regular expressions and (possibly) end up writing lots of spaghetti code.
However the effort wouldn't be effective or energy efficient. Remember that according to Larry Wall, one of the three great virtues of a programmer is Laziness. "The quality that makes you go to great effort to reduce overall energy expenditure." Or as we say here in England, "leaving time for the Pub".
We already have a DOM structured document transport_v1.xml holding the data and this (structured) format makes our job of feeding MySQL much easier.
Consider the following script using XPath 1.0 & PDO with prepared statements:
Deconstruct the script for questions, clarifications and criticisms. We will allocate 10 minutes at the begining of Monday's lecture for this purpose.
No comments:
Post a Comment