NYCPHP Meetup

[joshmccormack at travelersdiary.com]

On the subject of PHP, SQL, joins and the like, I encountered something a bit weird and wanted to see what all of you had to say on it.

 This is from the manual:

"mysql_fetch_array() is an extended version of mysql_fetch_row(). In
 addition to storing the data in the numeric indices of the result array,
 it also stores the data in associative indices, using the field names as
 keys.

  If two or more columns of the result have the same field names, the
 last column will take precedence. To access the other column(s) of the
 same name, you must use the numeric index of the column or make an alias
 for the column. For aliased columns, you cannot access the contents with
 the original column name (by using 'field' in this example)."




  so I have address fields, for example, that overwrite eachother. I
 either have to make up wacky names for the database columns, or I have
 to write out AS names for each (so using Select * is out). Now why
 wouldn't they throw in an option so your results have table names in
 front? So client.zip, owner.zip, etc. could all coexist? Doesn't that
 seem reasonable?


Josh



On Tue, 5 Aug 2003, jim.bishop wrote:

> > I believe the best way to normalize your tables is to apply the concept
> > of wether the information is related to that ID and is not going to
> > change or need to be archived. For example the users first and last name
> > and username/password and date of birth, etc are only related to this
> > user and there is no reason to have additional tables, Addresses on the
> > other hand can change and history can be important depending on your
> > project so a separate table for them is appropriate.
> 
> 
> i had been thinking of that as well.  i think you're right about the JOIN
> nightmare to follow.  i just wanted someone else to tell me that it would
> be a nightmare, so i wasn't not trying it because it seemed too abastract.
> 
> thanks!
> 
> 
> 
> >
> > Your structure is good for random data that will change or be added
> > later but for specific data that is unique to that ID and not going to
> > change your prior table structure is the most efficient and easiest to
> > code. Having over normalized (still up for debate) previous databases
> > the data is nice and makes sense but writing extensive joins down the
> > road become gruesome. I would recommend a blend of these two techniques.
> >
> > Planning out your database with a nice entity diagram will make your
> > life much easier and you should have the fields defined pretty well
> > before you create your first table. Of course this is the old boring
> > way but elliminates mistakes that will haunt you down the road.
> >
> > Hope this helps.
> >
> > On Tuesday, August 5, 2003, at 09:16 AM, jim.bishop wrote:
> >
> > >
> > > I'm designing a new user database for a personal project, and I was
> > > toying
> > > with a different method of storing the data than I have used before.
> > > Usually I create tables with logical column names that reference the
> > > data
> > > in the column.
> > >
> > > For Example:
> > >
> > > ---------------
> > > | User        |
> > > ---------------
> > > | id          |
> > > | first_name  |
> > > | last_name   |
> > > | email       |
> > >
> > > ...
> > > ---------------
> > >
> > > But this becomes cumbersome when you have to add fields to the table.
> > > I
> > > have designed a new table structure that looks like this:
> > >
> > >
> > > ---------------
> > > | User        |
> > > ---------------
> > > | uid         |
> > > ---------------
> > >
> > > -----------------
> > > | UserAttribute |
> > > -----------------
> > > | attribute_id  |
> > > | uid_fk        | * foreign key to user table
> > > | attribute     |
> > > | value         |
> > > -----------------
> > >
> > > Okay.  So I've built many user databases before and never employed the
> > > system below.  Has anyone else?  Besides the requisit JOIN that has to
> > > be
> > > called with every SELECT, what is the downsides to using this kind of
> > > architecture?
> > >
> > >
> > > _______________________________________________
> > > talk mailing list
> > > talk at lists.nyphp.org
> > > http://lists.nyphp.org/mailman/listinfo/talk
> > >
> >
> > _______________________________________________
> > talk mailing list
> > talk at lists.nyphp.org
> > http://lists.nyphp.org/mailman/listinfo/talk
> >
> 
> 
> 
> 
>  -------------------------------------------------------
>  -------------------------------------------------------
>  -> the syntax of cummings, the absurdity of faulkner <-
>  || jimbishop.org  ||   jim.bishop   ||  i heart n y  ||
>  -------------------------------------------------------
>  -------------------------------------------------------
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk
>