Need help with creating a "look-up" table in MySQL
Posted by: Chris Bassett
Date: September 05, 2013 01:26PM

I need some advice on how to model the following scenario:

I have a database that contains a list of custom fields that applies to certain products. Not all custom fields will apply to all products.

Example: Product 1 applies to custom fields 1 & 2; Product 2 applies to custom field 1 only, product 3 applies to custom field 2 only.

The issue is I need to create a lookup table to make device models to products (in this case, it is ag planter models to our products).
The thing is that each device model can have a varying number of custom fields associated with it. Some may have none, some may
have one or two, and others may have 20+ questions associated with it.

How can I have a "jagged" array or table design? (I know this is bad practice, but if anyone has any better ideas, please let me know.\

Below is an example of who I want the logic in the lookup table to work:
If device model = A, and custom_field1 = "LL" then product_id = 1;
If device model = A, and custom_field1 = "LL" and custom_field2 = "NO" then product_id = 3;
if device model = B, and custom_field1 = "STD" and custom_field2 = "YES" and custom_field3 = "24" then product_id = 100;

(What I am illustrating is that there can be varying number of custom fields, ranging from none to many).

Here is a graphical example of my idea and what I'm trying to do:

mysql> select * from lookup;
| pair_id | device_id | custom_field_conditions | product_id |
| 2 | A | TILLAGE=NONE | 1003 |
3 rows in set (0.00 sec)

My first idea was to simply create a large text field
and store the custom_field ID and answer (several of them) within the field and then tokenize the string and parse the entire
text block. I was also thinking that I would have to use the CONTAINS clause in the SQL statements when querying and looking at the custom_field_conditions column rather than tokenizing each string and then comparing.

Any ideas on how to model this so it is efficient and easily maintanable?

Edited 1 time(s). Last edit at 09/05/2013 01:36PM by Chris Bassett.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.