addressalign-toparrow-leftarrow-rightbackbellblockcalendarcameraccwcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-checkcircle-with-crosscircle-with-pluscrossdots-three-verticaleditemptyheartexporteye-with-lineeyefacebookfolderfullheartglobegmailgooglegroupsimageimagesinstagramlinklocation-pinm-swarmSearchmailmessagesminusmoremuplabelShape 3 + Rectangle 1outlookpersonJoin Group on CardStartprice-ribbonImported LayersImported LayersImported Layersshieldstartickettrashtriangle-downtriangle-uptwitteruseryahoo

Re: [TriPUG] [mysql-144] Tripug: PHP Best Practices: SQL column prefixing

From: Michael K.
Sent on: Wednesday, August 18, 2010 11:53 PM
I did explicitly state that doctrinev2 provides a way to separate
column names from db field names - no argument there.  I simply don't
like the docblock style of doing it.  It clutters the code with stuff
that would ideally be separated out some.

Having been spoiled with GORM from Grails (believe, it's not perfect -
I have issues with it too), I like having different sets of
information separated out.

class person {

  String name
  String email
  String phone

  static constraints {
    name(nullable:true, maxSize:30)
    email(nullable:true,­ email:true, unique:true, maxSize:50)
    phone(nullable:true,­ maxSize:20)
  }

}

compare this to the php doctrine equivalent (using docbloc style)

/** @Entity */
class person
{
    /** @Column(type="intege­r") */
    public $id;
    /** @Column(length=50) */
    public $name;
    /** @Column(length=50) */
    public $email;
    /** @Column(length=50) */
    public $phone;
}


I like that the constraints which influence schema generation are
grouped together, and the field names can be grouped together as well.
 With the PHP docbloc approach, I necessarily have to visually break
up the class properties to interject SQL-related information.  I find
that approach distracting.

Assuming we wanted to change the field names, we'd have

/** @Entity */
class person
{
    /** @Column(type="intege­r") */
    public $id;
    /** @Column(length=50, name="myname") */
    public $name;
    /** @Column(length=50, name="myemail") */
    public $email;
    /** @Column(length=50, name="myphone") */
    public $phone;
}

and what I'm more used to:

class person {

  String name
  String email
  String phone

  columns {
    name column:'myname'
    email column:'myemail'
    phone column:'myphone'
  }

  static constraints {
    name(nullable:true, maxSize:30)
    email(nullable:true,­ email:true, unique:true, maxSize:50)
    phone(nullable:true,­ maxSize:20)
  }

}

The grails/gorm one is getting longer, but simply feels cleaner and
more readable to me, even with the verbosity.

Again, given the constraints of PHP, that may be the best we can hope
for, but doesn't mean I have to enjoy it. :)

Oh, and more to the original point of "You (ideally?) are already
doing comments on your classes/models that way".  I don't do phpdoc
comments on field names, precisely because A) it breaks up the visual
consistency and B) 90+% of what would be added is redundant.  Properly
descriptive field names and a consistent approach is going to
alleviate the need for nearly all field-level docbloc info.  In rare
cases, it can be added, but ime those are the exceptions, not the
rule.

I'd prefer strong unit tests over docbloced fields any day of the week :)


