CODEWITHSUNDEEP
mysql
Ben in CA
Ben in CA

Reputation: 851

Record Master values in MySQL database?

I’m trying to figure out a good way of handling this situation strictly using MySQL. (using generated columns, views, logic statements, procedures, etc.)

I’ve simplified this for the example. Let’s say I have a table storing cost of production information for particular products in particular years in particular factories.

Some of these costs are specific for the product. (plastic, molding cost, packaging, labour, etc.)

And some of these costs are fairly generic; I may want to assign a specific value to them, but for many of them most of the time, I’ll just want them to refer to a particular value for the factory in that year. I’ll refer to these as “Master” values. (Such as overhead costs, so things like interest costs, electricity, heat, property taxes, admin labour, etc.)

Then if I update my Master values, the costs on these will automatically be adjusted; and they could be different for each year and factory. (So I can’t just use default values.)

So my columns might be:

table_example

And here’s the logic of how that would be defined:

$MValue(var) = var WHERE product_id = M (Master) AND year = year AND factory_id = factory_id;

Then essentially, if I wanted to use a unique cost for that product, I could put the cost amount in the field, but if I wanted it to use the Master value (shown on row 4, designated by M), then I could insert $MValue(column_id) in the field.

Any thoughts on how this could be accomplished with MySQL?

I should add that I’m already using generated (calculated) columns and views on these fields, so thus why I’m looking for a strictly MySQL solution.

Upvotes: 1

Views: 89

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562771

I suggest storing the derived costs as NULL in your product rows, and then define a view that joins to the master row.

CREATE VIEW finalcosts AS
  SELECT p.cost_id, p.product_id, p.factory_id, p.plastic_cost, p.molding_cost,
    COALESCE(p.interest_cost, m.interest_cost) AS interest_cost,
    COALESCE(p.tax_cost, m.tax_cost) AS tax_cost
  FROM costs AS p
  JOIN costs AS m ON p.year = m.year and m.product_id = 'M (Master)'

There's no way to use a default or a generated column to retrieve data from a different row. Those expressions must only reference values within the same row.

P.S.: Regarding the terminology of "master" values, I have been accustomed to the terms "direct costs" and "indirect costs." Direct costs are those that are easily attributed to per-unit costs of products, and indirect costs are like your master costs, they're attributed to the business as a whole, and they usually don't scale per unit produced.

Upvotes: 1

Related Questions