PHP/MySQL: Massive SQL query or several smaller queries?

Question

I have a database design here that looks this in simplified version:

Table building:

• id
• attribute1
• attribute2

Data in there is like:

• (1, 1, 1)
• (2, 1, 2)
• (3, 5, 4)

And the tables, attribute1_values and attribute2_values, structured as:

• id
• value

Which contains information like:

• (1, "Textual description of option 1")
• (2, "Textual description of option 2")
• ...
• (6, "Textual description of option 6")

I am unsure whether this is the best setup or not, but it is done as such per requirements of my project manager. It definitely has some truth in it as you can modify the text easily now without messing op the id's.

However now I have come to a page where I need to list the attributes, so how do I go about there? I see two major options:

1) Make one big query which gathers all values from building and at the same time picks the correct textual representation from the attribute{x}_values table.

2) Make a small query that gathers all values from the building table. Then after that get the textual representation of each attribute one at a time.

What is the best option to pick? Is option 1 even faster as option 2 at all? If so, is it worth the extra trouble concerning maintenance?

Answer 1

Another suggestion would be to create a view on the server with only the data you need and query from that. That would keep the work on the server end, and you can pull just what you need each time.