Member Avatar for erikkl2000

I have a table in my database that is a base table for my blind companys. Each company can share the same price table.

(This price table only hase 5 columns in it)
1. BlindSizeID(KEY)
2. Maxheight
3. Price
4. CompanyID (FK)
5. ProductID (This one might take a hike)

Anyway, i am connection these to a set of dropdowns that are on the UI.
For instance one Dropdown will be the width and one will be the Height and One will be the company (Blind Maker Of Choice (Thier options of company)).

Here is my question they all can sell the same product; however, their priceing is all eaither a little higher or a little lower than each other. Is it possiable (Without recreating the wheel) to add or subtract the result that is returned from the stored query?

Should i do this all in the database or just the application..??

Thank you!

Erik...

  • 2 Contributors
  • 1 Reply
  • 110 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by Paladine
kbjbkjbkj commented: facebookx +0

Dani AI

Generated

A few practical patterns to make per-company pricing clean and maintainable — building on 's schema and 's comment about doing adjustments in the application.

Keep one canonical base price per product, and store company-specific adjustments separately. That gives you a single source of truth for product price and a small, auditable table for differences (either fixed or percentage). Example table shape (illustrative):

CREATE TABLE Product (
  ProductID INT PRIMARY KEY,
  BasePrice DECIMAL(10,2)
);

CREATE TABLE CompanyAdjustment (
  CompanyID INT,
  ProductID INT,
  AdjustmentType CHAR(1), -- 'P' = percent, 'A' = absolute
  AdjustmentValue DECIMAL(10,4),
  PRIMARY KEY (CompanyID, ProductID)
);

When you need the final price, you can compute it in SQL with a left join and a simple CASE so the database returns the correct value for reporting or API calls:

SELECT p.ProductID,
  COALESCE(
    CASE ca.AdjustmentType
      WHEN 'P' THEN p.BasePrice * (1 + ca.AdjustmentValue / 100)
      WHEN 'A' THEN p.BasePrice + ca.AdjustmentValue
    END,
    p.BasePrice
  ) AS FinalPrice
FROM Product p
LEFT JOIN CompanyAdjustment ca
  ON ca.ProductID = p.ProductID AND ca.CompanyID = @CompanyID;

If you prefer to return base price from a stored proc (as suggested) and apply adjustments in the app, keep the same adjustment table and apply the same math in code. Always use DECIMAL for money, be explicit about rounding rules, and index CompanyAdjustment on (CompanyID,ProductID) for fast lookups.

When to do it where: calculate in the DB when multiple services, reports, or integrations consume price and you want central consistent logic. Calculate in the app when business rules are highly dynamic per session or when the UI needs to preview multiple hypothetical adjustments quickly.

Member Avatar for Paladine

I would recommend you do this in the application. The stored procedure can be set to return a value, and you can pass that value into a variable that you can now manipulate (add, substract, etc) as you see fit.

Hope this helps

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.