1

I run a store that sells cigarettes, which have certain promotions given to us by the manufacturer that effect their pricing. These promotions are organized into groups of products (like all menthol or all reds) and are subject to frequent change, making them a bear to manage. My end goal here is to create a table(s) that will help me track these promotions and run an UPDATE query that will adjust their prices.

I have table inventory like 

itemnum|dept_id|cost |price
-----------------------------
123    | cig   | 2.6 | 3.4
234    | 401   | 2.22| 23.4
345    | cig   | 3.33| 3.45
456    | cig   | 4.00| 4.56
567    | 901   | 4.5 | 5.67
678    | cig   | 4.1 | 6.25
789    | cig   | 5.2 | 6.25

My initial thought was creating a set of new tables like

CigGroup

Brand | Group_id | Itemnum
-------------------------------
Altria|  a_men   | 123
Altria|  a_men   | 345
Altria|  a_black | 456
RJR   |  r_crush | 678
RJR   |  r_crush | 789

And
CigGroup_Promo

Group_id |promo_1|promo_2|promo_n...|net_promo|
--------------------------------------------
a_men    | .5    | 1     |  .1      | 1.6 (promo_1 + ...promo_n...)
a_red    | .25   | 1     | NULL     | 1.25 
a_black  | .25   | .5    | .1       | .85
r_crush  | .25   | .1    | NULL     | .35
r_filter | .35   | .5    | NULL     | .85

I thought that maybe I could do something conditionally with foreign keys and set Cig_Group.Itemnum to reference inventory.itemnum only when inventory.itemnum = 'cig', though from SQL Server Conditional Foreign Key I gathered that this might not be possible. (I've also looked into composite keys, but not sure how to apply this to my data)

So, here are my questions:

First, is it possible to populate my new table(s) (however that ends up being structured) with inventory.itemnum only when inventory.dept_id = 'cig' ?

Second, can i set CigGroup_Promo.Net_Promo as a function of promo_1, promo_2, promo_n..., or is that yet another table that I would be creating?

Any suggestions on how to structure tables for these data and how to relate them would be greatly appreciated.

Side note: I could, instead of creating CigGroup, create new values for inventory.dept_id, which I would honestly prefer not to do, but might make things simpler.

Once all the tables are created and related, I'm hoping to be able to run something like: 

UPDATE inventory i SET price = 
CASE WHEN 1.07 * (i.cost - g.net_promo)  >= .5 + (i.cost - g.net_promo)
THEN 1.07 * (i.cost - g.net_promo)  
ELSE .5 + (i.cost - g.net_promo) 
END 
FROM inventory i JOIN GigGroup g ON i.itemnum = g.itemnum 
                 JOIN CigGroup_Promo p ON g.group_id = p.group_id
Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
Yofi
  • 47
  • 6
  • 1
    You can certainly add a _computed column_ `Net_Promo` to `CigGroup_Promo` that is the sum of the promos across the row. You'll need to allow for NULLs, e.g. `IsNull( promo_1, 0.0 ) + IsNull( promo_2, 0.0 ) + ...`. – HABO Jan 18 '19 at 19:07
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) – philipxy Jan 19 '19 at 02:54
  • @philipxy You're right, that query is very similar. Thank you for pointing me to it, it's quite enlightening. The only problem that leaves me with is wondering whether or not i can reference `inventory.itemnum` with a foreign key only `where inventory.dept_id = 'cig'` or whether i will have to `insert` new data as they appear in `inventory` – Yofi Jan 19 '19 at 17:11
  • I don't understand your problem. But in general subtyping designs involve updating multiple tables together; it depends on details, including calculated/computed columns. – philipxy Jan 20 '19 at 00:07

1 Answers1

0

Looks to me like there are multiple solutions for design available that would depend on how the source data is loaded and whether you require to track all periodic changes (in which case your model will need datetime-support).

There may be a variety of options, but I would explore a Star Schema design which would entail building your wide and descriptive dimension tables to link with a PKey - FKey relationship to a central Fact table that records all your transactions (in your case that would be the various "promotion" prices that need to be tracked).

In your example based on my comprehension i would opt for a star schema design with dimensions for item, brandGroup and any other required dimensions along with a fact table for tracking inventory and another fact table for tracking price updates. By designing the tables to a conformed dimensional model we can do all types of analysis across this new warehouse.

With regards to your "CigGroup"table specifically, I would create a table for "Items" with the most granular SKU / item on sale, which can then be structured into a hierarchy using attributes, or new columns in the table.

Gleb
  • 21
  • 4