vendredi 30 août 2019

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

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!

Aucun commentaire:

Enregistrer un commentaire