Widetable

Widetable, commonly referred to as a wide table in database design discussions, denotes a table that contains a large number of columns intended to capture many attributes of a single entity in one row. This approach contrasts with highly normalized schemas that distribute attributes across multiple related tables. A widetable often includes many optional or sparse attributes that are NULL for most rows, and it can grow to contain dozens or even hundreds of columns.

Characteristics and use cases include aggregation of diverse fields for convenient access, reduced join requirements for queries that fetch many attributes, and simplicity when the attributes are relatively stable and tightly scoped to one entity. Common examples include user or customer profiles with numerous optional preferences, product catalogs with a wide set of attributes, or single-row representations of complex entities where related data would otherwise require several tables.

Benefits of a widetable can include simpler read queries for attribute-rich requests and potentially faster performance when the access pattern consistently targets many columns at once. However, there are notable drawbacks: wide rows increase storage overhead due to NULLs, complicate schema evolution, hinder index effectiveness, and can degrade performance for insert, update, and maintenance workloads. Some database systems impose row-length limits that constrain how wide a table can become.

Design guidance suggests using a widetable only when most queries access many attributes together and the attribute set is relatively stable. Otherwise, consider vertical partitioning (splitting attributes into narrower tables), an entity-attribute-value model for sparse data, or storing dynamic attributes in JSON or a document store. Columnar or analytics-focused databases may also handle wide schemas more efficiently for read-heavy workloads.