4
$\begingroup$

I'm currently facing a project that I could solve with a relational database in a relatively painful way. Having heard so much about NOSQL, I'm wondering if there is not a more appropriate way of tackling it:

Suppose we are tracking a group of animals in a forest (n ~ 500) and would like to keep a record of a set of observations (this is a fictional scenario).

We would like to store the following information in a database:

  • a unique identifier for each animal
  • a description of the animal with structured fields: Species, Genus, Family, ...
  • a free text field with additional information
  • each time-point at which it was detected close to a reference point
  • a picture of the animal
  • an indication whether two given animals are siblings

And:

  • there might be additional features appearing later as more data comes in

We would like to be able to execute the following types of queries:

  • return all the animals spotted between in a given time interval
  • return all the animals of a given Species or Family
  • perform a text search on the free text field

Which particular database system would you recommend ? Is there any tutorial / examples that I could use as a starting point ?

$\endgroup$
  • 1
    $\begingroup$ Just as a comment, it looks like you have a pretty strict schema for your data at this. Depending on how many new "columns" you expect to appear at a later time, SQL may actually the best solution for you. But again, I'm just speculating. $\endgroup$ – Phonon Jul 22 '14 at 22:38
  • 2
    $\begingroup$ As someone who is a huge fan of NOSQL, SQL is probably the right choice for this project. $\endgroup$ – Slater Victoroff Jul 23 '14 at 6:16
7
$\begingroup$

Three tables: animal, observation, and sibling. The observation has an animal_id column which links to the animal table, and the sibling table has animal_1_id and animal_2_id columns that indicates two animals are siblings for each row.

Even with 5000 animals and 100000 observations I don't think query time will be a problem for something like PostgreSQL for most reasonable queries (obviously you can construct unreasonable queries but you can do that in any system).

So I don't see how this is "relatively painful". Relative to what? The only complexity is the sibling table. In NOSQL you might store the full list of siblings in the record for each animal, but then when you add a sibling relationship you have to add it to both sibling's animal records. With the relational table approach I've outlined, it only exists once, but at the expense of having to test against both columns to find an animal's siblings.

I'd use PostgreSQL, and that gives you the option of using PostGIS if you have location data - this is a geospatial extension to PostgreSQL that lets you do spatial queries (point in polygon, points near a point etc) which might be something for you.

I really don't think the properties of NOSQL databases are a problem here for you - you aren't changing your schema every ten minutes, you probably do care that your database is ACID-compliant, and you don't need something web-scale.

http://www.mongodb-is-web-scale.com/ [warning: strong language]

$\endgroup$
  • $\begingroup$ PostgreSQL also has HSTORE columns and (recently) JSON which provide an outlet for schemaless data associated with rows which are otherwise typed. $\endgroup$ – Joe Germuska Mar 25 '15 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.