Hello everyone!
I'm transitioning a DB from Filemaker to MySQL, and one thing that has come up for which I'm not finding a directly on-point answer, is this:
In Filemaker, it is possible, and actually advisable in many cases, to have your Value List values (the FMP equiv. of an enum or set list) stored in their own table. Such a Value List therefore contains whatever values are in that table, and is never "hard coded" as they are in all enum lists I've seen so far. In the master table, fields can then use that Value List, ie, that second table data, as their valid enum set. This is quite handy for, say, an invoices table, containing line items, and when adding line items to the invoice the available choices of what to add are whatever products are available in the products table. Obviously a product-dependent enum field in the line items table would need very frequent updating of its enum values were it not for a dynamic Value List. The alternative, using a non-enum value field (like an INT productID field, for example) would certainly work and I know how to work with that, but you lose the field validation inherent in the enum type, which I'd like to keep if possible.
So, the question becomes - is it possible to have an enum field with a dynamic enum value set, following the Value List model above, or must enum fields always use a "hard coded" value set?
for example, is there any way to implement something that would have the effect of:
(in table sometable)
ALTER TABLE sometable ADD somefield ENUM(SELECT someotherfield FROM someothertable WHERE condition=true) DEFAULT "0"
I'm sure I could figure a way to have PHP contantly reupdating the enum set of the field, but if there's a way to do this dierctly in mysql...
Thanks for any help you've got!
JK
