"Avoid redundancy" is important to me.

I want to store the input data for configuration management in a relational database.

My input data:

  • project_name
  • linux_user
  • db_user
  • db_name

In 95% of all cases all attributes are equal:

  • project_name: foo
  • linux_user: foo
  • db_user: foo
  • db_name: foo

If I would store this data in YAML, then I could use variables to avoid repetition.

AFAIK this does not work in relational databases.

I could store this in DB, but AFAIK I need to evaluate it myself:

  • project_name: foo
  • linux_user: {project_name}
  • db_user: {project_name}
  • db_name: {project_name}

How can I avoid redundancy if I store this in a relational database? Unfortunately these values are not always equal, only in most cases.

I use PostgreSQL


There is no built-in way of doing this in SQL. If the default is always the project_name, I would probably allow nulls in the other columns, and create a view that uses coalesce() to return a default value:

create view real_data
select project_name, 
       coalesce(linux_user, project_name) as linux_user,
       coalesce(db_user, project_name) as db_user,
       coalesce(db_name, project_name) as db_db_name
from base_data;

Another option would be a complicated case statement for each column:

create view real_data
select project_name, 
       case linux_user 
           when '{project_name}' then project_name
           when '{db_name}' then db_name
           when '{db_user}' then db_user
           else linux_user
       end as linux_user,
       ... repeat the above for the other columns ..
from base_data;

You could hide the case statement in a function or use a set returning function and dynamic SQL, so you don't need to hardcode each column name and possible variable.

If you only need to make sure you have a default value when creating the rows, then just replacing that in a trigger during insert might also be an option. That way there is no need for the view.

  • I like the coalesce() solution. I guess I will use this. Thank you for your answer. – guettli Nov 22 '16 at 11:19

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.