Considering that I have the following 3 attributes:
WeatherCondition
- rainy
- sunny
- cloudy
Daytime
- day
- night
RoadType
- city
- highway
- underconstruction
And I want to map this values with indexes (day - 1, night - 2, etc..)
My question is, what way should I do this considering that anytime I would want to add 2-3 choices more to an attribute, or even new attributes?
Solution1:
AttributessTable:
ID AttributeType AttributeValue
AT1 WeatherCondition rainy
AT2 WeatherCondition sunny
AT3 Daytime day
AT4 Daytime night
AT5 WeatherCondition cloudy
Solution2: Separate tables for each attribute with only 2 columns (ID and value). WeatherCondition table with values (1,rainy; 2,sunny; 3,cloudy) Daytime table with values (1,day; 2,night)
I'm somehow reluctant on the second solution thinking that I may have to create 30 tables.
The final result, is that I want to have a "lookup" or "bridge" table with the ID FK from another table like this:
FinalConditions
ID Attribute
1 AT1
1 AT3
1 AT5
2 AT2
2 AT5
Also, it's important to me to create reports by joining all this data altogether, I'm thinking that with Solution 2 it will be harder to join altogether 30 tables.
I think it all comes down to scalability - how many rows are the tables expected to hold (all of them, summed up). If they're never going higher than say 10k, you shouldn't worry - Solution 1 will do.
However, if you expect the eventual number to be, say, in the millions of rows, Solution 2 is definitely the way to go - it'll lead to a lot less locks and it will probably be a lot easier to maintain (albeit harder to implement - you might have to "create 30 tables").
Hope this helps.