How to model data with unknown attributes?

Question

What are good ways to model data that will need to be queried but where it's impossible to fully define up front?

For instance... say I want to model information about the countries of the world. Each country has a population, a flag and a list of languages, that's easy enough. But say we also want to model the win/loss record of their national baseball team and not all countries have one, of course. Or, we want to track the lineage of their kings & queens (again, obviously not applicable to most countries). Or, we decide we want to model the number of yurts the average clan member will erect in a lifetime.

Anyway, point is, we don't (and won't ever) know what's coming until it hits us. What approaches are there that are both scalable and query-able?

Is this, perhaps, a good use for a Document-centric database (MongoDB?) or perhaps some design pattern could be applied to the classic Relational database?

Answer 1

You can apply the Entity Atribute Value Model but it is a PITA in rails; I have used MongoDB and it is great for what you need.

Answer 2

If you're using Rails, you can use serialize :column_name in your model and it'll persist most objects successfully for you without any additional work. If you don't think you'll have a need for a schema-less NoSQL database, this is probably about the easiest thing you can do to get this functionality.

class Country << ActiveRecord::Base
  serialize :data

  def add_statistic(name, value)
    data[name] = value
  end

  def get_statistic(name)
    data[name]
  end
end

Those methods are somewhat superfluous; they're there just to show give you an example.

The biggest downside to this type of system is if you have a need to search or query based on these things. Rails won't handle Country.find_by_win_loss_record_of_national_basketball_team for you, after all.

Answer 3

All databases ought to be capable of evolving over time. If you have the right people and organisation in place then you should have no problem adding new attributes to the model as they arise.

Answer 4

You can do that in a pure Relational Database, and enjoy the speed and power of Relational databases.

You need to use Sixth Normal Form, the proper method with full integrityand control.

EAV is a subset of 6NF without the Integrity or control, and usualy very badly implemented.

My answers to these questions provide a full treatment of the subject. The last one is particularly long due the the context and arguments raised.

EAV-6NF Answer One
EAV-6NF Answer Two
EAV-6NF Answer Three