Column vs Field: have I been using these terms incorrectly?

Question

I feel kind of embarrassed here, I've always used the terms "column" and "field" completely interchangeably, which recently caused some confusion in a technical discussion.

I was told, though, that this wasn't correct, that it should be (translating each term into spreadsheet terminology, ignoring data types and all the other stuff that make databases useful):

• Database Column: like a spreadsheet column
• Database Record: like a spreadsheet row
• Database Field: like a spreadsheet "cell" (a specific column of a specific row)

Is this right? I could have sworn that column and field are used more interchangeably than that. I certainly have been.

So we don't add fields to a table, we add columns to a table, and fields are only relevant when talking about data within a record?

Other thoughts on column vs field?

Edit: to clarify, the current context is MS SQL Server. My background before SQL server was MS Access, which might influence my use of these terms.

Answer 1

Relational database theory does not include the use of the word Field. Dr. E.F. Codd, who wrote the series of papers that provide the theoretical basis for RDBMS's never used the term. You can read his seminal 1970 paper A Relational Model of Data for Large Shared Data Banks if you want to check.

Terms like Domain, Table, Attribute, Key and Tuple are used. One reason for this, is that his papers were largely concerned with relational algebra, and the way a particular implementation would define a table in a database wasn't considered by Codd to be important. Vendors would flesh that out later. People also have to understand that historically, RDBMS's evolved from existing hierarchical and network databases that predate them, AND the inner workings of an RDMBS still have to be concerned with data organization and storage.

In common use, and you can easily verify this by simply doing a bit of googling, Fields and columns are the same thing.

PC Databases like DBase, Access and Filemaker typically use "field" instead of "column". "Attribute" is another term that can be used interchangeably.

For example, here's a link to the MS Access manual on adding a "field" to a table. It's clear to see that in MS Access a "field" is equivalent to a "column".

The same holds for Dbase and Filemaker Pro.

Sometimes people will refer to a specific value in a specific row as being a "field" or more properly a "field value" but that does not make the use of "field" when referring to a column or column-equivalent-concept incorrect. This does tend to cause a level of confusion because people have used "field" to mean different things for many years. In relational theory -- a single atomic value is referred to as a "Datum".

If someone stated that a "field" is one value in a relational database and not the same as a column, that is their opinion, since "field" is not part of relational database vernacular. They are neither right nor wrong, however, throughout the database world, field is more often used to mean column.

With that said, projects and teams often have to work out an understanding of how they want to use particular terminology within the project to avoid confusion.

You aren't wrong, but you also might decide to simply go along with the convention being used, or avoid using the word field altogether in favor of "column". With relational databases, "Table" and "Column" are the building blocks that exist in DDL and it's best to just use those terms and avoid "field" which isn't used, nor clearly defined.

Answer 2

The older SQL:92 refers to fields as components of datetime items:

"Fields in datetime items", specifies the fields that can make up a date time value; a datetime value is made up of a subset of those fields

The fields here are year, month, and so on... and the term field doesn't seem to have any other meaning in the rest of the document.

The newer SQL:2003 standard has this:

Columns, fields, and attributes

The terms column, field, and attribute refer to structural components of tables, row types, and structured types, respectively, in analogous fashion. As the structure of a table consists of one or more columns, so does the structure of a row type consist of one or more fields and that of a structured type one or more attributes. Every structural element, whether a column, a field, or an attribute, is primarily a name paired with a declared type.

and later:

A field F is described by a field descriptor. A field descriptor includes:
— The name of the field.
— The data type descriptor of the declared type of F.
— The ordinal position of F within the row type that simply contains it.

This contrast with the column, which is defined as:

A column C is described by a column descriptor. A column descriptor includes:
— The name of the column.
— Whether the name of the column is an implementation-dependent name.
— If the column is based on a domain, then the name of that domain; otherwise, the data type descriptor of the declared type of C.
— The value of , if any, of C.
— The nullability characteristic of C.
— The ordinal position of C within the table that contains it.
... (and more)

Then later again, when introducing tables:

A table is a collection of rows having one or more columns. A row is a value of a row type. Every row of the same table has the same row type. The value of the i-th field of every row in a table is the value of the i-th column of that row in the table. The row is the smallest unit of data that can be inserted into a table and deleted from a table.

(emphasis mine). This seems to support what you wrote in the question: a specific column of a specific row.

Answer 3

And how many angels can dance around the head of a pin?

The person who corrected you could themselves be corrected.

• Table = Relation

• Row = Tuple

• Column = Attribute

• Domain = Data Type

See the Wikipedia entry on relational databases here.

I worked for an airline and the word "flight" could be used in three different ways depending on whether you were talking to pilots/flight-attendants, engineers or marketing.

• pilots/attendants: a "flight" was out and back from base (i.e. two take-offs and two landings),

• engineers: one take-off and one landing, could be test, repair, training (i.e. one airport back to the same airport) or a "leg", i.e. one airport to another - what "civilians" would normally call a flight, as in "I'm catching my flight home tomorrow"),

• marketing: a six month (typically on-season or off-season) series of "flights" from/to a given airport in the context of a contract.

The spreadsheet analogy is more than good enough for 99.99% of cases, even in reasonably technical speech (unless one is a professor of relational algebra). Does the person who corrected you use the word "whom" correctly? 99.99% of people don't and it really doesn't matter.

Answer 4

I generally use "field" and "column" interchangeably, more recently tending towards "column". I have not heard the term "field" alone to indicate "data" though. I've also not heard the term "attribute" to indicate "field" or "column". A Column/Field has attributes, accessible via the FieldInfo Class for instance.

I believe "column" is simply an evolution of the terminology. Desktop DBs (xBASE, MSAccess) generally use "field". M204 uses "field". This "field" terminology was carried into MSOffice xml and others. The docs for Oracle (it won't let me post any more links, sorry) and MSSQL use "field" and "column" interchangeably throughout. Sybase (now a SAP company) predominantly uses "column" but sometimes "field" in its documentation.

As long as your workgroup agrees on a term, it doesn't matter which one. It's a "rose by any other name" syndrome.

Answer 5

So I realize this is an old question but it is one I hear often. My take on it comes from the involvement our data team has with our development team. Developers definitely have fields within records that are shown on screens and those fields contain data that can frequently be mapped to columns with specific rows. However, in many cases the relational method used to access the data can alter what ends up on a particular screen in a particular field.

I record is a representation of the current value the data delivers. As time goes by those values may and probably will change so the record changes too. The data to support what it is now and what it was at a given time can easily be stored in a set of tables. The relationships between the data determine the meanings that make up the record at any given time.

The logic that derives the fields is something that can change over time. For example, an employee gets hired as Susan Jones and she was hired on 12/01/2010 as a sales clerk in store #101 who reports to Bill Anderson as store manager. This being a progressive company they also have a mentor assigned to each employee. Susan's mentor is Mary Phillips. Mary Phillips is a store manager but she is also a regional manager for the store Susan works in. On 11/10/2011 Susan was promoted to store manager. We do not know what happened to Bill but Susan now is the store manager.

We have a table of employees with name, number, hire date, position, and location.

We have a table of mentor with employee numbers for the mentors and the employee they are mentoring plus dates describing the beginning and end of the mentor relationship.

We have a table of regions with a name for the region and an assigned manager number.

We have another table of locations with address, description, region and manager number.

I screen that displays store information might have a field for store manager. The value for store manager is not a database field but it is a computable value that can change over time. Also a person's manager can change. The data that supports it is still stored in columns but the relationships between the columns has changed and when assembled for a specific purpose the becomes a field.