CODEWITHSUNDEEP
sqlmongodbschemaschema-design
jtmarmon
jtmarmon

Reputation: 6169

SQL embedded document equivalent

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.

Upvotes: 0

Views: 586

Answers (1)

langton
langton

Reputation: 126

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!

Upvotes: 1

Related Questions