When to Choose SQL vs NoSQL: Real-World Scenarios and Schema Design

Review:

SQL databases:

Store related data in tables.

Require a defined schema before use.

Encourage standardization to reduce redundancy.

Support joins to retrieve related data in a single query.

Enforce data integrity rules.

Provide transactions so multiple changes succeed or fail as an atomic unit.

Can be scaled (with effort).

Use a strong declarative query language.

Offer extensive support, professional tools and expertise.

NoSQL databases:

Store related data in JSON‑like name‑value format.

Can hold schemaless data.

Typically denormalize, keeping all information for an entity in one document.

Usually do not require joins (when using normalized documents).

Allow any data to be stored at any time without validation.

Guarantee updates to a single document, not multiple documents.

Provide excellent performance and scalability.

Query using JSON data objects.

Represent a newer, exciting technology.

Simple guidance:

SQL is ideal when requirements are well‑defined and strong data integrity is critical (e.g., online stores, banking systems).

NoSQL fits projects with uncertain or rapidly changing data needs where speed and scalability matter more (e.g., social networks, CRM, web analytics).

Scenario 1 – Contact list (SQL design):

Initial contact table fields: id, title, firstname, lastname, gender, telephone, email, address1‑3, city, region, zipcode, country.

Problem 1: Multiple phone numbers → create a separate telephone table (contact_id, name, number).

Problem 2: Multiple email addresses → create an email table (contact_id, name, address).

Problem 3: Multiple addresses → create an address table (contact_id, name, address1‑3, city, region, zipcode, country).

Resulting simplified contact table contains only id, title, firstname, lastname, gender.

Issues with the relational approach:

Schema is fixed; adding fields like middle name, birthdate, company, etc., requires schema changes.

Data becomes fragmented; queries need many joins and can produce combinatorial result rows.

Full‑text search is cumbersome.

Choosing NoSQL for contacts:

Store each contact as a single document in a contacts collection, allowing arbitrary fields.

{ name: ["Billy","Bob","Jones"], company: "Fake Goods Corp", jobtitle: "Vice President of Data Management", telephone: { home: "0123456789", mobile: "9876543210", work: "2244668800" }, email: { personal: "[email protected]", work: "[email protected]" }, address: { home: { line1: "10 Non-Existent Street", city: "Nowhere", country: "Australia" } }, birthdate: ISODate("1980-01-01T00:00:00.000Z"), twitter: "@bobsfakeaccount", note: "Don't trust this guy", weight: "200lb", photo: "52e86ad749e0b817d25c8892.jpg" }

Full‑text indexing in MongoDB:

db.contact.createIndex({ "$**": "text" })

Search example:

db.contact.find({ $text: { $search: "something" } })

Scenario 2 – Social network (NoSQL):

Documents store user updates, each containing an array of status objects.

{ user_id: ObjectID("65f82bda42e7b8c76f5c1969"), update: [ { date: ISODate("2015-09-18T10:02:47.620Z"), text: "feeling more positive today" }, { date: ISODate("2015-09-17T13:14:20.789Z"), text: "spending far too much time here" }, { date: ISODate("2015-09-17T12:33:02.132Z"), text: "considering my life choices" } ] }

The document can grow long, but subsets (e.g., recent updates) can be retrieved, and full‑text search works across all status entries.

Adding emoji images to updates simply involves adding an image reference to a new record; no NULL placeholders are needed.

Scenario 3 – Warehouse management (SQL required):

Track items arriving at the warehouse, their relocation, and order fulfillment.

Require strong data integrity and transaction support, which only SQL databases currently provide.

Conclusion:

Each project is different; choose SQL when strict consistency and complex transactions are essential, and choose NoSQL when flexibility, speed, and scalability are paramount. Evaluate the trade‑offs before making a decision.