Strategy to map multiple lookup tables with system tables?
I have a bunch of lookup tables:
Religion
Country
City
nationality
开发者_开发问答Currency
etc... approx 80-100 lookup tables.
Now I have system tables where i am centralizing all the fields and values so i can ID all fields, centralize them for reporting and add multi-language to my lookups. So these are tables like:
Form (all forms on system)
Field (all fields on system)
Value (all values for all fields on system)
Form_Field_value (mapping them together)
Translation (maps value and field to multi language)
but the question is how to get all the 80-100 tables data into these Field / Value tables? So it will be like this:
Field table:
id 1 Natioality
id 2 Country
1d 3 city
...
Value table
1d 1 american
id 2 chinese
id 3 rusian
...
field_value
id 1, field_id1, value_id1
id 2, field_id1, value_id2
id 3, field_id1, value_id3
...
Ofcourse i can manually do it but then it defeats the purpose of having those lookup tables. So ideal is to keep these tables in sync. next question is which tables to use for user forms? The lookup tables or the form_fields
table?
union select
http://dev.mysql.com/doc/refman/5.0/en/union.html
精彩评论