stumbling block on my Database design, stock tracking.. help.

[Tea_J]

hi guys

i basically just need your ideas ..

I am developing an inventory system inventory system for products catering to both SERIALIZED and CONSUMABLES

Primary Tables are:

PRODUCTS
Master table for product details like name, brand, model, specifications.. also has important detail such as
- serialized = 0/1 (if product has serial/trackable)
- stocks_new = all available stocks for sale
- stocks_all = all availabel stocks for sale, new or old/used/refurbished
(stocks_new and stocks_all columns are automatically updated at every transaction)

STOCKS
This is a details table for each individual serialized stocks, maintaining actual price / stock, serials, and other unique info to each stock.. this is generally for history/tracking.. includes colums:
- pid (prodcut id, related to Products table)
- serial
- cuid (customer id)
- soid (sales order ID)
- oid (order item ID, somewhat related to sales order above)
- drno_in (Delivery Receipt from Vendors)
- drno_out (Delivery Receipt given to our CUSTOMERS)

SALES_ORDERS
Master table for all sales orders.. has columns:
- soid
- cuid

ORDER_ITEMS
Details table for SALES ORDERS, contais each order line... has columns:
- oid
- soid (sales order id , above)
- pid (product id)
- sid (stock ID for stock specific items)
- qty (how many quantities)
- price (price per unit)
- drid (Delivery receipt ID)

DELIVERY_RECEIPTS
Table to hold the actual delivery records to customers, where Sales Order is the request to fullfill , and Delivery Receipt is the record of actual delivery to customers.. includes the followin columns:
- drid (delivery receipt id)
- soid (sales order ID)
- stamp (unix time for when delivery was made)

Ok, as an overview, sales staff creates the SALES ORDER, each sales order entry will have order items specific to each sales order.. data would look like:

for SALES ORDER # 001, order item would include:

oid/pid/stid/price/qty
ORDER ITEM 001 / 12 / 32 / 2.50 / 1 (since this has a specific stock (stid), means this order item has a specific stock with serial etc, therefore quantity is always set to 1)

ORDER ITEM 002 / 13 / 0 / 3.99 / 10 (this is consumable type of product, so this order item may have 1 or more qty)

Tracking wise, all is well up to this point.. each stock will contain the drno_out indiciate w/ delivery receipt batch it was part of, and a sales order id from w/c this item was sold with...

and when i need to know the serial numbers of the items related to that sales order/delivery receipt, i can simply look up stocksk w/ the same drid in the drno_out colum...

Now, my dilema is , in REAL life, there are situations where stocks are recalled, returned for replacement, or even exchanged for other items.. this can also mean a specific stock can be received and resold more than 2 times.. ..

HOW DOES ONE PROPERLY TRACK THIS?

Ofcourse I would think , why not a transactions table, or perhaps generating 1 Delivery REceipt entry / stock / per activity (receiving , and selling/sending out)..

this would easily solve my problem.. but then i got to thinking, what if there was an order for 100pcs LCD monitors... and these monitors where returned and exchanged for another type of product.. and then these 100pcs were SOLD again to another customer.. and then returned for RMA.. (just a sample scenario but still possible)..

basically the above scenario involves these 100 monitors being in and out of the shop for a lot of times already.. and GENERATING 1 DR entry (or even an entry in a new DR Items table) per stock, means a whole lot of entries (and clutter).. even if i used transactions table to monitor each movement of each stock, w/c is basically like the DR table .... it's the same clutter issue.. and this is just one product.. there are even instances of 100 computer units being sold, each computer unit containing 10 or more items (lcd, procesor, motherboard, etc) ... that's an easy 1000 entries in the DR or TRANSACTIONS /LOGS table.. per movement..

and dont get me started w/ WAREHOUSE TRANSFERS? what if you would transfer an item from 1 warehouse to another a few times.. this means thousands of entries/rows easily..

IS THERE A BETTER WAY of achieving tracking and stuff? What do you guys think about my situation?? Am i just being soo paranoid of the thousands of little movement entries, w/c mysql is actually built for and i shouldnt worry about??

