0

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 Oct 2 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. – Walter Mitty Oct 2 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 Oct 3 at 20:40
1

You could have a table

CREATE TABLE user_info (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   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? – Shahin Mursalov Oct 2 at 15:05
  • You'd have to test it. I personally would go with the first solution. – Laurenz Albe Oct 2 at 15:26
  • Why would you go with the first solution? – Shahin Mursalov Oct 2 at 15:29
  • Because it is relational. Relational databases are good with relational models. It just feels better to me. – Laurenz Albe Oct 2 at 15:31
0

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)
values 
(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? – Shahin Mursalov Oct 2 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. – Shahin Mursalov Oct 2 at 15:35

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.