Relational Model Versus Document Model

Relational Model Versus Document Model

How can a résumé be represented in a relational schema? The profile was a whole can be identified by a unique identifier, user_id. Fields like first_name and last_name appear exactly once per user, so they can be modeled as columns on the users table. However, most people have had more than one job in their career, varying numbers of periods of education, and any number of pieces of contact information. The one-to-many relationship between a user and these items can be represented in various ways:

• Put positions, education, and contact_info in separate tables, with a foreign key reference to the users table.
• In later versions of SQL, multi-valued data can be stored within a single row, with support for querying and indexing, e.g., as XML or JSON data-types.
• Encode positions, education and contact_info as JSON or XML, and store it on a TEXT column in the database. However, one can’t query the DB for values inside the encoded column.

CREATE TABLE users (user_id integer PRIMARY KEY, first_name text, last_name text, summary text);
CREATE TABLE positions (position_id integer PRIMARY KEY, title text, company text);
CREATE TABLE user_positions (user_id integer REFERENCES users, position_id integer REFERENCES positions, start_date date, end date);
CREATE TABLE institutions (institution_id integer PRIMARY KEY, instution_name text, degree text);
CREATE TABLE user_institution (user_id integer REFERENCES users, institution_id REFERENCES institutions, start date, end date);
CREATE TABLE contact_info (user_id integer REFERENCES users, info text);

For a data structure like a résumé, which is mostly a self-contained document, a JSON representation can be appropriate, e.g.,

{
  "user_id": 251,
  "first_name": "Bill",
  "last_name": "Gates",
  "summary": "Co-chair of the Bill & Melinda Gates... Active blogger.",
  "region_id": "us:91",
  "industry_id": 131,
  "photo_url": "/p/7/000/253/05b/308dd6e.jpg",
  "positions": [
    {"job_title": "Co-chair", "organization": "Bill & Melinda Gates Foundation"},
    {"job_title": "Co-founder, Chairman", "organization": "Microsoft"}
  ],
  "education": [
    {"school_name": "Harvard University", "start": 1973, "end": 1975},
    {"school_name": "Lakeside School, Seattle", "start": null, "end": null}
  ],
  "contact_info": {
    "blog": "http://thegatesnotes.com",
    "twitter": "http://twitter.com/BillGates"
  }
}

… with properties such as:

• Lower impedance mismatch between the application code and the storage layer.
• Locality: all the info in one place, and one query is sufficient.
• The one-to-many relationships' tree structure is explicit.

The use of IDs such as us:91 and 131 over free text such as Greater Seattle Area and Philanthropy enables:

• Consistent style and spelling across profiles.
• Avoids ambiguity, e.g., several cities with the same name.
• Ease of updating, e.g., city changes its name.
• Standardized list can be localized into user’s language.
• Better search, e.g., encoding that Geater Seattle Area is in Washington.

Anything that is meaningful to humans may need to change sometime in the future, but the ID, which has no meaning to humans, can stay the same. If information that is meaningful to humans changes, and that information is duplicated, all redundant copies need to updated (write overheads) lest the data becomes inconsistent. Removing such duplication is the key idea behind normalization in databases.

Are Document Databases Repeating History?

The network (CODASYL) model of the 1970s had it such that a record could have multiple parents. To access a record, one would need to follow an access path from the root record along chains of links (and keep track of the traversal because different paths can lead to the same record). The code for querying and updating the database was complicated and inflexible.

#databases

#innovation

Relational Versus Document Databases Today

If the data has a document-like structure, splitting it into multiple tables can lead to cumbersome schemas and unnecessarily complicated application code. That said, data has a tendency of becoming more interconnected as features are added to applications.

Some references cannot be made directly in a document model, e.g., “the second item in the list of positions for user 251,” but as long as the documents are not too deeply nested, this is not usually a problem.

if (user && user.name && !user.first_name) {
  // Documents written before Dec 8, 2013 don't have `first_name`.
  user.first_name = user.name.split(" ")[0];
}

-- Perform a migration that adds first_name
ALTER TABLE users ADD COLUMN first_name text;
UPDATE users SET first_name = split_part(name, ' ', 1);

… and some tradeoffs may arise. For example, MySQL’s ALTER TABLE copies the entire table, which may mean hours of downtime for very large tables. UPDATE on a large table is slow on any database, but can be mitigated by setting a default of NULL and filling it at read time, like it would in a document database. Schema-on-read is also advantageous when there are many different types of objects, or the structure of data is determined by external systems that you don’t control.

References

1. Designing Data-Intensive Applications: The big ideas behind reliable, scalable, and maintainable systems. Chapter 2: Data Models and Query Languages. Kleppmann, Martin. Mar 16, 2017.
2. The Law of Leaky Abstraction. Joel Spolsky. www.joelonsoftware.com . en.wikipedia.org . Accessed May 28, 2023.
3. sql - MySQL very slow for alter table query. stackoverflow.com . Accessed May 28, 2023.