0

I have a business entity representing a weather forecast for a given location at a given time. There are different kinds of weather parameters that are forecasted for the given location and time, for example air temperature, air pressure, wind speed etc. Not all parameters will be guaranteed to be available, and I want to stay flexible to add new parameters.

If I want to design a table that fits these needs, I could use the following approach (one line per entity):

myforecasttable:
  - location_id PRIMARY
  - timestamp PRIMARY
  - airtemperature
  - airpressure
  - windspeed
  - etc.

Or I could use the following approach (multiple lines per entity):

myforecasttable:
  - location_id PRIMARY
  - timestamp PRIMARY
  - parameter_type PRIMARY
  - value

...where parameter_type will be an enum or string representing the concrete type of weather parameter (like airtemperature, air pressure etc.).

With the first approach, I will get NULL values in different columns, but it will be easier to query. With the second approach, it will be much easier to add new weather parameters, but the queries will probably be harder.

Are there any pros and cons with these designs? How can I choose which database design is the best for my use case?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
j0nes
  • 8,041
  • 3
  • 37
  • 40
  • 3
    [EAV](http://en.wikipedia.org/wiki/Entity–attribute–value_model) – Damien_The_Unbeliever Apr 22 '13 at 09:47
  • Thanks for pointing me in the right direction. For future reference, here is one quite similar question / answer: http://stackoverflow.com/questions/4011956/multiple-fixed-tables-vs-flexible-abstract-tables – j0nes Apr 22 '13 at 10:37

0 Answers0