Database - (rows or records, columns or fields)?

Question

In database terminology:

What is the difference between a row and a record?

Likewise, aren't columns and fields the same thing?

On the blog Joe Celko The SQL Apprentice , I noticed that the banner mentions that they are different things.

Solution 1

Row and record can arguably be considered as the same thing.

Fields and columns are different, a field is the intersection of a row and a column.
i.e. if your table has 10 rows and 10 columns, it has 100 fields.

When you create a table using DDL statements, you define columns (metadata). When you add rows using DML statements, you define rows and their fields.

Solution 2

In a broader sense, rows and columns refers to a matrix structure. When a database, not limited to a relational database, has a matrix structured data, it can be borrowed this terminology, but there might be a more specifical one.

In relational databases, for example, a table is always a matrix, so at each column in a table corresponds a field in a record and at each row corresponds a record: different concepts pointing to the same object.

A field can be present even in NoSQL databases, where often there's a free schema (no columns) and each row can have a different number of fields.

Similarly, a record can be a complex value in non-relational databases: it can contain fields with multiple distinct values (not 1NF). A row (a tuple in relational algebra) otherwise contains a single value for each field.

Solution 3

As stated in a previous answer to this question, row and record can arguably be used interchangeably.

Column and field can also arguably be used interchangeably. See the following article:Column (database) Here's a quote (as of this writing), from the article mentioned above, which makes that point:

"The term field is often used interchangeably with column, although many consider it more correct to use field (or field value) to refer specifically to the single item that exists at the intersection between one row and one column."

---

Here's some additional background info which may be helpful:

During my IT career as an analyst and programmer, I've typically used the terms field and record, not column and row, in both programming and relational database contexts. I think that comes from the instruction that I received during my university studies, and the fact that I learned the basic data hierarchy of bit, byte, field, record, file, before learning about relational databases. In researching this question, I found that it is common practice, and arguably correct, to use row and record interchangeably and to use column and field interchangeably. I was actually quite surprised, though, when my research indicated that row and column are preferred terms over record and field, in database terminology.

Solution 4

The terms Record and Field, predate relational databases, a time when computerized file systems ruled persistence storage, mainframes ruled the computing market and DBAs/Data Analysts were called DPs (Data Processing specialists).

A file with data organized in a 2-d matrix form, where a piece of information is called a field (column) and a collection of related fields a record (row). This data file is similar to a table (without standardized relationships governing the contents), therefore, the terms used during the file processing times were inherited. Technically, a row <> record and column <> field.

--For more information: Database Systems: Design, Implementation & Management - Coroner (Chapter 1, Section 5)

View more solutions

Comments

[Conrad Frix]

Really I thought it was tuples and attributes which made up relations which then made up databases :)

[TJ Thind]

Not wrong, but 'row' and 'column' are still commonly accepted terms in the context of relational databases.

[Clox]

Is the following information incorrect? "(Rows are sometimes referred to as records, and columns are sometimes referred to as fields.)" Or does he mean that when people do refer to them as that they are incorrect? Source: asp.net/web-pages/tutorials/introducing-aspnet-web-pages-2/…

[vc 74]

@Clox, I think he means that people sometimes use this terminology but he does not say whether it's right or wrong. I tend to use row/record equally but to me columns and fields are different.

[Clox]

Alright, thanks. I'll refer to "cells" as fields from now on then =p. The reason for ending up here was just that, I wondered what they're called cause I only knew fields/records from what he wrote.

[fool4jesus]

Row and column are common relational database terminology. As far as I know, records and fields come from pre-relational databases.

[a_horse_with_no_name]

Note that in Postgres and Oracle a single column can be a record.

[a_horse_with_no_name]

"row and record can arguably be used interchangeably" - depends on the database. In Postgres and Oracle a single column can be a record.