On Wed, Aug 18, 2010 at 11:38 PM, Neal Anders <[address removed]> wrote:
> Whats not to love about docblock annotations!? You (ideally?) are already
> doing comments on your classes/models that way... which rolls up into your
> phpdoc output, etc.. and you avoid the overhead of maintaining (and having
> to remember if you change something) a separate YAML/XML files. (now thats
> not to say that comments 1-line above a bit of code get neglected too!)...
>
> Doctrine v2 will let you specify a column name separate from a model
> property:
>
> ??? ?/* @Column(name="fld_fo­o") */
> ??? private $foo;
>
> .. that's the beauty in it, that you can 'mask' the cruft of db/field/column
> naming into something pure and data-store agnostic!
>
> - Neal
> ____________________­____________
> From: Michael Kimsal <[address removed]>
> To: [address removed]
> Sent: Wed, August 18,[masked]:22:07 PM
> Subject: Re: [TriPUG] [mysql-144] Tripug: PHP Best Practices: SQL column
> prefixing
>
> This is another reason I like GORM, and would use it except it doesn't
> exist for PHP.
>
> http://www.grails...­
>
> It's pretty straightforward to separate the class field names from the
> database field names.
>
> It looks like this is possible in doctrineV2 as well, although I'm
> just not in love with the idea of comment-level annotations.? For
> better or worse, that's probably all we'll get in PHP.
>
>
> On Wed, Aug 18, 2010 at 11:14 PM, Neal Anders <[address removed]> wrote:
>> Having recently started to use ORMs (Doctrine v2 in this case) I'd have to
>> say that field naming can play very intimately into you actual
>> classes/models/code.­ If you are not careful then prefixing can end up
>> seeping into code in ways you might want to avoid. What do you do when
>> your
>> objects are stored in multiple data stores (say mysql and mongodb,
>> depending
>> on staleness)?? Suddenly you have have relational db concepts bleeding
>> over
>> to document-based... it gets messy.
>>
>> - Neal
>>
>> ____________________­____________
>> From: Michael Kimsal <[address removed]>
>> To: [address removed]
>> Sent: Wed, August 18,[masked]:44:37 PM
>> Subject: Re: [TriPUG] [mysql-144] Tripug: PHP Best Practices: SQL column
>> prefixing
>>
>> Naming the columns when aliasing makes sense - indeed, it's
>> *necessary* in some cases.? Just to be clear, it's not what I was
>> railing(!) against.? I was cautioning against prefixing field names
>> with redundant and unnecessary info at the schema level.
>>
>> create table tbl_foo {
>> fld_id int(11),
>> fld_email varchar(30),
>> fld_name varchar(30)
>> };
>>
>>
>>
>> On Wed, Aug 18, 2010 at 10:32 PM, Simon <[address removed]> wrote:
>>> ?>>> Database field names should not be prefixed with "field_" or "fld_"
>>> or, really, anything.
>>> I've used column (field) prefixing to avoid ambiguity when tables with
>>> some common column names are joined and the data is being retrieved
>>> using the column names (mysql_fetch_assoc()­, mysql_fetch_object()­, etc).
>>> For example:
>>>
>>> ? ? select * from table1 left join table2 using(stocknum);
>>>
>>> If table1 and table2 both have column 'id, what does $result['id']
>>> actually refer to - table1.id or table2.id?
>>> What if both are needed ?
>>> (rhetorical questions :)
>>>
>>> Prefixed column names (tbl1_id and tbl2_id) avoids the issue without
>>> getting into numeric index type retrieves (e.g. mysql_fetch_array() ) or
>>> having to code unique column alias' on each select, for example:
>>>
>>> ? ? select table1.id as tbl1_id, table2.id as tbl2_id from table1 left
>>> join table2 using(stocknum);
>>>
>>> Simon....
>>>
>>> On 8/16/[masked]:13 PM, Michael Kimsal wrote:
>>>> Database field names should not be prefixed with "field_" or "fld_"
>>>> or, really, anything.
>>>
>>>
>>>
>>>
>>> --
>>> Please Note: If you hit "REPLY", your message will be sent to everyone on
>>> this mailing list ([address removed])
>>> http://www.meetup...­
>>> This message was sent by Simon ([address removed]) from The Raleigh
>>> MySQL/PHP Meetup Group.
>>> To learn more about Simon, visit his/her member profile:
>>> http://www.meetup...­
>>> To unsubscribe or to update your mailing list settings, click here:
>>> http://www.meetup...­
>>> Meetup, PO Box 4668 #37895 New York, New York[masked] |
>>> [address removed]
>>>
>>>
>>
>>
>>
>> --
>> Michael Kimsal
>> http://jsmag.com­ - for javascript developers
>> http://groovymag....­ - for groovy developers
>> http://indieconf....­ - web freelancer conference
>>[masked]
>>
>>
>>
>> --
>> Please Note: If you hit "REPLY", your message will be sent to everyone on
>> this mailing list ([address removed])
>> http://www.meetup...­
>> This message was sent by Michael Kimsal ([address removed]) from The
>> Raleigh MySQL/PHP Meetup Group.
>> To learn more about Michael Kimsal, visit his/her member profile:
>> http://www.meetup...­
>> To unsubscribe or to update your mailing list settings, click here:
>> http://www.meetup...­
>> Meetup, PO Box 4668 #37895 New York, New York[masked] |
>> [address removed]
>>
>>
>>
>>
>>
>> --
>> Please Note: If you hit "REPLY", your message will be sent to everyone on
>> this mailing list ([address removed])
>> This message was sent by Neal Anders ([address removed]) from The
>> Raleigh MySQL/PHP Meetup Group.
>> To learn more about Neal Anders, visit his/her member profile
>> To unsubscribe or to update your mailing list settings, click here
>>
>> Meetup, PO Box 4668 #37895 New York, New York[masked] |
>> [address removed]
>
>
>
> --
> Michael Kimsal
> http://jsmag.com­ - for javascript developers
> http://groovymag....­ - for groovy developers
> http://indieconf....­ - web freelancer conference
>[masked]
>
>
>
> --
> Please Note: If you hit "REPLY", your message will be sent to everyone on
> this mailing list ([address removed])
> http://www.meetup...­
> This message was sent by Michael Kimsal ([address removed]) from The
> Raleigh MySQL/PHP Meetup Group.
> To learn more about Michael Kimsal, visit his/her member profile:
> http://www.meetup...­
> To unsubscribe or to update your mailing list settings, click here:
> http://www.meetup...­
> Meetup, PO Box 4668 #37895 New York, New York[masked] |
> [address removed]
>
>
>
>
>
> --
> Please Note: If you hit "REPLY", your message will be sent to everyone on
> this mailing list ([address removed])
> This message was sent by Neal Anders ([address removed]) from The
> Raleigh MySQL/PHP Meetup Group.
> To learn more about Neal Anders, visit his/her member profile
> To unsubscribe or to update your mailing list settings, click here
>
> Meetup, PO Box 4668 #37895 New York, New York[masked] |
> [address removed]



-- 
Michael Kimsal
http://jsmag.com­ - for javascript developers
http://groovymag....­ - for groovy developers
http://indieconf....­ - web freelancer conference
[masked]

Our Sponsors

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy