I have a user table with around 30 columns. Most of these columns are almost always null unless user decides to share additional info about themselves. For example, weight, height, religion, is_smoking, is_drinking, star_sign, school, job, etc.

In my app, I have on option to filter users based on these values. I might be adding one or two columns like these in future.

I was just wondering if there is a better design approach for situations like these. For some reason, it doesn't feel right to have all these nullable columns in the same table with login credentials.

PS: I am using PostgreSQL 11.4 as the database management system.

  • Sparse table is common. You may switch to EAV if needed, especially if attributes list is not static\.
    – Akina
    Commented Oct 2, 2019 at 11:37
  • 1
    You may want to research "Sixth Normal Form". some database people have used this term to describe a schema that is in Fifth Normal form and has been further decomposed so as to allow forbidding NULLs completely. Commented Oct 2, 2019 at 11:39
  • 30 columns plus one or two is not that many. Unless you can articulate something more than "it feels wrong", I wouldn't worry about it. Maybe it is the credentials that should be moved. I hope they are at least salted and hashed with an intentionally slow method. That is true regardless of how many other columns there are.
    – jjanes
    Commented Oct 3, 2019 at 20:40

2 Answers 2


You could have a table

CREATE TABLE user_info (
   user_id bigint NOT NULL REFERENCES users,
   property text NOT NULL,
   value TEXT NOT NULL

and store the additional properties there. That would be the relational way of doing it.

In recent PostgreSQL versions you could also add a jsonb column for all such information.

With a GIN index on it you can filter efficiently as long as you are looking for certain keys and their value. But you won't be able to do advanced things like substring or similarity matches that way.

It is a matter of taste which one you prefer.

  • I think JSONB is what I need. I don't need substring or similarity matches. Is there a performance compromise while querying on multiple fields (is_drinking & is_smoking) in case I use JSONB instead of columns? Commented Oct 2, 2019 at 15:05
  • You'd have to test it. I personally would go with the first solution. Commented Oct 2, 2019 at 15:26
  • Why would you go with the first solution? Commented Oct 2, 2019 at 15:29
  • Because it is relational. Relational databases are good with relational models. It just feels better to me. Commented Oct 2, 2019 at 15:31

If you don't really need the data type validation (that ensures that you can't store 'five' in an integer column), you could create a single JSONB column to dynamically store the values

create table user_account
  id integer primary key, 
  username varchar(50) not null unique,
  attributes jsonb

Then you can do things like

insert into user_account (id, username, attributes)
(1, 'arthur', '{"is_drinking": true, "job": "hitchhiker"}'),
(2, 'ford', '{"is_smoking": false}');

To find all users that are drinking:

select *
from user_account
where attributes ->> 'is_drinking' = 'true';

The drawback is that you can prevent things like {"is_drinking": "only on weekends"} unless you create some triggers.

  • Is there a performance compromise while querying on multiple fields (is_drinking & is_smoking) in case I use JSONB instead of columns? Commented Oct 2, 2019 at 15:03
  • Can you also tell me your opinion on the first solution in Laurenz's answer? Advantages & disadvantages of that method over JSONB. Thanks beforehand. Commented Oct 2, 2019 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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