How would you solve this? Some data in csv, some in mysql

[eluser]deco10[/eluser]
So I have a bunch of data in a csv file. This data will change... never.
This data includes a list of "names" and then pdf files that correspond to them. I don't want to rename the PDFs although that would be one way to solve them.

The table I'm using has 50k entries and may grow some but not significantly. Each line has some prices, description, manufacturer name (there are about 30 manufacturers across the 50k records).

When I print the query results, I print the the manufacture name as a link to the PDF.

I just realized it would be practical to load the csv data into an assoc array and just use the manufacturer name as the key. This way I would only have to read the csv once. But even with this improvement, I wonder if there is a more efficient way, as a user may perform hundreds of searches a day. (its an intranet site for a very busy sales dept).

Suggestions?

[eluser]kgill[/eluser]
The most efficient solution would be to load the CSV data into its own table in the database, then when you query the table with the prices, you can join the two tables and get the PDF name as part of your query instead of messing with reading a file, parsing each line and building an array. It's data and you want to query it, why would you keep it outside of the database if you're already using the DB in the app?

While you don't have to do it, ideally you'd want to use an ID number instead of the manufacturer's name as the primary key, auto numbering the new table will give you that much, the only issue is getting those numbers into the other table and that's easily taken care of with a new column and an update statement.

Code:

update pt set pt.manufacturer_id = mf.ID
FROM price_table as pt
LEFT JOIN manufacturers as mf on mf.name = pt.manufacturer_name

After that drop the manufacturers name column from the price table as that data is sitting in your other table.

[eluser]deco10[/eluser]
I can do that, easy enough. The thing is, I allow for up to 300 results and it seems wasteful to query the database for every result when they might all be the same manufacturer and therefore have the same link attached to them.

I could test the performance I suppose..