Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Inventory system proposal


philbish

Recommended Posts

I'm almost done with building my accounting system into my oscommerce store and no accounting system is complete without an inventory system. I am looking for your feedback on the following schema and methods I will use. I will have to re-write much of osCommerce to do this.

 

Yes, I know there are premade solutions to accounting and inventory, but I do not like how any of these work. I prefer to make my own from scratch :D

 

---

 

I propose a five tier product structure, with the fourth tier holding all text descriptions, photos, reviews and similar product info, and the fifth tier holding various versions of the product (colors, sizes, etc.). Inventory would be stored by version_id, which denotes the fifth tier and the smallest granularity of product info. This system will accommodate the current shopping experience of the Oznium store and allow for pull-down menus offering an unlimited variety of product options.

 

A four tier system with 30 items on each tier would accommodate 810,000 unique products. If each product then had 30 versions, the system would contain 24 million unique items. For example, Neon Mini Glow Stix would be one product in the fourth tier. Related to that product would be 24 versions in the fifth tier (the version table): 8 of those versions would be size=4?, 8 would be 6? and 8 would be 8?. Each of the sizes would have one version with each of the 8 colors. The version data is stored in an options table (see below). The shopping cart script would create an option menu for each unique type (color, size, etc.) in the option table, then populate that menu with each of the values for that type with that product_id. Prices can continue to be associated with products, and upcharges can continue to be attached to versions.

 

In the case of products with no options, such as underbody kits, there would be only one version and no options. When the customer calls the shopping cart script, it would get a count of zero options and therefore not display any option menus.

 

The above product structure enables the inventory table to use only the version_id and trans_id as a primary key to identify quantities of each item. We will need a special ledger entry script, similar to the current one, to provide for entry of all inventory purchases. That script will create both a ledger entry to increase inventory and decrease a bank account or accounts payable, and will create an inventory record for each product purchased. As the products are sold, the quantity in the inventory record will be decremented until it reaches zero. Also upon each sale, a ledger entry will be created for cost of goods sold offset by a decrease in inventory.

 

To accommodate accessories such as rocker switches and resistors, I?ve proposed a simple table called related_product. It has just two columns: product_id and related_id. Items sold as accessories would be in the version table just as any other item and could be sold separately. When a product is presented by the shopping cart, the system would also search the related_product table to see if that product_id has any related products. If it does, then those related items would also be presented to the customer as an appropriate accessory. The related product could be presented as a radio button or check box next to the accessory name. You could also make that name a clickable link to that could pull up the accessory?s own product page. With this system, an item such as a rocker switch could be an accessory for a wide range of products, plus could be a stand-alone product. It would also have its own record in the inventory table.

 

In the orders_products_attributes table, storage of products_options will become redundant. I?m not sure how that table functions, but it may become wholly redundant. The only product item necessary in the order tables will be version_id, which alone will convey exactly which item(s) was ordered.

 

The inventory system would work by first storing all items in the inventory table with a version number, quantity, total amount paid, date purchased and price per item. When a sale is made, the system would look for the oldest inventory record for that item, then decrease the quantity of item by updating the record. The system would use the cost of the item in that record to make an accounting transaction in the ledger for cost of goods sold. To preserve the inventory transaction, the entire inventory record would be copied to the close_inventory table and the quantity sold would be appended to the copied record. This provides a full audit trail of all inventory activity.

 

The pending table in the inventory system will keep track of items that have been ordered by check or money order. This provides flexibility in deciding whether or not to label items as out-of-stock if they are physically available but will be sold if and when you receive money orders.

 

Please note that the proposed schema below is conceptual in nature. More tables and some additional fields will probably be required.

 

Schema:

 

sector

sector_id

name

 

groups

group_id

sector_id

name

 

sub_group

subgroup_id

group_id

name

 

product

product_id

subgroup_id

name

creation_date

death_date

 

version

product_id

version_id

 

related_product

product_id

related_id

 

options

version_id

type

value

add_price

 

pending

version_id

qty

change_date

 

inventory

version_id

trans_id

seq_no

open_date

qty

price

open_amt

change_date

 

close_inventory

version_id

trans_id

seq_no

open_date

qty

price

open_amt

close_qty

close_trans_id

close_seq_no

Link to comment
Share on other sites

there are at least three accounting packages which tie in with osCommerce now, which have inventory, etc built in.

you can use them as examples of what needs to be done.

qb, myob, turbocash

Link to comment
Share on other sites

there are at least three accounting packages which tie in with osCommerce now, which have inventory, etc built in.

you can use them as examples of what needs to be done.

qb, myob, turbocash

 

What are these accounting packages? Can you please name them?

your source for custom hand-knotted rugs from Nepal.

A part of family of companies.

Link to comment
Share on other sites

qb = quick books

myob - manage your own money

turbocash - turbocash

 

Quickbooks inventory management seems to choke on the idea of having products with attributes (size for example). As far as I know each size basically has to be a different product, which gets logistically nightmarish quite swiftly. If anyone knows something different please share because the QB inventory management (or lack thereof) is driving my girlfriend/finance manager insane :)

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...