Custom Ecommerce

[autocrat]

Okay folks... I'm currently using a CMS system for the vast majority of the sites I build.
I have a simple setup for Ecommerce and use the PayPal cart for Cart and Payment.

The issue is, two of my Clients are doing rather well, an dare considering go up a level or two in the ecommerce game. They have both been sweet and stated they'd rather wait a bit and let me generate something for them rather than going else where.

Now, I have the functionality to create Custom tables/db/dbs that can be easily integrated into the site and CMS... but I'm not 100% certain that I am thinking things through thoroughly enough.

So if I oultine my thoughts, I'd appreciate and welcome comments, suggetsio9ns, hint's tips and even criticisms...(to make it clear - no, I don't want to use an existing system, I want to make it integral with my cms sites - no third part stuff okay).

So, as far as I can figure, taking a rather simplistic view of things...

Products, Shipping and Payment.
This is not counting on having User or Wholesaler accoutns etc. (hink that part is easier :) ).

Products
Now, rather than making it overly simple, I need to consider things such as Stock etc.
This is a little confusing at first, as you could be selling tyres... you not only need the item information; such as name, price etc... but Options such as Dimensions, colours etc.
So, I'm thinking of a table that permits two main types of entries... Product Type and Product Options... sort of Parent with Child/Children.
Logic suggestions not only having the ability to state whether Parent or Child... but if Child, to tell it what the Parent is (otherwise thing would get confusing).

The other issue is how many options ??? some products may only have variants on a single aspect... size or colour.... others may have numerous options, size and colour and material etc.
How to handle that?

Additional ideas including being able to have "related items" (those of similar nature... should be handled by Categories and Sub-Categories etc... and "component items"... such as batteris for that type of remote control car
Further... should Products be able to Cross-Categories - some items may actually be in two or more Cats... so I would need to beable to permit multiple entires
???

Categories
This at first appears simple.... but how to distinguish between Parent, child, grandchild etc. categories? How far can you go - how far should you go.

Should I make this a combined table with the products... adding four options, Parent Cat, Child Cat, Parent Prod and Child Prod... or should I keep them as two seperate Tables?

Shipping
This is the one that is killing me.
I can see numerous different methods for the prior sections - and several are more than viable...
But shipping is a night mare.
The UK's leading delivery is PArcel Force... and they now charge based not only on wight, but on Dimensions(H/W/D) too - so shipping charges are crippling many businesses - so it's important to get this right.

I need to be able to calculate shipping in several different manners...
Per item...
Per first item and then per additional item
Per total weight
Per total Sum of money
By Dimension
A mix of any of the above...
PLUS- different destinations... UK-mainland, UK-non-mainland, Western/Eastern Europe, other parts of the world that usually cost a sodding fortune :)

So I need to beable to permit the clients to permit the users the different choices.... which some how I need to have a name/id for each choice, then a system for calculating the costs.

For some reason this is escaping me...
I've already decided to have cascading overides in place.... in the product you can specific Per Item, Per first and additional etc... (these should also be calculated on weight etc.)...
if these aren't selected, then it could default to the site defined (rather than item defined) system/s...
but how can I create a table that would permit all the various possibilites ?

OR do I simplify the whole procies... permit Item based or Site based... setup up destination options, and have a single table with references to destination and charge ranges based on weight/dimension?
i.e.
UK - < 1 kg - < 0.5 sq M = £1.50
UK - < 1 kg - 0.5 to 1 sq M = £2.50
UK - < 1 kg - 1 to 2 sq M = £3.50
UK - 1 to 2 kg - <0.5 sq M = £2.50
UK - 1 to 2 kg - 0.5 to 1 sq M = £4.50
UK - 1 to 2 kg - 1 to 2 sq M = £6.50
etc...

? Am I making it to difficult ?

A simple bit of could will help total Item based seperately from site based then add the two together to get the total...

Of couse, the last is payment providers.
I need to find at least one strong alternative to PayPal ( after their recent screw up with JS on the billing page for certain CC's - one cliet lost over an estimated £600 due to people not being able to pay and going else where!)...

But who to use?

So - if you have followed all of that, don't think I'm to much of a moron, lunatic or over reaching - please lend some insight and support.

Much obliged,
auto

[expertentity]

Its really difficult to understand.

[fionix]

Indeed it is, I do not understand exactly what you asking for.

Would it be possible to shorten your question ?

[autocrat]

Well, not really :)

The problem with small details is that it leaves things uncovered,whichleadsto either assumptions or misunderstandings...

Still, the gyst of it is as follows;

There are many different ways to construct an Ecommore system, ranging from "lite" and simple through to complexand permiitting many variants of Products and options.

