0

I have a question about how I can model my stock database in order to get the best performance possible.

In SQL Server or in the Oracle, each update executed generates a little lock.

I'd like to know what's the best solution that you could tell me

Solution 1: create a product stock table with quantity column and for each input or output execute a SQL update against this column

Solution 2: create a table for product stock movement where for each input I would execute an insert with a positive quantity and for each output I would execute an insert with a negative quantity.

At the end of the day, I would execute an process for update the quantity of the stock products with the "sum" result of the product stock movement table After that, I would delete all records in the product stock movement table

With solution 1, I would have the advantage that execute an simple select to get the product stock quantity but during the day I would have the disadvantage that have many locks due many quantity updates regarding output sold products

With solution 2, I'd have the disadvantage when, I will need to get the product stock quantity, I'd need to make a query with a join with product stock movement table and make a sum in all inputs and outputs of the consulted product, but in this way, during all day I wouldn't have any locks

What do you think about that two solutions presented?

Is it a good practice to make the modeling described in solution 2?

Thank you so much

| improve this question | |
  • 1
    INSERTS create locks, too. Perhaps start by googling some tutorials. Generally, there is no perfect solution, just a best fit: sqlservercentral.com/blogs/basits-sql-server-tips/2012/10/21/… – Victor Di Leo Aug 21 '18 at 0:19
  • yes, i agree, but in my opinion, in this case the update will lock a record line that's accessed by many other different modules and the insert will lock a record that's accessed just by modules that will need get quantity value. Do you agree? – Midnight biker Aug 21 '18 at 1:43
  • Good point. These decisions prompt some questions: how current do the reports need to be? If up-to-the-minute, then option 2 may be your only option. But if a 24-hr lag is fine, then option 1 may be best. A third option would be both; option 1 would be for summary reporting with a 24-hr lag, option 2 would be a history table with fine-grained detail of what products were ordered by whom and at what time, plus the method of shipping. I mention it because in my experience, eventually folks will want to see that data too. Gives the best of both worlds but would require more maintenance. – Victor Di Leo Aug 21 '18 at 14:22
  • Yes @VictorDiLeo I agree too. The question now is if I update the stock quantity each sell for use a simple query to get this information or if I update the stock quantity once a day and when I need to get stock quantity I use a complexest querie. – Midnight biker Aug 21 '18 at 17:10
  • Not to sound off-putting, and with all due respect to you for asking good questions, it's tough to give good detailed advice without knowing many details, so I'll keep it general: stay close to third-normal form (3NF). It is the most efficient for transactional systems, and is quite modular in design, giving you flexibility. OLTP query engines/optimizers were designed to work in 3NF. So follow guidelines for primary keys, indexes and writing optimal SQL. Any performance issues that arise can then be dealt with as they arise. – Victor Di Leo Aug 21 '18 at 18:02
1

I think you should consider your requirements a bit further and see which model fits better long term.

Regarding performance: How fast is fast enough? You need a target or goal to work to. And to do that you need to forecast load and volumes.

Ignoring performance and thinking about data models: personally i like the 2nd option because it can give you an audit trail. You can see which items were sold on which days, and can handle things like returns or stock take adjustments. This makes reporting, accounting and auditing easier.

| improve this answer | |
  • Of course, the audit trail only exists if you do not delete the product stock movement records after you do a daily true-up. – RDFozz Aug 22 '18 at 15:24
  • Ok, let's suppose I choose the option 2. Even so, I still can update stock product quantity for each sell or once a day and use both informations to compose my query result – Midnight biker Aug 22 '18 at 17:23
  • @RDFozz Yes you're right. Personally I'd keep the records and continually calculate. This may be not be feasible with massive volumes of records. Maybe consolidate monthly or yearly. Stored opening/closing balances? – Sir Swears-a-lot Aug 23 '18 at 1:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.