i'm pretty stuck .. i know several solutions to achieve what i want specially in tracking but i guess my issue is OPTIMIZATION of design... i just wana make sure im right ...

Really hope for some feedbacks..

TJ

[Tea_J]

.....

im thinkin of just adding a field in the ORDER ITEMS entry for, w/c will hold all stock specific IDs , comma delimitted...

[Tea_J]

.. or "F" it.. so what if i have another entry per transaction per item in the database? so what if it can easily go up to 10,000 records in under 1 month.. right? it provides the best means of tracking each stock item, as well as easier coding. hmmm.. i think im just being tooo paranoid or OPTIMIZATION freak ..

[johanafm]

Tea_J;10984304 wrote:

so what if i have another entry per transaction per item in the database? so what if it can easily go up to 10,000 records in under 1 month.. right? it provides the best means of tracking each stock item, as well as easier coding.

Indeed, stay away from comma-separated hell! 10k rows per month? No problem. Make sure you have proper indexing (most important), pay attention to how your write your queries (important, but pointless without proper indexing), and if some queries becomes too slow you deal with them in some situation-specific way such as running a query once per day to give you the proper subset of data to work with or cache results of queries that are run often etc (only needs to be done when the other two aren't enough).
If you do this, your DBMS should be able to handle millions, tens of millions or hundreds of millions of rows, depending on hardware and how often those queries are performed ofc.

Also, once per year / fiscal year, whatever suits you best, you could rename the order table to order_2010, order_2011 etc and create a new order table, so that you'd only have to deal with the accumulated rows for one year rather than forever.

I didn't quite understand the purpose of all your tables, such as STOCKS, which looked pretty much like a mix between sales order and current price for a specific item and customer. But perhaps there is some purpse I don't get.

However, you have a lot of redundant data in your tables. For example, a receipt will always concern one or more rows in order items (everything shipped or returned at a single point in time), and each such row is always only on one receipt: 1:n relation, or one-to-many. As such, each order_item should have a receipt_id, but the receipt_id should not have a sales order id, since every single item the receipt concerns has that information. And once you adjust your schema, you might even deal with 2 returned products from two different orders (by the same client) on one receipt, should you want to. Each item in turn refers to a different sales order, which means that so does the receipt.
Another example is sales order which has a one to one correlation with stocks, which makes no sense. And should a sales order not have date, customer name, billing address, shipping addres (these could all change in the customer table in the future while you wish to retain the correct information for a particular sales order), as well as contact person?

And as for the tracking issue, you could deal with returned items in a "credit / debit" way in the order_item table. That is, an order_item has qty and price, such as 10 items at $5.00, and if 4 are returned, you insert 4, $-5.00. However, it comes down to the needs of the economy department and how things like these are handled in the end. The above works well if they are returned for good, but may not be enough if they should be replaced by another 4 items, since you may not wish to repay the customer now and charge them again as you send out the new items.
So you may have to use a separate table for items that should be replaced.

[/quote]
hmmm.. i think im just being tooo paranoid or OPTIMIZATION freak ..[/QUOTE]
No, you should always consider performance when you start working on large data sets.

[Tea_J]

@,

thanks very much for responding to this thread...

1st of regarding redundant data, i actually dont rely on the redundant data, i just keep related data in different tables for purpose of easier and faster queries. i have found this technique to be very helpful , and it's used by many complex databases and applications, like forums for example.. and so is the "comma hell" hahha. but yeah i will try to keep away from comma hell , haha

I didn't quite understand the purpose of all your tables, such as STOCKS, which looked pretty much like a mix between sales order and current price for a specific item and customer. But perhaps there is some purpse I don't get.

stocks are the ACTUAL INDIVIDUAL stocks w/ serial. for my purpose it's not enough just to maintain a PRODUCT INFO (products table) and a qty count... how do i track serialized items w/c are 99% of the products well be handling (computer store)..

basically im designing a system where i need to be able to tell the COMPLETE HISTORY of a SINGLE ITEM, eg. Inte i7 2600K w/ Serial # 654946504606546

see im dealing w/ complex tracking. hence the complexities 🙁
here are just some of things i need to keep track,
the what when and where (what branch or warehouse), who (suplier or customer) and how much :
1) Purchased from supplier
2) sold to customer
3) returned by customer
4) returned to supplier
5) returned by supplier (back to us)
6) returned by customer
7) replaced by another item
8) was used as a replacement of a different defective item (sales replacement)
9) transfered to different location
etc etc

