Difference between revisions of "VbzCart/tables"

Revision as of 01:52, 16 July 2009

Navigation

{{#lst:VbzCart|navbar}}: tables

Main Data

Catalog

These tables describe and price the items displayed in the catalog pages and in shopping carts.

A "Title" is a group of items with a common description, e.g. different sizes or styles of a shirt, different media (CD, cassette) for an audio recording.

In the previous version of the cart software, we had to have items of somewhat different appearance (e.g. longsleeve and shortsleeve shirts) sharing a single title, so as to remove the necessity to always have pictures for each. In this version, a title can point to another title for its picture, thus keeping it clear whether the picture is truly representative or just an approximation.

/cat_supp: catalog suppliers (i.e. manufacturers, wholesalers)
/cat_depts: catalog departments within a supplier
/cat_titles: titles within a department - a particular "thing" which may be available in multiple varieties
/cat_items: items within a title - a particular version of a title
/cat_ittyps: item types - every item has one, but they are often all the same
/cat_ioptns: these typically distinguish items within a title
/cat_images: image data; so far, we only keep track of images by title
/cat_ship_cost: shipping costs for different types of item
/cat_pages: mapping URLs to various catalog entities (suppliers, depts, titles)
/cat_pages_old: catalog designations sometimes change; where possible, this lets us redirect old URLs

future

It may be that Departments and Suppliers should be handled by the Topics tree, but this involves creating some infrastructure which is going to take some doing. (Specifically, we need to be able to assign named values – like "catkey" – to any topic, so that each Department topic and Supplier topic can refer to the proper catalog number.)

Catalog Entry

These are tables used to make it easier to update the catalog. The key concept here is "catalog title groups" (CTGs). A CTG is a set of titles which are all available with the same set of options (e.g. sizes) at the same prices (e.g. $10 S, $11 M-XL, $12 2XL). The final vbz catalog (cat_items) is the result of a sort of vector multiplication of Titles (cat_titles) and CTGs plus any items in stock.

A "source" is usually a printed catalog from a supplier, though it can also be the manufacturer's web site on a particular date.

See VbzCart catalog building for queries and processes.

Ordering

The items a customer wants to order are saved in a cart. The cart also saves session information, e.g. customer's IP/domain, but not the customer's shipping or payment data. Later on, we'll allow customers to make changes to carts after the cart has already been assigned to an order, so will need session info stored separately, which is why it's in a separate table. See VbzCart ordering for details.

There is a handful of tables just for managing customer contact information, so I'm putting those on a separate page: VbzCart customer tables

/core_orders
/ord_lines
/ord_pkgs
/ord_pkg_lines
/ord_event - will eventually replace and supplement ord_pull
- /ord_event_type
/ord_shipmt
/ord_trxact - order transactions
/ord_trx_type - order transaction types
/ord_pull
- /ord_pull_type - can this be migrated to ord_event_type?
/ord_msg
- /ord_msg_media
/card_auth_code - should probably be in a different section

Customers

There ought to be a better way to organize this stuff, but I haven't been able to think of one that doesn't cause worse problems. Each type of customer data has its own set of fields and its own search-optimization, and combining them results in the awkward possibility of pulling up the wrong type of data under certain circumstances. So, until something better comes along...

/core_custs - main customer records
/cust_addrs - customer mailing addresses (shipping and credit card)
/cust_cards - customer credit card numbers - sensitive data
/cust_emails - customer email addresses
/cust_names - customer names/aliases
/cust_phones - customer phone numbers
/cust_charges - credit card charges - sensitive data (includes card #s)

Restocking

/rstk_req - restock request master record (was /core_restock_new)
/rstk_req_item - catalog item in a restock request
/rstk_ord_line - list of customer orders needing those items (distribution)
/rstk_rcd - restock shipment received
/rstk_rcd_line - items received in a restock shipment
/rstk_req_item_minima - items types with minimum supplier orders

obsolete tables

/core_restocks - OBSOLETE
/rstk_lines - OBSOLETE

Shopping

These tables store data generated by the user during the shopping process.

/shop_cart
/shop_cart_line
/shop_cust_data
/shop_cust_data_type
/shop_log
/shop_client
/shop_session: to be implemented. A new session should be created whenever the "same" client doesn't remember the session cookie (could be a different computer on the same IP address), or if the session has timed out.

Stock Management

/stk_items
/stk_bins
/stk_places
/stk_history
/stk_history_legacy - for accommodating old nonconforming data
/stk_bin_history

Browsing

Topics and images

Calculated Tables

These are used for caching data which takes a long time (more than 0.3 seconds or so) to calculate and which depends on things which a lot less often than pages are viewed.

/_depts
- ← depts x suppliers – Upd_Depts_fr_Depts_Suppliers
- ← _dept_ittyps by ID_Dept – Upd_Depts_fr_DeptIttyps
/_titles
- ← titles x _depts – Upd_Titles_fr_Depts
- ← cat_items (grouped by ID_Title) – Upd_Titles_fr_CatItems
- ← _title_ittyps (grouped by ID_Title) – Upd_Titles_fr_TitleIttyps
/_title_ittyps - list of available titles for each item type
- ← cat_items (grouped by ID_Title), cat_titles – Upd_TitleIttyps_fr_CatItems_Titles
- ← _titles – Upd_TitleIttyps_fr_Titles
- ← cat_ittyps – Upd_TitleIttyps_fr_CatIttyps
/_dept_ittyps
- ← _title_ittyps – Upd_DeptIttyps_fr_TitleIttyps
- ← cat_ittyps – Upd_DeptIttyps_fr_CatIttyps
/_supplier_ittyps
- ← suppliers x _titles x _title_ittyps x cat_ittyps ... – Upd_SupplierIttyps
/_stk_containers
- ← stk_bins, cat_items... – Upd_StkContainers

Data Flow Management

These tables are all maintained by hand (except for timestamps and data_log), but must match the actual dependencies in the SQL code.

/data_tables - list of tables involved in data caching, either as a source or as a cache
/data_procs - list of stored procedures which update one table from others
/data_flow - which procs update which caches from which sources
/data_log - log of all cache updates (except those done directly via SQL commands)

Miscellaneous

/stats
/event_log
/discarded - discarded tables

@@ Line 17: / Line 17: @@
 * [[/cat_ioptns]]: these typically distinguish items within a title
 * [[/cat_images]]: image data; so far, we only keep track of images by title
-* [[/cat_ship_costs]]: shipping costs for different items
+* [[/cat_ship_cost]]: shipping costs for different types of item
 * [[/cat_pages]]: mapping URLs to various catalog entities (suppliers, depts, titles)
 * [[/cat_pages_old]]: catalog designations sometimes change; where possible, this lets us redirect old URLs
 ====future====
 It may be that Departments and Suppliers should be handled by the Topics tree, but this involves creating some infrastructure which is going to take some doing. (Specifically, we need to be able to assign named values &ndash; like "catkey" &ndash; to any topic, so that each Department topic and Supplier topic can refer to the proper catalog number.)
 ===Catalog Entry===
 These are tables used to make it easier to update the catalog. The key concept here is "catalog title groups" (CTGs). A CTG is a set of titles which are all available with the same set of options (e.g. sizes) at the same prices (e.g. $10 S, $11 M-XL, $12 2XL). The final vbz catalog (cat_items) is the result of a sort of vector multiplication of Titles (cat_titles) and CTGs plus any items in stock.