Schema required to find a record in a table with null values



  • I have the following table.

    ID Country City Street No
    1 UK 2
    2 Lon
    3 Oxf 19
    4 UK Glas
    5 US NY Wall
    6 US NY 14
    7 5

    In this table, I have defined some address rules. For example.

    1. If Country is UK and house number is 2: The value of City and Street does not matter
    2. If City is London: The value of Country, Street & No can be anything.
    3. The empty cells are NULL and that means they will match any value.

    Now, I have an address with the following properties:

    {
        "Country": Brazil,
        "City": Rio,
        "Street": Aven,
        "No": 5
    }
    

    I need to find the rules, that this object matches with.

    The above address, will match the last rule, because No is 5.

    Now my question: is the above table structure suitable for my purpose? When searching for a record in the rules, I have to ignore the null values for all columns, so I think that will make the query inefficient.

    Also, in the above table, what index should be used to cover all columns and conditions? Is it possible to have an efficient index to cover everything?

    Another concern that I have is redundancy. As you can see, the Country, City and even Street could be inserted repeatedly. Is it possible to reduce redundancy here?

    What's the recommended schema to store and search in such data? I am using PostgreSQL. Also, note that these data are read-intensive. The rules are defined once in a while, but searching happens a lot.



  • One way I can think of, is to convert the row to a JSON value, remove the NULLs, and then check if your comparison value contains that "rule json":

    Given your sample table and data:

    select *
    from rules r
    where '{"Country": "Brazil","City": "Rio","Street": "Aven","No": 5}'::jsonb 
              @> jsonb_strip_nulls(to_jsonb(r) - 'id') 
    

    Returns the rule with id = 7 (assuming that No is defined as an integer column)

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=af549ddb6db147f8879fc3fb1cd38917




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2