Making a foreign key nullable where it wouldn't always make sense

Question

I'm trying to come up with a good database design for this use case

I have a table called expenses as follows

id
category_id
item_id

Problem

• item_id column wouldn't make sense if the expenses category is i.e. "Rent"
• It would only make sense if the category is i.e. "Food"

I have thought of two different approaches

1st approach:

Make the item_id column nullable

2nd approach:

Create a separate table called expense_item with the following columns

expense_id
item_id

And drop the item_id column from the expenses table

I'm not sure what approach I should go with or if there's a better approach. I'm not sure how to handle this case scenario

Answer 1

The following technique is just an extension of your 1st approach. You allow the item_id to be NULL, but validate its null or not null value against the category. I presume you have a categories table, if not you create it as you do in need it anyway. Now create or add a column item_required boolean. Create a trigger that checks the presence or absence of item_id against item_required for the specified category. This results in something like the following: (see demo here)

create table expenses(exp_id   integer  generated always as identity
                                        primary key
                     , cat_id  integer  references categories(cat_id) 
                     , item_id integer
                     ); 
                     
create or replace function validate_category_item()
   returns trigger 
  language plpgsql
as $$
begin 
    if  not exists(select null                              
                     from categories                    
                    where cat_id = new.cat_id             
                      and (   (    new.item_id is null
                               and not requires_item
                              )
                          or  (    new.item_id is not null
                               and requires_item
                              )
                          )
                  )
    then
        raise exception E'*** Item Id Exception. ***\n1. Specified Category requires item and Item missing. \n2. Specified Category does not take Item but Item Present ';
    end if; 
  
    return new;
       
end;
$$;

create trigger cat_item_check_aiur
  after insert or update on expenses
  for each row 
  execute function validate_category_item();  

The assumption here is that a category either must have an item or cannot have an item. You of course need to adjust the trigger to your exact requirement.
In case it does not yet exist the following is a sample setup for a categories table.

create table categories( cat_id   integer  generated always as identity
                                           primary key 
                       , name     text
                       , requires_item boolean
                       ); 

insert into categories(name,requires_item)
     values ('Rent', false)
          , ('Food', true); 
      
select * from categories;