Column (database)

src: i.ytimg.com

In a relational database, a column is a set of data values of a particular simple type, one value for each row of the database. A column may contain text values, numbers, or even pointers to files in the operating system. Some relational database systems allow columns to contain more complex data types; whole documents, images or even video clips are examples. A column can also be called an attribute.

For example, a database that represents companies might have the following columns:

• ID (integer identifier, unique to each row)
• Name (text)
• Address line 1 (text)
• Address line 2 (text)
• City (integer identifier, drawn from a separate table of cities, from which any state or country information would be drawn)
• Postal code (text)
• Industry (integer identifier, drawn from a separate table of industries)
• etc.

Each row would provide a data value for each column and would then be understood as a single structured data value, like a company perhaps, as shown above in this particular example. More formally, each row can be interpreted as a relvar, composed of a set of tuples, with each tuple consisting of the two items: the name of the relevant column and the value this row provides for that column.

Examples of databases: PostgreSQL, MySQL, SQL Server, Access, Oracle, Sybase, DB2.

Coding involved: SQL [Structured Query Language]

See more at SQL.

Video Column (database)

Field

The word 'field' is normally used interchangeably with 'column'. However, database perfectionists tend to favor to use the word 'field' to signify a specific value or single item of a column. Therefore, a field is joint of a row and column.

Maps Column (database)

Row database vs column database

Relational databases mainly use row-based data storage, but column-based storage can be more useful for many business applications. For example, a column database has faster access to which columns can read throughout the range process of a query. Any of the columns are known to serve as an index. Alternatively, row-based applications process only one record at one time and normally need to access a complete record or two. Column databases have better compression as the data storage permits highly effective compression since the majority of the columns cover only a few distinct values compared to the number of rows. Furthermore, in a column store, data is already vertically divided. This vertical organization allows operations on different columns to be processed in parallel. If multiple items need to be searched or aggregated, each of these operations can be assigned to a different processor core. Overall, row-based databases in rows needs to check read though the obligation is to access data from a few columns. Therefore, requests on a large amount of data can take a lot of time, whereas in column database tables, this information is kept physically next to each other, knowingly increasing the speed of certain data queries.

src: i.ytimg.com

Advantages

The main benefit of keeping data in a column database is that some queries can come really quickly. For instance, if you want to know the average age of all users, you can easily jump to the area where the 'age' data is stored and read just the data needed instead of searching up the age for each record row by row. During querying, columnar storage avoids going over non-relevant data. Therefore, aggregation queries where one only needs to look up subsets of total data develop more quickly, compared to row-oriented databases.

Furthermore, as the data type of each column is alike, better compression occurs when running compression algorithms on each column, which will help queries churn results more quickly.

src: i.stack.imgur.com

Disadvantages

There are many situations where you multiple fields from each row will be desired. Column databases are usually not the best option for these types of queries. The more fields that need reading per record, the less benefits there are in storing data in a column-oriented fashion. If queries are looking for user-specific values only, row-oriented databases usually perform those queries faster. Secondly, writing new data could take more time in columnar storage. For instance, if you're inserting a new record into a row-oriented database, you can easily write that in one process. However, if you're inserting a new record to a column database, you need to write to each column one by one. This results as it will take longer time when loading new data or updating many values in a columnar database.

src: i.ytimg.com

See also

• Column-oriented DBMS, optimization for column-centric queries
• Column (data store), a similar object used in distributed data stores
• Row (database)

src: slideplayer.com

References

Source of article : Wikipedia