Have you encountered the anti-pattern 'Beautiful…

Yes, I have encountered this. "Beautiful columns" (Stripey Data, Rainbow Data) is an anti-pattern of data storage where different types of related data are spread across many columns in a single table, instead of being separated into related entities using normalization.

Example: a users table with columns like birth_date, address, phone, email, registration_date, last_login, status (e.g., active, blocked), user_type (admin, regular, guest), as well as columns for settings, preferences, related IDs, etc.

Problems I observed:

Query complexity: Retrieving specific information requires selecting from a wide table, often with NULL values for columns not applicable to the entity type. SELECT address, city FROM users WHERE status = 'active' AND user_type = 'regular'.
Redundancy: Fields relevant only for certain user types or statuses occupy space for all records.
Schema modification difficulty: Adding new data types or attributes becomes cumbersome, as it requires adding new columns to the entire table.
Performance: Wide tables can negatively impact query performance, especially UPDATE and INSERT, due to the need to process many columns.
Violation of relational database principles: Normalization is ignored, leading to anomalies (inserts, deletes, updates).
Schema comprehension difficulties: Developers find it hard to understand the purpose of each column and its relation to other data.

My experience involved refactoring such structures. We split the main table into several related tables:

users (main data: ID, name, registration date)
user_profiles (contact data: address, phone, email, linked by ID to users)
user_settings (interface settings, notifications, linked by ID to users)
user_types (type ID, name, description)

Foreign keys were used for relationships. This led to an increase in the number of tables but significantly simplified queries, increased flexibility, and improved schema understanding.

-- Before refactoring:
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    address TEXT,          -- Nullable
    phone TEXT,            -- Nullable
    registration_date TEXT,
    last_login TEXT,
    status TEXT,           -- 'active', 'blocked', 'pending'
    user_type TEXT,        -- 'admin', 'regular', 'guest'
    admin_approval_date TEXT -- Relevant only for admins? Nullable
);

-- After refactoring:
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    registration_date TEXT,
    last_login TEXT,
    user_status TEXT, -- Linked to status table if complex
    user_type_id INTEGER,
    FOREIGN KEY(user_type_id) REFERENCES user_types(id)
);

CREATE TABLE user_details (
    user_id INTEGER PRIMARY KEY,
    email TEXT,
    address TEXT,
    phone TEXT,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE user_types (
    id INTEGER PRIMARY KEY,
    type_name TEXT UNIQUE
);

In the end, although migrating data required significant effort, the new structure was more scalable, understandable, and maintainable.

Have you encountered the anti-pattern 'Beautiful Columns'? Can you share your experience working with it?