Re: Shopping cart - Mailing list pgsql-general

On Wednesday 22 October 2008 18:39:53 Jonathan Bond-Caron wrote:
>
> If there's no time constraints, I'd do as someone mentioned and build it
> yourself. Open source or even commercial e-commerce solutions tend to get
> very complicated in trying to be 'flexible'.
>
> i.e.
> CREATE TABLE shopping_carts
> (
>   carts_id serial NOT NULL,
>   carts_date_created timestamp without time zone NOT NULL,
>   carts_date_updated timestamp without time zone NOT NULL,
>   carts_subtotal numeric(9,4) NOT NULL,
>   carts_total numeric(9,4) NOT NULL,
>   carts_tax1 numeric(9,4),
>   carts_tax2 numeric(9,4),
>   CONSTRAINT shopping_carts_pkey PRIMARY KEY (carts_id)
> )
> WITHOUT OIDS;
>
> CREATE TABLE shopping_carts_products
> (
>   products_id serial NOT NULL,
>   products_code character varying(64) NOT NULL,
>   products_name character varying(100) NOT NULL,
>   products_description character varying(1024),
>   products_date_added timestamp without time zone NOT NULL,
>   products_date_modified timestamp without time zone NOT NULL,
>   products_taxable boolean NOT NULL DEFAULT true,
>   products_qty double precision NOT NULL,
>   products_price numeric(9,5) NOT NULL,
>   shopping_carts_id integer NOT NULL,
>   shopping_carts_pos smallint NOT NULL,
>   CONSTRAINT shopping_carts_products_pkey PRIMARY KEY (products_id),
>   CONSTRAINT shopping_carts_products_shopping_carts_id_fkey FOREIGN KEY
> (shopping_carts_id)
>       REFERENCES shopping_carts (carts_id) MATCH SIMPLE
>       ON UPDATE CASCADE ON DELETE CASCADE,
>   CONSTRAINT shopping_carts_products_products_code_key UNIQUE
> (products_code)
> )
> WITHOUT OIDS;
>
> CREATE TABLE products
> (
>   products_id serial NOT NULL,
>   products_code character varying(64) NOT NULL,
>   products_name character varying(100) NOT NULL,
>   products_description character varying(1024),
>   products_status int,
> ...
> )
> WITHOUT OIDS;
>
> Displaying products and adding to a shopping cart is not much work, but the
> product *management* and analytics / reporting will eat your time.
>
> Best of luck!

I agree with Jonathan and Grzegorz , you learn a lot doing it yourself and can
have all (and just) the functionality you need.

Cart system we built with linux / Pg / ColdFusion / Apache has a bit more
complex table structure than the above. Here's for the products (not in full
but to give an idea).

maincategories:
maincategory_id SERIAL PRIMARY KEY NOT NULL
mc_maincategory_name text NOT NULL
mc_maincategory_descr text
mc_maincategory_pic text
mc_published boolean
...

categories:
category_id SERIAL PRIMARY KEY NOT NULL
c_maincategory_id integer
c_category_name text NOT NULL
c_category_descr text
c_category_pic text
c_published boolean
...
"categories_c_maincategory_id_fkey" FOREIGN KEY (c_maincategory_id) REFERENCES
maincategories(maincategory_id) ON UPDATE CASCADE ON DELETE RESTRICT

subcategories:
subcategory_id SERIAL PRIMARY KEY NOT NULL
sc_maincategory_id integer
sc_category_id integer
sc_subcategory_name text NOT NULL
sc_subcategory_descr text
sc_subcategory_pic text
sc_published boolean
...
 "subcategories_sc_maincategory_id_fkey" FOREIGN KEY (sc_maincategory_id)
REFERENCES maincategories(maincategory_id) ON UPDATE CASCADE ON DELETE
RESTRICT
    "subcategories_sc_category_id_fkey" FOREIGN KEY (sc_category_id)
REFERENCES categories(category_id) ON UPDATE CASCADE ON DELETE RESTRICT

products:
product_id  SERIAL PRIMARY KEY NOT NULL
p_maincategory_id integer NOT NULL
p_category_id integer NOT NULL
p_subcategory_id integer
p_manufacturer_id integer NOT NULL
p_supplier_id integer NOT NULL
p_product_name text NOT NULL
p_product_code text NOT NULL
p_product_descr text
p_product_pic text
p_published boolean
...
 "products_p_category_id_fkey" FOREIGN KEY (p_category_id) REFERENCES
categories(category_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "products_p_subcategory_id_fkey" FOREIGN KEY (p_subcategory_id) REFERENCES
subcategories(subcategory_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "products_p_manufacturer_id_fkey" FOREIGN KEY (p_manufacturer_id)
REFERENCES manufacturers(manufacturer_id) ON UPDATE CASCADE ON DELETE RESTRICT
"products_p_supplier_id_fkey" FOREIGN KEY (p_supplier_id) REFERENCES
suppliers(supplier_id) ON UPDATE CASCADE ON DELETE RESTRICT

This way you can sort and display the products by maincats, cats and subcats
in the actual webshop. A product must belong to a main category and a category
under that main category, sub category under a category is optional. You would
of course have much more info in the products table: taxes, added, modified,
stock amount, next shipment, and what have you ...

Then add tables for orders, order rows, customers, delivery methods, payment
methods, suppliers, manufacturers, order statuses, taxes, maybe temp tables
for automatic updates from suppliers ...

As Jonathan said, the trick is not in getting the shop online but in the
management side of it all. The public shop interface is in fact only a small
proportion of the system.

Anyway, we did not use cart tables. The web application stores cart
information in session cookies until the point the order is finished and
approved and is then written to customers, orders and order rows. Two cookies,
or a cookie pair, one for the product id and one for the amounts. E.g. a
cookie for products "1,234,3472,555" and a cookie for amounts "2,1,1,3" means
you have 2 pcs of product id 1, 1 pcs product id 234 and so on.

And as mentioned, you have the freedom to choose your preferred API, be it
php, python, perl or what ever.

Please (Andrus) have a look at the shop, LinuxShop, in action at

http://www.linuxkauppa.fi/

It is in finnish but I think you will get the hang of it with no problems.
LinuxPood / LinuxKauplus, a web shop for linux compatible hardware.

With very best regards,

Aarni

--

Burglars usually come in through your windows.