I have an industry lookup table: ID, Name.I have other industry properties such as Industry sector, Industry service, Industry products, etc. These are all required properties for each industry so any industry being entered will have these data. These data are fixed list items like Industry sector = (Primary, Secondary, tertiary). On site, these values will be either auto-suggest or single select drop down list values. Also these will be used as search filters to further filter industries on site. And these will be used for reporting like -> Displaying count of companies belonging to primary sector industries only from people you are friends with.For schema I see two ways it can be designed:
1) Industry lookup table will have all these additional data as text
2) The additional data will be stored as IDs which FK reference to their lookup tables.
3) Open to other design ideas too. Issue with #1 is there will be no enforcement of data quality.
Issue with# 2 is there are many many fixed list items, so each having its own lookup table means there will be tons of lookup tables and FKs for the parent tables. I am not sure in the real world of large scale systems how this is done. Industry is only one entity; I have many entities and each has at least 40-50 fixed list items (columns), so which way is better? For more info, this is a user content website - professional networking website so performance is important.Suggestions?
1) Industry lookup table will have all these additional data as text
2) The additional data will be stored as IDs which FK reference to their lookup tables.
3) Open to other design ideas too. Issue with #1 is there will be no enforcement of data quality.
Issue with# 2 is there are many many fixed list items, so each having its own lookup table means there will be tons of lookup tables and FKs for the parent tables. I am not sure in the real world of large scale systems how this is done. Industry is only one entity; I have many entities and each has at least 40-50 fixed list items (columns), so which way is better? For more info, this is a user content website - professional networking website so performance is important.Suggestions?