Conditional value list based on grandparent

[ah]

I have an external database (MySQL) with the following tables:

tbl_Products

tbl_Product_attribute

tbl_Options_values

tbl_OptionsValues_to_Options

tbl_Options

The tbl_OptionsValues_to_Options is there because the solution may be shown in different languages.

When making a new product attribute record, I need to chose an option, and then a value for that option. The last field should be based on a conditional valuelist.

I am simply not able to make it work.

I have made the following relationships, where I have tried two different solutions:

either to make a link between tbl_Product_attribute and tbl_Options_values,

or between tbl_Product_attribute and tbl_OptionsValues_to_Options:

The value list works just fine when the product attribute is already assigned an option and an option value, but not when making a new attribute that has not yet been assigned an option value (images shown at the bottom of the post).

I am able to make a new product attribute from both the Products_attribute layout and from a portal on the Products-layout, and assign it an option, but I am not able to get any list of the value options.

Can anybody help me?

Should the relationships be:

attribute <> value_options <> value_options_to_options <> options

or

attribute <> value_options_to_options <> options AND value_options

Can i redo the value lists in any way?

Here is the value list for both options and option_values, plus a sketch of the portal:

Product attribute already assigned an option and a related option value:

Newly made product attribute, where only the option has been assigned:

[bcooney]

Like this?

Options.fp7.zip

[ah]

Like this?

Your solution is the one I would have made myself, with a general parent-child-relationship.

Here however, there is a complicating table making the link between options and options values, thus creating a grandparent-grandchild solution.

I need the relationship to go through tbl_optionValues_to_options, since this is a Zen-Cart database so that I cannot redesign the general solution. This table does have a function if accessing th database through the online admin panel: http://www.zen-cart.com/forum/showthread.php?t=132781

Tbl_options_values does NOT have an option_ID field. The link between tbl_options and tbl_options_values is tbl_optionsValues_to_options.

This might have something to do with languages (but they might have been combined to form a joined key), or it might be so that one should be able to reuse the same option_value for different options. I don't have the need to do this myself, but I don't dare to circumvent the table either, even though this particular setup has made a lot of hazzle for me when trying to make my FM-solution work.

As there are more people than me accessing the database through different means (admin panel, MagneticOne, my FM-solution), I need to work with the database the way it is.

An imagined example of some records, I am not sure if the solution is supposed to allow different options to one option_value, but at least it is a multi-language database:

tbl_Options:

options_ID = 4

language_ID = 1

Options_name = "Color"

options_ID = 4

language_ID = 2

Options_name = "Couleur"

options_ID = 5

language_ID = 1

Options_name = "Shirt color"

options_ID = 5

language_ID = 2

Options_name = "Couleur de chemise"

tbl_optionsValues_to_options:

optionsValues_to_options_ID = 267

options_values_ID = 53

options_ID = 4

optionsValues_to_options_ID = 290

options_values_ID = 53

options_ID = 5

tbl_optionsValues:

options_values_ID = 53

language_ID = 1

Options_value_name = "Red"

options_values_ID = 53

language_ID = 1

Options_value_name = "Rouge"

tbl_languages:

language_ID = 1

language_name = English

language_ID = 2

language_name = French

[bcooney]

Maybe this?

Options2.fp7.zip

[ah]

Maybe this?

It still doesn't work for my real database, when I tried to do different changes to make it closer to your solution

I have now redone some of the tables in the database to a local database, and imported the records for each table.

I relookuped all the tables in the relatonship graph as well, but apparently something is not working with these relationships (unfortunately).

But at least, this database might better show the structure of the solution.

I am uncertain about how the relationship between attributes, options, option_values and option_values_to_options should work, and how the value list should be set up.

I hope someone is able to help, this is my main obstacle, and I would very much like to know for the future as well how more complex relationships like this may be solved.

local_FM.zip

[bcooney]

I can't unravel the relationship graph in your file. I use a convention called Anchor-Buoy and without that approach I find the RG almost impossible to decipher. Also, I prefix all keys. Primary keys are "__kP" and foreign keys are "_kF", this sorts them to the top. The RG is not an ERD, as you'll see a table can be represented more than once. Because of that, you very much need to know "where" you are on the RG, that is the concept of "context." Each layout is based on a table occurrence, which provides your context. It is important when it comes to conditional value lists to be in the correct context. Basically, the layout must be on the parent table occurrence, and the field to which you assign the conditional value list must be related to that table occurrence and use the same relationship as the value list.

You haven't explicitly said that the example I provided successfully solved your requirement. Does it?

Also, it would be helpful if you had named your tables and the keys the same as your ERD above.

[ah]

I have now done some minor changes to Options2.fp7:

• 
  
  
• added some name fields to the tables Options OptionsValues and Products
  
• made a relationship between the tables products and productAttributes
  
• added some records in the above mentioned tables
  
• added some fields/portals to the layouts
  

My attempts to make relationships between the tables just made a mess, so I have not made any other changes to the relationship than the link between products and productAttributes.

How should the relationship be, so that for instance the layout Products shows a portal with all the related attributes and their option / optionValue names?

Options2_added_fields.zip

I have now read a bit about the AB method, and it does seem to have its advantages, but I am a bit uncertain about how I would make my relations with this approach (especially the real database, with 40+ tables). Seems interesting though, I will look more into the concept.

I didn't rename the fields simply because of time constraints, as I spent some hours figuring out how to copy the tables, redefine the tables in the relationship graph and import data to each of the tables.

(I didn't make the original database, so i just tried to follow conventional DB-standards when making my first draft of a simple ERD)

[ah]

You haven't explicitly said that the example I provided successfully solved your requirement. Does it?

It dsolved the value list problem, but the relationships were not right for the rest of the database.

It might be that it just needs some more buoy/anchor-relationships, but I really have no clue how these should be made.

I need to get the options and values right with regards to the attributes and products

[ah]

I am still not able to make neither the otions2.fp7-database work as it should, nor my original database

Any help would be deeply appreciated