PHP mySQL recommendations regarding search

Question

My question is: I have a mysql database that consists of something like a fact table (although not every field is a search) and many other tables. When I want to display data from this “fact table”, is there a need to run a query for each individual search, or is there a way to make a temporary table that has already performed a “search”?

Example: Table Structure -

• unique_id (auto increment int),
• model (int, search in table # 2),
• type (int, search from table No. 2 to table # 3)
• employee (int, search in table # 4)
• notes (text)
• cost (floating point)
• hours (floating point) -

So, for example, when I want to make a php page to enter this data, it seems a lot more "working" than it should be:

• unique_id (does not appear as a data entry field, automatically increases on submit)

• model (drop-down field. population requires a query to table # 2, where status = X)

• type (a read-only text field shows the type of model. A query on table # 3 is required based on a column of table 2)

• employee (drop-down field. population requires a query to table # 4, where employee_status = "Active")

• notes (text box, user inputs associated with view notes)

• cost (text box, user enters the costs associated with the presentation)

• hours (text box, user enters the hours associated with the view)

Just to get a simple form filled with valid data requires what seems to me like a lot of queries / searches.

Is this the best way? Is there a better way?

In addition: I have control over the data structure, so if the problem is related to the database design, these suggestions will also be useful.

Answer 1

Size tables usually do not change very often, at least with respect to the number of inserts in the fact table. Size tables are also individually much smaller than a fact table. This makes size tables good candidates for caching.

What some people do with a good effect is to render partial HTML output for the form, and all the data is populated like drop-down menus, radio buttons, etc. Then, save this partial HTML under the memcached key so that you don’t have to do any of the database queries or render HTML for most PHP queries — you simply extract the pre-filled HTML fragment from memcached and echo it verbatim. I think of it as Ikea database output.

Of course, if you ever make changes to the data in the dimension table, you will need to invalidate the cached HTML or even better generate it and save the new version of HTML in memcached.

Regarding the execution of all queries, I will indicate that there is no need to use pseudo-docs in the fact table. You can use natural values ​​and refer to the primary key of the dimension table, which can also be a natural key instead of a pseudocode. In some cases, this may take up a bit of space, but this excludes the search. Of course, it makes sense to continue to use pseudo-docs for sizes that are long varchars.

Answer 2

I’m not quite sure what you mean by "request for each individual search." Do you mean a way to save the whole table in your php script? Or do you mean a way to cache on a mysql server to eliminate process resources in a node database?

MySQL includes an integrated caching system that eliminates many server cycles for such requests. You can find more here-> MySQL Caching

Regarding the structure of your database, you will need to provide a little more detailed information about your schema (what your database should do) if you want to get some suggestions. It is difficult to understand which structure works and is effective, not knowing what it should do. (Are there several notes per employee, what are the costs? They are per employee, etc.)