1

I'm developing an app that requires to save data that is not consistent across all rows. These database entries represent different kind of sport activities. Let's take an example: In football the final result is an important field. However, in cycling there's no such field. So it wouldn't be a good idea to create a table that has a score column, would it? This kind of data would be easy to store in a NoSQL database.

All these have some things in common, though: date, time, duration and location, at least.

The app also has functionality that would greatly benefit from typical relational database. Users table, messages etc.

How should I implement a database for such an application? All data is relational in a way that connects everything to a specific user. However, not all data can be organized in a traditional row/column based database.

Should I use MySQL, and for these activities have a data field that contains the relevant data in JSON? What are my options?

Also keep in mind that the database might be holding millions of entries.

closed as too broad by Robert Harvey, gnat, durron597, user40980, Kilian Foth Jun 10 '15 at 12:03

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Why use the same database object to track un-alike data? Clearly, each sport has its own statistics and scoring, you would need different ways of storing it. Do not create an inner-platform. – user22815 Jun 8 '15 at 18:56
  • 4
    @Snowman Creating hundreds of tables, one for each sport type wouldn't be a good idea. – MikkoP Jun 8 '15 at 18:57
  • 5
    Then I think you just answered your own question. – user22815 Jun 8 '15 at 18:58
  • 1
    To paraphrase Mr. Myagi, either MySQL do or NoSQL do... but sit in the middle of road by using both... squashed like grape. – GrandmasterB Jun 8 '15 at 19:54
  • 1
    @MiPnamic: Why should he think "document" when there is nothing described in the question which is remotely document-like? – JacquesB Jun 9 '15 at 11:18
4

It is no problem to have data "not consistent across all rows" in a relational database. This is precisely what relational databases are designed for! You create a table for the data which is common across all activities, and then you create separate tables for data specific for kind of activities. E.g. a football table with a score column, a cycling table with a distance column, both linked to the activities table with foreign keys.

So I'm skeptical that "not all data can be organized in a traditional row/column based database." You can always do that, unless you have very peculiar data. There are some cases where it might be inconvenient (eg. you have some complex application specific structured data which you never need to query or update - it might be easier to just serialize into a single field), but this depends on your particular use cases.

There is nothing in your question that indicates why a relational database wouldn't be fine.

  • I mostly agree with you. I often see people view rigid schema as something bad, while actually it can be very convenient tool. The only problem with this approach is when you need to change those sport-specific tables often (adding/removing new types of sort games). This is indeed the situation which might lead to invention of inner-platform Snowman's mentioned. I've seen one of those, it is definitely the case when JSON/XML documents (not specifically NoSQL) might be a good idea. – scriptin Jun 9 '15 at 10:48
3

Take a look at PostgreSQL. You can store JSON in it in a type-safe way. It supports indexing so you can combine relational data with JSON documents pretty well. It is also fast, there are plenty of benchmarks.

Also, if you never used NoSQL before, please read this: Why You Should Never Use MongoDB (don't mind the title, article is pretty constructive)

As for MySQL, I wouldn't suggest my worst enemy to use it. It has so much bugs it's not even funny. It pretty much doesn't work in a cluster (due to bugs some features become unusable and there are 1001 "workarounds" which make it unmaintainable), for one thing, so it's not a scalable solution.

So, choose between PostgreSQL and NoSQL at least. I definitely would recommend PostgreSQL.

EDIT:

I decided to mention some of MySQL bugs I happened to deal with to make it clear it is not just hate speech:

  • Incomplete XA support ("For XA START, the JOIN and RESUME clauses are not supported. For XA END the SUSPEND [FOR MIGRATE] clause is not supported.") + bug in official Java driver (this bug may be in MySQL itself, I'm not sure) causes (NUM_CORES*100)% CPU load on DB server, in which case you have to restart it.

    Workaround: merging all databases which participate in distributed transactions into one or not using distributed transactions. I participated in merging just 2 DBs, and that was brutal.

  • Deadlocks on concurrent inserts. Happens when you insert 4 values in 2 transactions, 2 each, in 2 gaps into some indexed field.

    Workaround: serialization of inserts. It means that practically you can only insert with one thread, hence no scalability for your application.

  • Triggers and replication don't go together. There are few bugs, like Bug #45677 related to AUTO_INCREMENT

    Workaround: not using triggers or not using replication. There are actually less brutal workarounds for each bug, but in the end of the day it's just what I said: choose either triggers or replication, not both.

Those are not even half of my list, but I want to emphasize that MySQL is bad when you need more than a single database with no replication. And it is especially important since scalability is one of the strongest arguments for using NoSQL solutions, and you mentioned you might have a lot of data.

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