StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<ul> <li><p>My first question is, can all of the prices be calculated from one of the prices? If yes and the rules are the same across products, we're in a somewhat easier place.</p></li> <li><p>Do any of the prices change independently of other prices? You have hinted that the Sale Price is valid for 1 month and then extended - does the price remain the same or does it change? If it stays the same, this is easier than if it changes.</p></li> </ul> <p>You're on the right lines with having a start and end date set for product prices as this will be fundamental for historical price reporting.</p> <p><strong>EDIT:</strong> </p> <p>I was thinking that you could use <a href="http://msdn.microsoft.com/en-us/library/ms191250.aspx" rel="nofollow noreferrer">persisted computed columns</a> for the other prices based on the cost price, but since the prices can be manually adjusted this isn't an option (I don't believe you can override p.c.c. values). You could write a stored procedure to insert the initial prices into a <code>PRICE</code> table based on cost price.</p> <p>Based on the info so far, I think you're best option would be to have a separate <code>PRICE</code> table from your <code>PRODUCT</code> table and a product_id foreign key in your <code>PRICE</code> table referencing a primary key id of the <code>PRODUCT</code> table -</p> <p><strong>PRODUCT table</strong></p> <pre><code>id | name | image | description | etc... </code></pre> <p><strong>PRICE table</strong></p> <pre><code>id | product_id | list_price | cost_price | retail_price | gov_price | sale_price | bpa_price | start_date | end_date </code></pre> <p>It is prudent to have the start and end date fields on your pricing because not only can you historically report prices, but also populate the table with future prices. </p> <p>With the above structure, now when you have a price change, you would need to insert a new record in the PRICE table for the product. Set up indexes on start and end dates, then you would query products and prices as follows</p> <pre><code>SELECT product.name, price.list_price, price.cost_price, price.retail_price /*, ETC... */ FROM product INNER JOIN price ON product.id = price.product_id WHERE price.start_date <= @date AND price.end_date >= @date </code></pre> <p>You could normalize this design further and also have a <code>PRICE_TYPE</code> table. The thing to bear in mind with taking this approach however, is that if you want to get the full set of prices for a product, then the WHERE clause is applied to 6 records for each product.</p> <p>To handle the other company situation, provided the proper permissions and restrictions are put in place, I see <em>few</em> * problems with storing their prices in the same database. You say they use different markup - are you referring to their prices? If so, you could handle this with a company_id within the price table. Access to data could be controlled through stored procedures and updating prices through transactions.</p> <p>*it depends on how closely coupled the work is between companies. Is it permissible to share resources?</p> <p>(N.B. I've made the assumption that your target database is SQL Server, but I would imagine the logic to be similar for other platforms).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload