chrometweaks.org

If a website is certified by iPage.com will it for sure be a safe website?

Click Here To View All Answers...


Got a question, hope someone can answer... If a website is certified by iPage.com will it for sure be a safe website? Looking forward for any answer. My other question... 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.

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).

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.

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 it's 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..

Comments (4)

Good question... I dunno what is the right answer to your question. I'll do some Googling and get back to you if I find an anything. You should email the people at iPage as they probably could give you help..

Comment #1

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..

Comment #2

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

Comment #3

Qb = quick books.

Myob - manage your own money.

Turbocash - turbocash..

Comment #4

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..

Comment #5


This question was taken from a support group/message board and re-posted here so others can learn from it.