i've pretty much figured out how to do most of it efficienly, but i've hit a stumbling block really bad, hence this thread....

I guest the simpliest way to ask this is,

im really just lookin for a technique/method on how to track properly if an item (specifically stock coz it is a specific item with serial) if it has been sold to a customer, then is returned / replaced, then that same specific item was sold again to another customer, and then returned..

the existing technique i was using is simple,
from sales order , where each sales order item will have a corresponding entry in the delivery receipt

eg.
SALES ORDER 0001 has 1 sales order item entry, below
SALES ORDER ITEM , intel i7 2600K x 3pcs (notice 1 SO item reflects 3 qtys of different stockable item (serialized) ).. each serial is then sorted / indicated properly during delivery below

when finalized/delivered

DELIVERY RECEIPT TABLE will 1 entry #0001
delivery_items table will have 3 rows and include the following data

intel i7 2600K / serial 163415646574
intel i7 2600K / serial 163415646575
intel i7 2600K / serial 163415646576

• this process makes sure that what is delivered (exact item) are properly recorded ..since @ sales order stage cancellations, additional qty's and other scenarios can still occur..

THE ABOVE IS THEN DUPLICATED to a transaction/log table
intel i7 2600K / serial 163415646574 / stat = sold
intel i7 2600K / serial 163415646575 / stat = sold
intel i7 2600K / serial 163415646576 / stat = sold

then when item
intel i7 2600K / serial 163415646574
gets returned

transaction/log table will get adidtional entry
intel i7 2600K / serial 163415646574 / stat = returned

then when sold again to another customer, a new corressponding rows in SALES ORDER< sales order item, DR and DR item, as well as Transaction log are obviously inserted..

the above works OK for me, sysyem is then able to Track each item whereever it goes whenever it happens!!

THEN THE STUMBLING BLOCK scenario

WHAT IF, there was an order for 1000pcs of this processor, this means 1000 of rows on each
DR and Transaction log ... w/c , i dont know if it's just me being paranoid, feels like clutter....

so i was thinking one way of doin this is put all the related products from the DR into the Transaction Logs table in 1 row, but some how indicate all the IDs of specific products related to this transaction (comma hell!) ..

that's actually my issue hhehe. but im almost giving up w/ too much optimizing logic, i dont think there's any other way to do this detailed tracking unless i do have a ROW reflecting EACH item/stock ...

(as for the non stockable items, or no serial, deemed consumable like mousepads, sata cables etc, its easy and no tracking is needed, that's the PLUS MINUS routine you were talkin about)

[johanafm]

Ah, now I get the use of STOCK. And yes, stick with one transaction tracking row per specific item. Comma hell really really needs to be avoided. Finding a specific item when they're each in their own row is fast, since you retrieve it directly based on an indexed value. It is fast and easy to insert, update and delete.

To move something out of a comma separated list, you'd need to perform string comparison to find the proper row, retrieve the comma separated list, break it apart and rebuild it and update the row accordingly.

For more in depth information on how to structure tables, read up on normal forms 1 thorugh 4, 1NF, 2NF, 3NF, 4NF, and while you're at it you might as well read about BCNF which is in between 3 and 4. Googling for database normalization should give enough places to look, or just go straight to en.wikipedia.

[Tea_J]

@

Thanks very much verifying man, gives me confidence to go down that path, i just gota get over my Obsesive Compulsiveness hahah (re row clutter)

and yes, i do know about normalizations (not much detail though), but thanks for pointin me directly to those specific topics, i guest it's time i review also

Thanks again mate!!

TJ