Re: Need help with creating a "look-up" table in MySQL

[Chris Bassett]

Having several rows for a particular product or device is fine. That's not so much a problem.

The problem that I still see with the layout proposed is what if I need to have 2 custom fields to determine a product. The proposed layout (from my interpretation) would assume 1 device + 1 custom field = 1 product.

What if I need multiple custom fields to determine a single product?
This query would work fine if it was just one custom field:

SELECT * FROM LookupTable WHERE device_id = 300 AND custom_field_id = 1 AND custom_field_value = 'STD';

But in many cases, more than one custom field would need to be checked in a single query.