0

I have a table with 49 columns, which is already normalized. It seems kind of excessive, but since every field is part of a form and the main entity is the form there are no relations I can refactor to delegate columns to another table, so hence my question "Are the any patterns that target the reduction of the number of columns in a table?"

I've tried to do something like a Question - Answer schema, but since the Answer value can be of many different Data Types (Enums, Strings, Numbers), there is no generic way to parse everything.

UPDATE:

I hadn't considered but the data will be eventually serialized on JSON to be served from a WEBApi so having a lot of nested fields could mean trouble.

  • 2
    I don't find myself saying this too often, but this may be a good candidate for a NoSQL approach if you're averse to having that many columns. Depending on your RDBMS (MSSQL or Postgres) you could even store some of it as JSON within your columns directly. That being said, tables far far wider exist out there so unless there are performance issues related to this, what is the real issue? – LowlyDBA Mar 6 '17 at 22:35
  • 2
    Are all columns always populated? Or are many of them often blank? – blobbles Mar 7 '17 at 1:04
  • @blobbles most of them are – J. Pichardo Mar 7 '17 at 2:12
1

Does looking at the structure from a functional point of view help? I.e., can you redesign the table to correspond to the way the application will use it? A sparsely populated table with a bunch of initially null columns that are populated later on can cause performance issues over time. Isolating these columns into their own table might make sense.

How is the table used once populated? Decreasing the row length (the sum of bytes all column values use) reduces the time it takes to perform a full table scan. If the table is to be scanned, then you will want to isolate the columns that are not needed.

If either case is helpful, the table containing the undesirable columns will become a child of the other by referencing the other table's primary key and adding a unique constraint on the foreign key to ensure it remains a one to zero, one relationship.

1

Have you considered a table for the questions, and a table for the answers?

I'm using SQL Server; you haven't specified what DBMS you're using.

USE tempdb;
CREATE TABLE dbo.QuestionType
(
    QuestionTypeID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_QuestionType
        PRIMARY KEY CLUSTERED
    , IsString bit NOT NULL
    , IsDate bit NOT NULL
    , IsInteger bit NOT NULL
);

CREATE TABLE dbo.Questions
(
    QuestionID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Questions
        PRIMARY KEY CLUSTERED
    , QuestionTypeID int NOT NULL
        CONSTRAINT FK_Questions_QuestionTypeID
        REFERENCES dbo.QuestionType(QuestionTypeID)
    , QuestionText nvarchar(100) NOT NULL
);

CREATE TABLE dbo.People
(
    PersonID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_People
        PRIMARY KEY CLUSTERED 
    , PersonName nvarchar(100) NOT NULL
);

CREATE TABLE dbo.Answers
(
    AnswerID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Answers
        PRIMARY KEY CLUSTERED
    , PersonID int NOT NULL
        CONSTRAINT FK_Answers_PersonID
        FOREIGN KEY REFERENCES dbo.People(PersonID)
    , QuestionID int NOT NULL
        CONSTRAINT FK_Answers_QuestionID
        FOREIGN KEY REFERENCES dbo.Questions(QuestionID)
    , AnswerString nvarchar(1000) NULL
    , AnswerDate datetime NULL
    , AnswerInteger int NULL
);

INSERT INTO dbo.QuestionType (IsString, IsDate, IsInteger)
VALUES (1, 0, 0)
    , (0, 1, 0)
    , (0, 0, 1);

INSERT INTO dbo.Questions (QuestionTypeID, QuestionText)
VALUES (1, 'This is a text question')
    , (2, 'This is a Date question')
    , (3, 'This is an integer question');

INSERT INTO dbo.People (PersonName)
VALUES ('Max');

INSERT INTO dbo.Answers (PersonID, QuestionID, AnswerString, AnswerDate, AnswerInteger)
VALUES (1, 1, 'This is a test', NULL, NULL)
    , (1, 2, NULL, '2017-03-06T13:24:00', NULL)
    , (1, 3, NULL, NULL, 27);

You could place a constraint on the dbo.Answers table to match the QuestionTypeID to prevent invalid entries being added.

  • How would you this when there are like 7 different enum types? – J. Pichardo Mar 6 '17 at 23:54
1

Look at your table from the feature usage side:

  • If you always access a single record by id, or work with data sequentially (example - answer for all questions) - continue to use a single table
  • If you plan to make any group by, filter or search operation over a range of records - main table PK + columns for these operations, second (and more) - other tables. Example - select query for specific topic.
  • Have TEXT/BLOB - move to second table
  • If number of columns unpredictable - look for JSON type of column

Real practice shows that 3-5, max 10 parameters are used for search and group by. These columns have indexes, and if indexes are created properly (including for foreign keys) - all operations will be fast.

1

If most columns are blank, you should look at the Entity-Attribute-Value method. This deals with sparse columns nicely and can save you a lot of space. If you are designing a question with multiple answers and types, you could design it in the following way:

Respondent
--------
RespondentID         int
RespondentEmail      varchar(200)

AnswerType
--------
AnswerTypeID         int
AnswerType           varchar(30)
Size                 int

Question
--------
QuestionID           int
AnswerTypeID         int
QuestionText         varchar(500)

Answer
--------
AnswerID             int
RespondentID         int
QuestionID           int
Value                varchar(500)

Respondent Table:

9 / jimmyjimmy@yahoo.com
8 / someoneelse@gmail.com

AnswerType Table:

1 / Text / 100
2 / Int  / NULL
3 / Date / NULL

Question table:

4 / 1 / "What is your name?"
5 / 2 / "How old are you?"
6 / 3 / "When did you join the company?"

Answer table:

1001 / 9 / 4 / "Jim Smith"
1002 / 9 / 6 / "2010-05-01"
1003 / 8 / 4 / "Yohan Sebastian Bach"
1004 / 8 / 5 / "30"
1005 / 8 / 6 / "2009-08-05"

A few notes:

  • This has the advantage of not having to store blank answers for optional questions. As questionnaires often follow optional paths, it means you are not storing details for paths that were not traversed by the respondent.

  • One quick note is that you should parse the data type based on the
    specified type in the AnswerType field both when displaying the
    question (to ensure you can present the right item, e.g. date picker) AND when data is submitted (to ensure you are receiving the right
    type).

  • This has the disadvantage of always storing the answer in a text
    field which will need to be converted to a specific type to perform
    calculations. Alternately one advantage is indexes can be built on
    the Answers table which will make data retrieval very efficient.

  • Most DBMSs usually only store the used portion of varchar types,
    meaning you are as space efficient as possible.

  • The data in the Answer table can be easily pivoted in a view to get a "normal" view of the data with a single answer per person, per row.

  • Thanks, but I see one more disadvantage, a lot of my fields are Enums so the AnswerType table would also grow on columns, besides, please check the Question Update – J. Pichardo Mar 7 '17 at 3:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.