[wp-hackers] questioning the efficiency of using custom post types

[Haluk Karamete]

Coming from a web application development background using ASP, I
found wp's way of handling the custom fields a little different and
inefficient. I may be wrong, so hence the post.

say, i need to build a web site that has tons of restaurant reviews.
in a typical web app, i would first create the restaurant table and
add all the fields i need it to describe a restaurant and go from
there.

So, as an example, I would add fields such as recID, rName, rAddress,
rOwner, rType, rThis, rThat...

And when I add a new restaurant, I just add 1 record into this table
and when I query that table, I can get all the info with a simple
select * from thatTable

Of course, this approach gives me a tremendous search and sort power as well.

now, if I were to do the same thing with WP, I would first create a
custom post type called restaurants and then add each and every field
that I need as a custom field. that needless to say bloats the
wp_post_meta table causing an exponential growth for every restaurant
added;

so if my restraurant type has 20 fields to describe it, I end up
creating 20 records into the  wp_post_meta. coupled with the original
wp_post entry, that would make 21 records.
whereas in the case of a custom restaurant table approach I described,
you only end up with 1 record!

this makes me think.. the more the restaurant entries, the less
efficient WP will get here...  and when it comes to do complicated
power searches against the restaurants such as find me all restaurants
whose field1=val1 and field2 contains val2 and so on, the effiicieny
will go out the window.

Of course, wp went along way with this structure/approach  because it
allows anyone and everyone to implement whatever the post type is to
get away with just two tables...  the wp_posts and the wp_post_meta
was enough for millions of people for all kinds of post types, and the
wp system works like a charm and I admire that.

my question is is this approach right for me ? can this be made more
efficient...

I have the following idea...  please feel free to give your opinion as
to why it would be good or bad...


* create a custom restraurants table into mySQL and add all the fields
you need into it.
* create php form letting you add/edit/delete restaurants...
* everytime you add a new restaurant into that mySQL table, also add a
programatic post entry into the wp_posts table ( using wp_insert_post)
relating to the corresponding restaurant from the mySQL table.
* make sure the wp_insert_post also creates/works with a custom field
called RestaurantID containing the recid value of the restaurant from
the restaurants SQL table.
* and when doing so, also store the perma-link search engine friendly
URL of that restaurant that wp will give you into the mySQL
restaurant's table...  maybe in a field called wp_URL in the
restaurants table.
* and just keep these two entries in sync for the field info that matters.

It appears to me, with this approach, I end up being more efficient because...

for every restaurant I add into the system, I end up adding only 3
records - that is true no matter how many fields the restaurant table
would require.

1 rec for the wp_posts table
1 rec for the wp_post_meta table for the custom field restaurant id
1 rec for the restaurant table that has all the needed fields to
describe a restaurant.

this way, I can land on a wp_post containing the restaurant info and
the content section contains a custom php code that paints the
restaurant info tapping into the mySQL restaurant table. ( in fact
with such an implementation, that SQL database does not even have to
be mySQL database, could perfectly be a Microsoft SQL )

and when  I need to perform a search, I can provide a wordpress page
containing a form allowing the user to do a power search against the
restaurant table any which way I like and sort the results in the
recordset accordingly, and then display it...
and when I display the results, I use the permalinks stored so that
each link lands back on a wp_posts table.

this way I achieve, all the interactivity, search engine optimization
and the power WP is famous for, but tap into a custom app
implementation where it matters.

what do you say about this approach?

more efficient?
totally useless?
already exists?