In the context of databases, the terms attribute, field, and column are often used interchangeably, especially in relational databases. However, there are slight nuances in the way they’re used within different contexts. Here, I create a blog post to quickly define the differences and nuances of these terms. If you are unfamiliar with ERD, Conceptual, Logical, and Physical database models, you may want to take some time and review these concepts.
First, let’s start with the definitions of Attribute, Field, and Column.
- Attribute: This term is commonly used in the context of an entity-relationship diagram (ERD) during the database design process. An attribute represents a characteristic or property of an entity. For example, in an entity
Students,StudentName, Student ID,Coursecould be considered as attributes. When an ER model is implemented in a relational database, these attributes (often) become the table’s columns. - Field: The terms field and column are often used interchangeably, but to purists, there is a distinction between field and column. The term “field” is frequently used in the context of a single record; it refers to a single piece of data or information stored for a particular attribute in a record. For instance, in a
Studentrecord, theStudent Namefield could contain the value “John Doe”. One effective approach to grasp this concept is by envisioning how you would naturally explain this value to a coworker. In that scenario, you would likely refer to it as a field rather than a column. For instance, you might say, “In the Student Name field, the value for Student ID 102 is John Doe.” - Column: This term is predominantly used in the context of a table in a relational database. A column in a table represents a set of data values of a particular type, one for each row of the table. For instance, the
Student Namecolumn would contain the names of all students in theStudentstable. When discussing a column, it is common to describe it in the following manner: “The status column can only contain the values of Approved, Denied, or NULL.”
Due to the interchangeable use of the terms “field” and “column,” it is not uncommon to come across varying terminologies in different documentation.
- In Microsoft’s documentation for creating tables in Microsoft Access, the term “field” is utilized rather than “column.”
- On the other hand, in Microsoft’s documentation for creating tables in SQL Server, the term “column” is employed.
The term “attributes” can have several meanings in the context of a database.
In an Entity-Relationship Diagram (ERD), an attribute is a characteristic or property that defines an entity in the model. When an ERD is translated into a physical database model, entities become tables, and attributes become columns.
Within the context of a database, the term attribute can mean any property that a database object (table, column, schema, trigger, stored procedure, view, etc.) can possess. However, it is most often used to discuss the properties of a table’s column.
Let’s explore these properties or attributes of a table’s column.
- Name: Every column must have a unique name within its table.
- Data Type: This defines the kind of data the column can store. It can be integers, floating-point numbers, strings, dates, Boolean values, or other types of data depending on the database system.
- Length or Size: For some data types, such as strings (VARCHAR, CHAR), you can specify the maximum length.
- Default Value: You can specify a default value the database system uses if no value is explicitly provided when a row is inserted.
- Constraints: Constraints are rules that the database system enforces. They include:
- NOT NULL: This means the column must always have a value (it can’t be NULL).
- UNIQUE: This means all values in the column must be unique.
- PRIMARY KEY: This means the column is the primary key for the table. Its values uniquely identify each row in the table.
- FOREIGN KEY: This indicates that the column is linked to the primary key of another table.
- CHECK: This allows you to specify a condition that the values in the column must meet.
- Collation: This is a set of rules determining how string data is sorted and compared. This is relevant for columns that store string data.
- Auto-Increment: This attribute is available in some database systems. It automatically generates a unique number for each row, typically used with a primary key column.
I hope this clarifies some of the terminology you’ll frequently encounter in your database-related tasks. It’s common for developers to interchange certain database terms, often without adhering strictly to their official definitions (for instance, referring to a table’s column as a field). Understanding these terms, both in their proper context and how they’re casually used, can greatly enhance your proficiency with SQL and overall database management.