Explain database design to me, and relational vs non relational designs

Question

I'm naturally a front-end guy but database design and back-end dev has piqued my interest the last few days and I'm lingering very confused. I'd like to fully grasp these concepts so my head doesn't hurt anymore.

I'm used to dealing with an ORM like active record. When I imagine my user objects through rails I picture an object of a person being related to a row in the people table. Ok basic.

So, I read that non-relational databases like mongodb aren't just cool because they're "fast with big data" but they're also cool because they apparently make developing more natural with an oop language (why?). Then I also read that most design patterns probably aren't truly relational. Ok, this is where I get lost.

1) What are some fundamental examples of relational design and non-relational design? 2) similar to above, what are examples of structured data vs unstructured (is this just rephrasing above?)

So, given those things I feel (in my immediate ignorance) that almost every type of project I've attempted to model against has been relational. But maybe I'm just using semantics over technicality. For example, posts and comments. Relational to each other. Add users in there. it seems most apps these days have data that is always useful to reach through other data/objects. That's relational isn't it?

How about something describing something less typical.

Let's say I was building a workout tracker app. I have users, exercises, workouts, routines and log_entries.

I create a routine to group workouts. A workout is a group of exercises. I record my reps and weight through log entries for my exercises. Is this relational data or non relational? Would mongo be great for modeling this or awful?

I hear things like statistics come into play. How does that affect the above example? What statistics are people usually speaking of?

Let's say I added tracking other things, like a users weight, height, body fat and so on. How does that affect things?

Thank you for taking the time to help me understand.

Edit: can anyone explain why it may be easier to develop for one over the other. Is using something like mongo more agile both because it "clicks" more once you "ge it" and also because you don't need to run migrations?

Another thing, when using an abstraction like an ORM - does it really matter? If so when? To me the initial value would be the ease of querying data and modeling my objects. Whatever lets me do that easier is what I'd be happy with. I truthfully do find myself scratching my head a lot when trying to model data.

Answer 1

OK, let me give it a stab...

(DISCLAIMER: I have very little practical experience with non-relational databases, so this will be a bit "relation-centric".)

Relational databases use "cookie-cutters" (tables) to produce large numbers of uniform "cookies" (rows in these tables). Non-relational databases give you greater latitude as to how to shape your "cookies", but you loose the power of set-based SQL. A little bit like static vs. dynamic typing.

1) What are some fundamental examples of relational design and non-relational design?

A tuple is an ordered list of attributes, a relation is a set of tuples of the same "shape", a table is a physical representation of a relation. If something fits this paradigm, it is "relational".

Financial transactions tend to be relational, while (say) text documents don't. There is a lot of gray area in between.

2) similar to above, what are examples of structured data vs unstructured (is this just rephrasing above?)

I don't know. How do you define "structured"?

it seems most apps these days have data that is always useful to reach through other data/objects. That's relational isn't it?

Sure. Relational databases don't have "pointers" per-se, but foreign keys fulfill essentially the same role. You can always JOIN the data the way you see fit, although JOINS are usually done over FKs.

Let's say I was building a workout tracker app. I have users, exercises, workouts, routines and log_entries.

It looks like this would fit nicely in the relational model.

Let's say I added tracking other things, like a users weight, height, body fat and so on. How does that affect things?

You'll probably need additional columns and/or tables. This still looks relational to me.

What statistics are people usually speaking of?

Perhaps they are talking about index statistics, that help the cost-based query optimizer pick a better execution plan?

why it may be easier to develop for one over the other

Right tool for the job, remember? If you know the structure of your data in advance and it fits the relational model, use a relational database.

Also, relational databases tend to be extraordinarily good at managing huge amounts of data, accessed concurrently by many users (though this might be true for some non-relational databases as well).

Another thing, when using an abstraction like an ORM - does it really matter?

ORMs tend to make easy things easier and hard things harder. Again, it's a matter of balance. For me, being able to write a finely-tuned SQL that extracts exactly the fields I need trumps the tedium of writing boiler-plate CRUD, so I'm not a very big fan of ORMs. Other people might disagree.

I truthfully do find myself scratching my head a lot when trying to model data.

Well, it is a big topic. If you are willing to put the time and effort in it, start with the ERwin Methods Guide, and also take a look at: Use The Index, Luke!