Forum OpenACS Development: subsite- and CR-enabled ecommerce package
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 );
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 (
on delete cascade
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,
usage_hours_p bool not null,
color text not null,
financing_p bool not null,
warranty_p bool not null,
dealer_id int not null references moto_dealer,
location_id int not null references moto_dealer_location
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.
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...
NB : this is actually a re-post, as the two last blank posts by Guan Yang apparently erased the orginial one.
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?
What might those packages be?
- recommendation system
- product reviews
- customer addresses
- credit cards
- shopping cart/orders
- gift certificates
- financial transactions
- customer service
- email notifications
- clickstream tracking