The Underscore Archives

On 08/03/07, Richard Price <richardprice at gmail.com> wrote:
> On 08/03/07, s'unya <sunya.dickman at gmail.com> wrote:
> > Hi,
>
> >
> > MY (not great) SOLUTION:
> > 1) Create a csv from the data in the database and output it for an
> > administrator so that they can simply copy and paste a csv as their means of
> > update.
> > 2) When changes are saved, data in the database table is Deleted and new csv
> > file is parsed into separate values and insert all into the database. I
> > toyed with UPDATE-ng the table until I ran out of records and then
> > INSERT-ing extra records, but that seemed to be adding a heck of a lot of
> > processing, so I am just DELETE-ing and INSERT-ing all data. As you can see
> > a little cringe-worthy
> >
> > SO:
> > What do you think I would be better off doing?

Just out of interest why does the client want to maintain their master
"database" as an excel spreadsheet and not work with their actual
database they've had built for their website?

I've worked on numerous data import projects and have developed
several different solutions but it depends upon what the client really
wants to do and how clued up they are.

If you really can't convince the client that having a single master
database on the website is a good idea (though you need to give them
the ability to administer it so clearly this involves building an
admin backend for which they probably don't want to pay given they are
happy to work in excel), then personally I would work with XML. I
think it's a much more elegant representation of data and allows you
to use schema to ensure the data is in the correct format prior to
attempting to update the db. Furthermore it allows for data
manipulation via XSLT so if the client wants the data output in a
slightly different way you can cater for that, and if for any reason
you change the underlying data model then you can tweak the import
without having to get the client involved. Potentially you could even
evolve this to a full web service.

There's nothing wrong with the CSV import method of just blowing away
the old data and importing the new providing the supplied data is
guaranteed to be a full copy of the db tables involved (I'm guessing
you archive the imported CSV files somewhere just in case). From what
I can tell this seems to be the natural choice by most for this kind
of import but I have reservations over data validation and this again
depends upon the complexity of the data model and whether the client
is good at getting it right.

Just some thoughts.

rick (not Rick)