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