Best Practice Question

[Saragon]

I'm building a mySQL database for a property manager's website -- a much larger database than my previous projects. (Not Oracle-sized by any means, but still new to me.) I never played around with VIEWs much before now, but I'm wondering whether or not they might speed things up.

To browse through the properties, I'm going to use the standard ten-at-a-time list, with certain pieces of information (rent, street, thumbnail image, etc.) displayed for each one. There's also a large table for amenities that are mostly boolean values (fireplace?, pets allowed?, cable?, and so forth). Each of these pages will see a lot of use.

Which is the better practice: To pull the data directly from the base tables via PHP and have the appropriate function control what's shown? Or have a SQL VIEW already in place that contains the data, and have my code put the entire row into an array or something similar?

[obsidian]

[!--quoteo(post=389234:date=Jun 29 2006, 08:49 AM:name=Saragon)--][div class=\'quotetop\']QUOTE(Saragon @ Jun 29 2006, 08:49 AM) [snapback]389234[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I'm building a mySQL database for a property manager's website -- a much larger database than my previous projects. (Not Oracle-sized by any means, but still new to me.) I never played around with VIEWs much before now, but I'm wondering whether or not they might speed things up.

To browse through the properties, I'm going to use the standard ten-at-a-time list, with certain pieces of information (rent, street, thumbnail image, etc.) displayed for each one. There's also a large table for amenities that are mostly boolean values (fireplace?, pets allowed?, cable?, and so forth). Each of these pages will see a lot of use.

Which is the better practice: To pull the data directly from the base tables via PHP and have the appropriate function control what's shown? Or have a SQL VIEW already in place that contains the data, and have my code put the entire row into an array or something similar?
[/quote]

for something like this, can you just use a SQL join to pull them all into one query anyway? when it's only joining 2-3 tables, you may be just a well off to write a more complex query that will simply return all the values in one row that you can then assign into an array as you would be to create a view. now, if you're having to join 6-7 tables with some very intricate relations, i'd definitely recommend creating a view, but i think you're borderline with your situation.

[fenway]

Agreed... VIEWs, IMHO, are only good for very complicated & resource-intensive queries you don't want screwed up, or for "faking" row-level permissions.