Yet there should be a way to seperate out the different aspects of an ecommerce system, and modulise it...
Thus you haev Cart, Products, Categories, Shipping, Checkout etc.
You could also hae Customers, Suppliers, Orders/Cancellations, Related Products, Recommended products etc. too.

All of these should be seperate from each other, yet be able to relate...
.. and ...
...be "expandable" - meaning that you can add more data/details/info to it as required (thus Products could be as simple as
ID/Name/Price...
or as complicated as
ID/Name/Type/Option1/Option2/Price...
or even...
ID/Name/MainType/SubType/Option1/Price-for-Type+Price-for-Option1 etc...

Does that make more sense?

Basically, I'm asking what others think about ecommerce in general, and how they would tackle such a thing - whether they can foresee modulisation as being sensible/workable, or if it all ahsto be more closely tied together etc.

[navin2004]

Autocrat,

This message is a whole 2 months old and I have not see anybody respond yet.

I am kind of doing the same that you are doing, but I work as a Data Architect for a large company in Canada and am doing an eCommerce like site's Database design.

The big difference is that this site if for internal use by employees of the company I work for. But the Product catalogue will be populated with products from external vendors. My company is just creating the site as part of a rewards and recognition system where employees can spend there points to buy items from the catalogue.

What I have done is that I have a CATALOGUE_ITEM as a super type (not sure if you understand this data modeling terminology) which is sub-typed into PRODUCT(something tangible) and SERVICE(non-tangible). I then have a child table to CATALOGUE_ITEM called CATALOGUE_ITEM_CHARACTERISTICS. This is where I will record all the "characteristics" about a CI. This is where I was having issues explaining my design to others and this is where your eCommerce site will differ.

But for your purposes, what you should build is then a table called CHARACTERISITC_TYPE (such as Color, Gender etc) and CHARACTERISTIC_TYPE_VALUE. This second table is where you will list all the valid values for a CHARACTERISTIC_TYPE. Then build an intersection table for these two called ALLOWED_CHARACTERISITC_TYPE_CHARACTERISTIC_VALUE
This will be where you define valid combinations of Type's and Value's. For instance a type of Gender does not make sense to have a value of Red, Blue, Green etc, but Male, Female...

These two tables and the intersection table is what then you will give the end user and admin screen for which they can add any values to.

The catch is that things such as Weight, Length, Width are not CHARACTERISITC. I will create them as columns on PRODUCT as they are really specs of a PRODUCT, but not really variations in which the PRODUCT is available. This concept is debatable and this is where I was having lot of discussion with my colleagues.
The best would to be look at what eBay does as they really sell both PRODUCTS and SERVICES, unlike Amazon which sells only PRODUCTS.
At the end of each PRODUCT detail page on Amazon you will Product details, which always contains things such as Dimension, Weight, Shipping special details and some kind of ASIN number.

Hope this helps and do let us know what you ended up designing your database like.

Well, not really :)

The problem with small details is that it leaves things uncovered,whichleadsto either assumptions or misunderstandings...

Still, the gyst of it is as follows;

There are many different ways to construct an Ecommore system, ranging from "lite" and simple through to complexand permiitting many variants of Products and options.

Yet there should be a way to seperate out the different aspects of an ecommerce system, and modulise it...
Thus you haev Cart, Products, Categories, Shipping, Checkout etc.
You could also hae Customers, Suppliers, Orders/Cancellations, Related Products, Recommended products etc. too.

All of these should be seperate from each other, yet be able to relate...
.. and ...
...be "expandable" - meaning that you can add more data/details/info to it as required (thus Products could be as simple as
ID/Name/Price...
or as complicated as
ID/Name/Type/Option1/Option2/Price...
or even...
ID/Name/MainType/SubType/Option1/Price-for-Type+Price-for-Option1 etc...

Does that make more sense?

Basically, I'm asking what others think about ecommerce in general, and how they would tackle such a thing - whether they can foresee modulisation as being sensible/workable, or if it all ahsto be more closely tied together etc.

[peterk]

What you described we call the ERP - Enterprise Resources Planning system where e-commerce is a separate but interrelated module with other modules, such as "Manufacturing", "Accounting", "Catalog management", "Order Management", "Promotion/Marketing", etc.

The most obvious vendors are Oracle, SAP, IBM. And a typical cost of implementation is in hundreds of thousands.

We have a similar solution for you, but with a different price tag. BTW, it has a DATABASE.

Please send me PM if you need more information.

[ayni]

Fantastic post,
Great job keep it up.

[shaniadavid]

to much lengthy post and really hard to read...