Forum OpenACS Development: subsite- and CR-enabled ecommerce package

Request notifications

I am developing a subsite- and content-repository-enabled ecommerce package, and since this is my first experience with the CR, I am in need of some guidance.

In my data model, there is one product table named es_product that holds the most generic attributes required for ecommerce, and it has a CR revision table named es_product_revision. Attributes specific to a product type are stored in a separate table that has a primary key referencing the generic product table.

Below are the tables for es_product, es_product_revision, and moto_vehicle. moto_vehicle stores specific attributes for vehicles, and as you can see, it's primary key is a foreign key pointing to es_product.

I am trying to figure out the best way to model moto_vehicle for the CR. Should I create a new content type with a supertype pointing to es_product, or is there a better approach?

select content_type__create_type (
       'es_product',		 -- content_type
       'content_revision',	 -- supertype,
       'ES Product',		 -- pretty_name,
       'ES Products',		 -- pretty_plural,
       'es_product_revision',	 -- table_name,
       'product_revision_id',	 -- id_column,
       null			 -- name_method

create table es_product (
       product_id             int 
                              constraint es_product_id_fk
		              references cr_items(item_id)
                              on delete cascade
			      constraint es_product_id_pk
			      primary key,
       product_type           varchar(1000) not null references acs_object_types,
       sku	              text not null,
       quantity               int not null, -- not in CR since it would cause so many entries
       package_id             int not null references apm_packages

create table es_product_revision (
       product_revision_id    int 
                              constraint es_product_revision_id_fk
		              references cr_revisions(revision_id)
			      on delete cascade
			      constraint es_product_revision_id_pk
			      primary key,
       product_description    text,
       price                  numeric(10,2),
       weight		       real, -- used to calculate shipping costs
       shipping_p             bool not null, -- shipping available?
       shipping_base          numeric(10,2), -- base shipping cost
       shipping_incr          numeric(10,2), -- incremental shipping cost
       lead_time              int, -- days it takes to ship if not in inventory
       preorder_p             bool not null, -- can customer preorder item?
       url                    text, -- external product url
       date_available         timestamptz not null default CURRENT_TIMESTAMP,
       active_p               bool not null default TRUE,
create table moto_vehicle (
       vehicle_id       integer constraint moto_vehicle_id_fk
		        references es_product(product_id)
		        on delete cascade
			constraint moto_vehicle_id_pk
			primary key,
       year	        numeric(4) not null,
       new_p	        bool not null,	      
       category_id      int not null references moto_vehicle_category,
       make_id	        int not null references moto_make,
       -- find or write a plpgsql strip fuction to query model
       model	        text not null,
       payment          numeric(10,2),
       usage	        real,
       usage_hours_p    bool not null, -- usage in hours or miles?
       color            text not null,
       engine	 	text,
       financing_p	bool not null,
       warranty_p       bool not null
       date_sold        timestamptz,
       dealer_id        int not null references moto_dealer,
       location_id 	int not null references moto_dealer_location

Posted by James Thornton on
I think I figured it out. I created a content type moto_vehicle with a supertype of es_product, and I made the moto_vehicle primary key reference es_product_revision. I see that the automatically generated views, moto_vehiclex and moto_vehiclei, include the es_product attributes.

Inside moto_vehicle__new I'll call es_product__new, and then I'll call content_revision__new, passing in the revision_id returned from es_product__new.

Did I do it right? Am I missing something?

select content_type__create_type (
      'moto_vehicle',         -- content_type
      'es_product',         -- supertype,
      'Moto Vehicle',         -- pretty_name,
      'Moto Vehicles',         -- pretty_plural,
      'moto_vehicle',         -- table_name,
      'vehicle_revision_id',     -- id_column,
      null             -- name_method

create table moto_vehicle (
      vehicle_revision_id    int
                              constraint moto_vehicle_revision_id_fk
                      references es_product_revision(product_revision_id)
                  on delete cascade
                  constraint moto_vehicle_revision_id_pk
                  primary key,
      year                  numeric(4) not null,
      new_p                  bool not null,
      category_id            int not null references es_category,
      make_id                  int not null references es_category,
      -- find or write a plpgsql strip fuction to query model
      model                  text not null,
      payment                numeric(10,2),
      usage                  real,
      usage_hours_p          bool not null,
      color                  text not null,
      engine               text,
      financing_p          bool not null,
      warranty_p            bool not null,
      date_sold              timestamptz,
      dealer_id              int not null references moto_dealer,
      location_id           int not null references moto_dealer_location

Posted by Talli Somekh on
Hey James,

Let's coordinate on this work. I also have the need for an updated ecommerce system that uses the CR and subsites and we should figure out a way to share as much work as possible.

I should hope to have some docs available relatively soon. I will see how much I can make public.


Posted by xx xx on
As far as I know there isn't a best practice for this yet. At least I didn't find any here. But since Joel is planning to "TIP a generally acceptable set of rules" for internationalization of content it would be nice if your efforts would add to general standards. Talli, are your docs ecommerce-specific or do they serve a more general "How to subsite- and CR-enable a package" goal.
Posted by Guan Yang on
Posted by Guan Yang on
Posted by Thomas Senn on

Good to read that some people are concerned by getting e-commerce package compatible with CR and subsite. Actually we also plan and need to make e-commerce package evolve to support these two ACS features. We would be pleased to participate and collaborate in such a revision/modification process.

Maybe the work could be split into as independent as possible aspects and each party could handle one of the aspects, hopefully putting them altogether at the end. Furthermore, it seems to us that e-commerce could require a complete re-writing, especially to achieve a better separation of the logics/programming side of a page (tcl) from its layout/aspect (adp); there are effectively numerous places where html code is produced inside tcl procs (a nightmare for designers indeed), when not getting some bits of it from the database...

Best regards.

NB : this is actually a re-post, as the two last blank posts by Guan Yang apparently erased the orginial one.

Posted by Jade Rubick on
I don't use ecommerce, but I've really been impressed with the approach taken with calendar:

Yes, everyone agreed it needed to be rewritten, but Dirk has been implementing the changes in small, easily obtainable chunks. I'd hate to see someone undertake such a large project and not completely finish it, or lose functionality. Then we'd be stuck with two ecommerce packages we don't like.

Instead, why don't we gradually fix it?

Posted by Torben Brosten on
I agree with Bart's previous suggestion that ecommerce really needs to be split into a few separate packages.

What might those packages be?

bill-of-materials (products)
payment-gateway (exists)
shipping-gateway (exists)


Posted by James Thornton on
I have been pulling apart the current data model, and I have separated it into the following sections:

- stores
- categories
- images
- products
- specials/sales/offers
- recommendation system
- product reviews
- customer addresses
- credit cards
- shopping cart/orders
- gift certificates
- shipping
- returns
- financial transactions
- customer service
- email notifications
- problems
- clickstream tracking
- administration

Posted by Caroline Meeks on
What is your current status with this work?  We are bidding on a project with a major ecommerce component and we would be interested in collaborating.