0

I'm starting to use SQL after only ever using mongodb. As such, I'm having trouble mapping some practices over to the SQL world. Primarily, I'm not sure how embedded documents in mongodb map to SQL.

For example, in mongodb, if I wanted to model a person who has a car, I would have a collection of documents that look like

{
    firstName: 'John',
    lastName: 'Smith',
    car: {
        color: 'Red',
        year: 2001,
        ...
    }
}

How would I create a similar relationship in a SQL database? I realize I could namespace things like car_color and car_year, but this seems conceptually ineffective when compared to showing this relationship in your document structure.

jtmarmon
  • 5,727
  • 7
  • 28
  • 45
  • You'd just use two tables. A Person table with a CarIndex field that relates the the unique ID of a car from the second, Car table. – Mr. Mascaro Feb 04 '15 at 21:11

1 Answers1

1

Following the example above, you would separate these into two tables (relations), say Person and Car with the fields as noted. Each table would also have a primary key (which may be natural, e.g. the car registration number, or artificial like an auto-increment integer).

To relate these two entities, you could either have (1) a foreign key field in Person with the value of the primary key in Car, or (2) a foreign key field in the Car table with the value of the primary key value in Person. I would prefer (2) since it allows multiple cars to be owned by 1 person.

Of course, this is a simplification - People may own multiple cars and one car may be owned by multiple people. This would usually be modeled by keeping the foreign keys out of the car and people tables and creating a 3rd table which simply stored a pair of foreign keys relating Cars with People.

Footnote: most people are going from SQL to MongoDB, not the other way around! Good luck!

langton
  • 126
  • 1
  • 3
  • thank you :) and yes, a lot of people are going the opposite direction; however, our primary query is turning out to be very relational, and we want to be able to query across 3 or 4 collections at once - mongodb just wont do that for us – jtmarmon Feb 04 '15 at 21:23
  • "*most people are going from SQL to MongoDB*" - I think that hype is actually cooling off again. I actually see more and more people mentioning that a NoSQL doesn't really cut it for them and e.g. Postgres as a good combination of both a relational and "NoSQL" store (json, key/value) gets a lot of attention recently. –  Feb 04 '15 at 21:53
  • I'm inclined to agree @a_horse_with_no_name. PostgreSQL + native XML/JSON columns types with some useful functions is a pretty good combination. – langton Feb 04 '15 at 22:01