I have a table Job and on the add page I would like the user to select with a look-up Job.productID from Products table, and job.color from Colors table. My problem is that Color depends on the color group that the product belongs to though I do not want to store the color group id in my Job table, nor force the user to select the color group because that should be determined when they select the product.
My tables are structured like:
Product.id, Product.name, Product.color_group
Color_goup.id, Color_group.name
Colors.id, Colors.name, Colors.color_groupID
So when someone selects the product the color group is determined but color group is not a field in my Job table.
Any help would be appreciated even if that is just helping me state my problem more clearly.