[wp-trac] Re: [WordPress Trac] #5183: General Meta-Data Table

[WordPress Trac]

#5183: General Meta-Data Table
-----------------------------------------+----------------------------------
 Reporter:  filosofo                     |        Owner:  anonymous     
     Type:  enhancement                  |       Status:  reopened      
 Priority:  normal                       |    Milestone:  Future Release
Component:  General                      |      Version:  2.9           
 Severity:  normal                       |   Resolution:                
 Keywords:  needs-patch meta-table meta  |  
-----------------------------------------+----------------------------------

Comment(by mikeschinkel):

 Replying to [comment:29 Denis-de-Bernardy]:
 > There are occasions where you some DB integrity is very desired. When
 dealing with accounting-related data, for instance: if you delete a user,
 you need to be sure that the query will fail if it's about to delete lines
 in your accounting.

 Oh, agreed; right tool for the right job.  I didn't mention those use-
 cases because generally people don't use WordPress for accounting (though
 admittedly some do.)

 > When it comes to properly optimize the queries afterwards, joining posts
 with a table contains IDs from posts, comments, links and users won't fare
 as well as one on a table that contains IDs from a single one.

 Agreed. My biggest concerns are making sure there is what I like to call
 "an escape valve"; i.e. the ability for a plugin developer to add metadata
 for any arbitrary object w/o having to add their own table or to overload
 an existing table in a non-relational manner. If the team thinks link meta
 should be in a specific table I'm fine with that but do want to see a
 table that can handle arbitrary objects.

 > Now, if we introduce some kind of node ID so as to ensure no two pieces
 of data in posts, comments, links and users share the same numeric ID,
 that's a different story. Generic meta and term2node tables then makes
 perfect sense. But that's a much more massive change.

 I'd caution against a generic node ID; I think it adds too much complexity
 w/o enough benefits.  I also think that there is huge value to established
 structure and changing established structure should only happen when very
 significant improvements would come with it.

 > Expanding on the previous comment, the last thing want are joins that
 use indexes like (foo_id, vtype) or (foo_id, bar_id, vtype), where vtype
 is a varchar. We can use the vtype internally in php and simple queries,
 but at the end of the day, there needs to be a means to join tables using
 integers only for performance reasons.

 It's ironic. I came to WordPress/MySQL via a year of Drupal/MySQL but
 before that 12 years of ASP/VBScript/SQL Server where we *never* used
 anything except numeric keys (and with constraints in the database!) My
 first reaction to storing strings for keys was one of disgust but after
 working with both Drupal then WordPress I've really come to appreciate and
 even prefer them.  I do understand the performance concerns but wonder if
 the gains for moving to all integers is really worth the loss of usability
 for working directly with the database which I do all the time (for
 development.)

 Simply put: 15 years ago I was exactly certain the answers for all the
 rules about how database apps should be developed. Today I wonder if there
 really are any rules at all that don't fail in one use-case or another.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/5183#comment:31>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software