Oracle Table design question: configuration table, default values, primary keys

Question

need to design a table in Oracle, these are my columns:

1. COUNTRY
2. PRODUCT
3. PARAM1
4. PARAM2
5. PARAM3

My first thought was that I would like the combination of Country and Product to be a PK. But then, I also want a catch-all/Default for anything that doesn't fall into the records for Country + Product. Example:

• Product A and Country US: have param values as follow 1,1,1
• Country France for all products: have param values 2,2,2: should I have a record for every possible product? Is it possible to have a record for France and all products?
• Any other Country & Product combination has params 3,3,3: what's the best way to achieve this without creating a record for every single country and product. Countries are probably not going to change a lot, but I could get new products all the time and I don't want to be updating this table every time this happens

Looks like I should let Product and Country be nullable and not a PK, but I wonder if I'm missing any other options.

Any thoughts would be greatly appreciated.

Cheers!

Answer 1

One possibility is the use of virtual columns:

CREATE TABLE country_parms
    (country                        VARCHAR2(50 CHAR),
    product                        VARCHAR2(50 CHAR),
    param1                         VARCHAR2(50 CHAR),
    param2                         VARCHAR2(50 CHAR),
    param3                         VARCHAR2(50 CHAR),
    country_nn                     VARCHAR2(50 CHAR) GENERATED ALWAYS AS (NVL("COUNTRY",'-')) ,
    product_nn                     VARCHAR2(50 CHAR) GENERATED ALWAYS AS (NVL("PRODUCT",'-')) ,
    PRIMARY KEY  (country_nn, product_nn)
    )

With this approach you can also use fk-constraints to your country and product table. To select the values you use a view like the following:

SELECT c.country
     , p.product
     , CASE WHEN cp1.country_nn IS NOT NULL THEN cp1.param1
            WHEN cp2.country_nn IS NOT NULL THEN cp2.param1
            WHEN cp3.country_nn IS NOT NULL THEN cp3.param1
            ELSE cp4.param1
       END param1
     , CASE WHEN cp1.country_nn IS NOT NULL THEN cp1.param2
            WHEN cp2.country_nn IS NOT NULL THEN cp2.param2
            WHEN cp3.country_nn IS NOT NULL THEN cp3.param2
            ELSE cp4.param2
       END param2
     , CASE WHEN cp1.country_nn IS NOT NULL THEN cp1.param3
            WHEN cp2.country_nn IS NOT NULL THEN cp2.param3
            WHEN cp3.country_nn IS NOT NULL THEN cp3.param3
            ELSE cp4.param3
       END param3
  FROM country c
 CROSS JOIN product p
  LEFT JOIN country_parms cp1
    ON cp1.country_nn = c.country
   AND cp1.product_nn = p.product
  LEFT JOIN country_parms cp2
    ON cp2.country_nn = c.country
   AND cp2.product_nn = '-'
  LEFT JOIN country_parms cp3
    ON cp3.country_nn = '-'
   AND cp3.product_nn = p.product
  LEFT JOIN country_parms cp4
    ON cp4.country_nn = '-'
   AND cp4.product_nn = '-'

Using the cross product of all countries and products and then first look for the exact fit, then the one where product is empty, then where country is empty and finally where both are empty to get the config of the parameters.

Or even simplier:

  SELECT MAX(param1) KEEP (DENSE_RANK FIRST ORDER BY country nulls last
                                                   , product nulls last) param1
       , MAX(param2) KEEP (DENSE_RANK FIRST ORDER BY country nulls last
                                                   , product nulls last) param2
       , MAX(param3) KEEP (DENSE_RANK FIRST ORDER BY country nulls last
                                                   , product nulls last) param3                                              
    FROM country_parms
   WHERE country_nn IN (:country_nn, '-')
     AND product_nn IN (:product_nn, '-')