NYCPHP Meetup

[Jeff]

I had originally intended to do that, that is, normalize the data. The
idea was "killed" a long time ago by the client.

Let me make matters worse, so to speak. This data has a part code that
represents each different car part, e.g., brakes; fender, etc. However,
the company that creates the data (*not* my client) decided to use the
same exact part code number for different parts so that every electronic
module in a car has the part code "591" even though one module may
control air bags and one may control ignition, etc. Further, part
dealers know the parts by these 3 digit codes and can differentiate them
by their description (ignition; air bag, etc.). So it requires a part
code and text description to differentiate an air bag control module for
a Buick from one from a Chevy, etc.  Even the make/model information
that is provided is not complete....for example, some parts are lumped
in under Mercedes Benz even though there are many different models of
Mercedes. 

The bottom line is that the data is not pretty. 

Jeff

-----Original Message-----
From: Jim Hendricks [mailto:jim at bizcomputinginc.com] 
Sent: Thursday, June 19, 2003 7:20 PM
To: NYPHP Talk
Subject: Re: [nycphp-talk] MySQL and spaces in data


Jeff,

Why can't you build your own table for make/model?  You can run a
process
that parses each row of data lookup the string in your table.  If the
string
doesn't exist in your table, add it with a unique int ID.  If it exists
in
your table, move to the next row of your denormalized table.  What you
will
end up with is a table with 2 columns, ID and MakeModel.  ID is an int
set
as primary key, MakeModel is a varchar with a unique index.  Now you can
build your form using the ID's from your table rather than the MakeModel
string.

Even if the data is coming from another source, normalization of the
data
makes much more sense than trying to use the data in denormalized
condition.
Your app will perform much better and will be much easier to
troubleshoot
and maintain.  Even if the data is not under your control I would sugest
writing a background process that periodically parses the data and puts
it
into your own normalized tables.

Jim
______________________________________________________________
Jim Hendricks, President, Biz Computing, Inc
Phone:  (201) 599-9380     Email: jim at bizcomputinginc.com
Web: www.bizcomputinginc.com
Snail:  Jim Hendricks,  Biz Computing, Inc.,  255 McKinley Ave, New
Milford,
NJ 07646
______________________________________________________________

----- Original Message -----
From: "Jeff" <jsiegel1 at optonline.net>
To: "NYPHP Talk" <talk at nyphp.org>
Sent: Thursday, June 19, 2003 7:48 PM
Subject: RE: [nycphp-talk] MySQL and spaces in data


> The purpose:
>
> If a box is checked it means that the dealer sells parts for that
> particular make/model. This data is then used to match a purchaser to
a
> dealer(s) depending on the user's needs.
>
> Now I *wish* I could say that the makes/models had identifying keys
> however...things are worse than that. This data is matched against a
> table containing 2.5 million rows (that's right! 2.5 million rows) of
> non-normalized data that contains information on most every part for
> most every make/model made since about 1952. This data comes from
> another source and, therefore, there is nothing that can be done to
> normalize the data (it's a L-O-N-G story as to why it can not be
> normalized...makes/models are simply text strings within each row).
> Consequently, the text is needed because a match is made by matching
> text strings.
>
> Not the greatest system, I admit, but sometimes one has to work with
> what one is given.
>
> I've tried the str_replace and that seems to have done the trick (at
> least in preselecting check boxes based on post data) and I'll use it
> again to strip out underscores when I save it to the database.
>
> I'm open to suggestions if you have another idea.
>
> Jeff
>
> -----Original Message-----
> From: Analysis & Solutions [mailto:danielc at analysisandsolutions.com]
> Sent: Thursday, June 19, 2003 6:02 PM
> To: NYPHP Talk
> Subject: Re: [nycphp-talk] MySQL and spaces in data
>
>
> Jeff:
>
> On Thu, Jun 19, 2003 at 05:36:16PM -0400, Jeff wrote:
> > Dan,
> >
> > Thanks for your input...here's some more info on what I'm doing.
> >
> > <input name="ALFA-ROMEO|GTV-6" type="checkbox" value="1" checked >
> > <input name="ALFA-ROMEO|SPIDER VELOCE" type="checkbox" value="1">
> >
> > First part gives me the manufacturer, second part gives me the
model.
>
> But what you haven't clarified is the forms actual purpose.  Okay, so,
> you're processing the manufacturer and model in the checkboxes.  But
> what
> does it mean if someone checks the box?  In addition, what other data
is
> submitted?  What are you doing with all of this data?
>
> Before writing what I just wrote, I started writing the following, so
> I'm
> leaving it here to avoid having to restate it later...
>
> You mentioned using a database.  I assume each manufacturer and each
> model
> are represented by integer based primary keys.  Right?  Again, if you
> want
> to store both the manufacturer and model ID numbers in the name
> attribute
> of the name element, do this:
>
>    <input name="cars[5][23]" ... />
>    <input name="cars[5][50]" ... />
>    <input name="cars[10][2]" ... />
>
> Where the first key is the manufacturer and the second key is the
model.
>
>
> > The post data has underscores.
>
> Which is why I'm saying you should use use a different approach.
>
> --Dan
>
> --
>      FREE scripts that make web and database programming easier
>            http://www.analysisandsolutions.com/software/
>  T H E   A N A L Y S I S   A N D   S O L U T I O N S   C O M P A N Y
>  4015 7th Ave #4AJ, Brooklyn NY    v: 718-854-0335   f: 718-854-0409
>
>
>
>
>
>
>
>
> 
>
>
>
>



--- Unsubscribe at http://nyphp.org/list/ ---