Difference between revisions of "VbzCart/tables"
(→Ordering: event table names updated) |
(→Event Logging: more obsolete tables, so I can close the tabs) |
||
(37 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | == | + | ==Current Tables== |
− | |||
− | |||
− | |||
===Catalog=== | ===Catalog=== | ||
These tables describe and price the items displayed in the catalog pages and in shopping carts. | These tables describe and price the items displayed in the catalog pages and in shopping carts. | ||
Line 16: | Line 13: | ||
* [[/cat_ittyps]]: item types - every item has one, but they are often all the same | * [[/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_ioptns]]: these typically distinguish items within a title | ||
− | * [[/ | + | * [[/cat_topic]]: categorization |
+ | ** [[/cat_title_x_topic]]: titles for each topic | ||
* [[/cat_ship_cost]]: shipping costs for different types of item | * [[/cat_ship_cost]]: shipping costs for different types of item | ||
* [[/cat_pages]]: mapping URLs to various catalog entities (suppliers, depts, titles) | * [[/cat_pages]]: mapping URLs to various catalog entities (suppliers, depts, titles) | ||
Line 24: | Line 22: | ||
===Catalog Entry=== | ===Catalog Entry=== | ||
− | These are tables used to make it easier to update the catalog | + | These are tables used to make it easier to update the {{l/vc/piece|catalog/local|local catalog}}; see {{l/vc/piece|catalog/supplier}} and {{l/vc/piece|catalog/building}}. |
− | + | * <s>[[/ctg_supp]]</s> - deprecated | |
− | |||
− | |||
− | |||
* [[/ctg_prc_funcs]] | * [[/ctg_prc_funcs]] | ||
* [[/ctg_sources]] | * [[/ctg_sources]] | ||
Line 37: | Line 32: | ||
* [[/ctg_upd1]] | * [[/ctg_upd1]] | ||
* [[/ctg_upd2]] | * [[/ctg_upd2]] | ||
− | + | ===File Management=== | |
+ | * [[/fm_node]]: a filesystem entity (could be either file or folder) | ||
+ | * [[/fm_file]]: files of any type | ||
+ | * [[/fm_folder]]: places where files can be stored | ||
+ | * [[/cat_images]]: files that are images, with additional metadata | ||
===Ordering=== | ===Ordering=== | ||
− | + | Cart data and line-items are converted to orders and order lines when the customer confirms the order. Orders become the focal point for a lot of other activity, such as shipping, transactions, and tracking of status changes. | |
− | |||
− | |||
− | * [[/ | + | * [[/orders]] |
− | * [[/ord_lines]] | + | ** [[/ord_lines]] |
− | * [[/ord_pkgs]] | + | ** [[/ord_pkgs]] |
− | * [[/ord_pkg_lines]] | + | ** [[/ord_pkg_lines]] |
− | * [[/ord_shipmt]] | + | ** [[/ord_shipmt]] |
− | * [[/ord_trxact]] - order transactions | + | ** [[/ord_trxact]] - order transactions |
− | * [[/ord_trx_type]] - order transaction types | + | ** [[/ord_trx_type]] - order transaction types |
− | * [[/ | + | ** [[/ord_msg]] |
− | * [[/ | + | *** [[/ord_msg_media]] |
− | ** [[ | + | ** ''see also [[#Event Logging]]'' |
− | + | * [[/card_auth_code]] - reference table for payment status codes; this should probably be a hard-coded array, not a table | |
− | *** [[/ | + | * '''obsolete''': |
− | * | + | ** [[/ord_pull]] |
− | ** [[/ | + | *** [[/ord_pull_type]] |
− | * [[/ | + | ** [[/ord_event]] - internal events not generally affecting order status |
===Customers=== | ===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... | 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... | ||
− | * [[/ | + | * [[/cust]] - main customer records |
− | * [[/ | + | * [[/cust_addr]] - customer mailing addresses (shipping and credit card) |
* [[/cust_cards]] - customer credit card numbers - '''sensitive data''' | * [[/cust_cards]] - customer credit card numbers - '''sensitive data''' | ||
* [[/cust_emails]] - customer email addresses | * [[/cust_emails]] - customer email addresses | ||
− | * [[/cust_names]] - customer names/aliases | + | * <s>[[/cust_names]] - customer names/aliases</s> - deprecated |
* [[/cust_phones]] - customer phone numbers | * [[/cust_phones]] - customer phone numbers | ||
* [[/cust_charges]] - credit card charges - '''sensitive data''' (includes card #s) | * [[/cust_charges]] - credit card charges - '''sensitive data''' (includes card #s) | ||
+ | |||
+ | ===Users=== | ||
+ | Customers can be users, but only when they've created a user account. Users can also be admins and (eventually) suppliers and vendors. | ||
+ | * [[/user]] | ||
+ | ** [[/user_tokens]] - for emailing password-reset links | ||
+ | ** [[/user_client]] | ||
+ | ** [[/user_session]] | ||
+ | ** '''access control''': | ||
+ | *** [[/ugroup]] - groups to which users may belong | ||
+ | *** [[/uperm]] - permissions a group may have | ||
+ | *** [[/user_x_ugroup]] - groups to which each user belongs | ||
+ | *** [[/ugroup_x_uperm]] - permissions each group has | ||
===Restocking=== | ===Restocking=== | ||
Line 77: | Line 86: | ||
* [[/rstk_rcd_line]] - items received in a restock shipment | * [[/rstk_rcd_line]] - items received in a restock shipment | ||
* [[/rstk_req_item_minima]] - items types with minimum supplier orders | * [[/rstk_req_item_minima]] - items types with minimum supplier orders | ||
− | |||
− | |||
− | |||
===Shopping=== | ===Shopping=== | ||
These tables store data generated by the user during the shopping process. | These tables store data generated by the user during the shopping process. | ||
Line 85: | Line 91: | ||
** [[/shop_cart_line]] | ** [[/shop_cart_line]] | ||
** [[/shop_cart_event]] | ** [[/shop_cart_event]] | ||
− | ** [[/ | + | ** [[/cart_data]] |
− | ** | + | ** [[/shop_cart_data]] - OBSOLETE |
− | |||
− | |||
− | |||
===Stock Management=== | ===Stock Management=== | ||
− | * [[/ | + | * [[/stk_lines]] |
* [[/stk_bins]] | * [[/stk_bins]] | ||
+ | ** [[/stk_bin_history]] | ||
* [[/stk_places]] | * [[/stk_places]] | ||
* [[/stk_history]] | * [[/stk_history]] | ||
− | * [[/ | + | * [[/stk_whse]]: warehouses |
− | |||
− | === | + | ===Caching=== |
− | + | ====Management==== | |
− | * [[/ | + | See [[User:Woozle/datamgr.php]] for the code that handles this. |
− | * [[/ | + | * [[/cache_tables]] - list of tables involved in data caching, either as a source or as a cache |
+ | * [[/cache_queries]] - list of [[../procs|stored procedures]] which update one table from others | ||
+ | * [[/cache_flow]] - which queries update which caches from which sources | ||
+ | * [[/cache_log]] - log of all cache updates done within cache management | ||
− | ==Calculated Tables== | + | ====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. | + | 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 change a lot less often than pages are viewed. |
* [[/_depts]] | * [[/_depts]] | ||
** ← depts x suppliers – {{vbzcart|proc|Upd_Depts_fr_Depts_Suppliers}} | ** ← depts x suppliers – {{vbzcart|proc|Upd_Depts_fr_Depts_Suppliers}} | ||
** ← _dept_ittyps by ID_Dept – {{vbzcart|proc|Upd_Depts_fr_DeptIttyps}} | ** ← _dept_ittyps by ID_Dept – {{vbzcart|proc|Upd_Depts_fr_DeptIttyps}} | ||
* [[/_titles]] | * [[/_titles]] | ||
− | |||
− | |||
− | |||
* [[/_title_ittyps]] - list of available titles for each item type | * [[/_title_ittyps]] - list of available titles for each item type | ||
** ← cat_items (grouped by ID_Title), cat_titles – {{vbzcart|proc|Upd_TitleIttyps_fr_CatItems_Titles}} | ** ← cat_items (grouped by ID_Title), cat_titles – {{vbzcart|proc|Upd_TitleIttyps_fr_CatItems_Titles}} | ||
Line 124: | Line 127: | ||
* [[/_stk_containers]] | * [[/_stk_containers]] | ||
** ← stk_bins, cat_items... – {{vbzcart|proc|Upd_StkContainers}} | ** ← stk_bins, cat_items... – {{vbzcart|proc|Upd_StkContainers}} | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | ==Miscellaneous== | + | ===Event Logging=== |
+ | * {{l/ferreteria}} tables: | ||
+ | ** {{l/ferreteria/table|event}}: EventPlex core | ||
+ | ** {{l/ferreteria/table|event_done}} | ||
+ | ** {{l/ferreteria/table|event_in_table}} | ||
+ | ** {{l/ferreteria/table|event_notes}} | ||
+ | * Custom EventPlex extensions | ||
+ | ** [[/event_vc_legacy]] - old log data | ||
+ | ** [[/event_vc_bin]] - stock bin log | ||
+ | ** [[/event_vc_ord_hold]] - order status log | ||
+ | *** [[/ord_hold_type]] | ||
+ | ** to be converted: | ||
+ | *** [[/stk_history]] | ||
+ | * obsolete: | ||
+ | ** [[/event_log]] - now renamed <code>OLD event_log</code>, soon to be removed | ||
+ | ** [[/shop_cart_event]] - now renamed <code>OLD shop_cart_event</code>, to be removed after checking that data has been migrated | ||
+ | ** [[/ord_pull]] | ||
+ | *** [[/ord_pull/migration]] | ||
+ | ** [[/shop_cart_event]] | ||
+ | |||
+ | ===Miscellaneous=== | ||
+ | * [[/ref_country]] - list of countries | ||
+ | ** [[/ref_country_alias]] - list of possible country name spellings | ||
* [[/var_global]] | * [[/var_global]] | ||
− | * [[/ | + | * {{l/vc|archive/tables}} - discarded tables |
− | + | ||
+ | ==Revisions Under Consideration== | ||
+ | ===2011-09-26=== | ||
+ | The [[/cat_depts]] table is seeming increasingly useless and an obstacle to good data design. Suppliers are prone to assigning items to different departments every year as their offerings change, and few or none of them use departments as part of their catalog key. In cases where they do, the amount of time saved by not having to key in 2-4 extra characters per catalog number simply isn't worth the extra maintenance and coding to make this work reliably. | ||
+ | |||
+ | The function of the cat_depts table can be replaced by the [[/brs_topics]] table (which should be renamed '''cat_topics'''). | ||
+ | |||
+ | Once you start thinking about life after cat_depts, however, one has to ask whether [[/cat_supp]] should also be rolled into brs_topics. Obviously suppliers do, unlike departments, have some data significance: suppliers have catalogs, catalog groups, price functions, restock history... most of which data goes in other tables, not cat_supp itself, so it wouldn't be hard to use topics for suppliers too. | ||
+ | |||
+ | That would make it easier to allow the same item to be purchasable from multiple suppliers -- but how do you handle catalog numbers then? The same item can have different catalog numbers depending on where it was ordered from? Possibly this makes sense, but it still would cause trouble: each item still only has one item record, and that record needs a catalog number. Do you then move catalog numbers into a separate table that records item-to-supplier relationships? | ||
+ | |||
+ | Is there any stopping point, short of "going all the way", that seems sensible? Can't we just eliminate departments for now, and worry about further enhancements later after mopping up all the code changes that that alone will require? That's probably a sensible compromise. |
Latest revision as of 13:47, 3 March 2020
Current Tables
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_topic: categorization
- /cat_title_x_topic: titles for each topic
- /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 local catalog; see catalog/supplier and catalog/building.
/ctg_supp- deprecated- /ctg_prc_funcs
- /ctg_sources
- /ctg_groups
- /ctg_titles
- /ctg_items
- /ctg_upd1
- /ctg_upd2
File Management
- /fm_node: a filesystem entity (could be either file or folder)
- /fm_file: files of any type
- /fm_folder: places where files can be stored
- /cat_images: files that are images, with additional metadata
Ordering
Cart data and line-items are converted to orders and order lines when the customer confirms the order. Orders become the focal point for a lot of other activity, such as shipping, transactions, and tracking of status changes.
- /orders
- /ord_lines
- /ord_pkgs
- /ord_pkg_lines
- /ord_shipmt
- /ord_trxact - order transactions
- /ord_trx_type - order transaction types
- /ord_msg
- see also #Event Logging
- /card_auth_code - reference table for payment status codes; this should probably be a hard-coded array, not a table
- obsolete:
- /ord_pull
- /ord_event - internal events not generally affecting order status
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...
- /cust - main customer records
- /cust_addr - 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- deprecated- /cust_phones - customer phone numbers
- /cust_charges - credit card charges - sensitive data (includes card #s)
Users
Customers can be users, but only when they've created a user account. Users can also be admins and (eventually) suppliers and vendors.
- /user
- /user_tokens - for emailing password-reset links
- /user_client
- /user_session
- access control:
- /ugroup - groups to which users may belong
- /uperm - permissions a group may have
- /user_x_ugroup - groups to which each user belongs
- /ugroup_x_uperm - permissions each group has
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
Shopping
These tables store data generated by the user during the shopping process.
Stock Management
- /stk_lines
- /stk_bins
- /stk_places
- /stk_history
- /stk_whse: warehouses
Caching
Management
See User:Woozle/datamgr.php for the code that handles this.
- /cache_tables - list of tables involved in data caching, either as a source or as a cache
- /cache_queries - list of stored procedures which update one table from others
- /cache_flow - which queries update which caches from which sources
- /cache_log - log of all cache updates done within cache management
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 change 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
- /_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
Event Logging
- Ferreteria tables:
- event: EventPlex core
- event_done
- event_in_table
- event_notes
- Custom EventPlex extensions
- /event_vc_legacy - old log data
- /event_vc_bin - stock bin log
- /event_vc_ord_hold - order status log
- to be converted:
- obsolete:
- /event_log - now renamed
OLD event_log
, soon to be removed - /shop_cart_event - now renamed
OLD shop_cart_event
, to be removed after checking that data has been migrated - /ord_pull
- /shop_cart_event
- /event_log - now renamed
Miscellaneous
- /ref_country - list of countries
- /ref_country_alias - list of possible country name spellings
- /var_global
- archive/tables - discarded tables
Revisions Under Consideration
2011-09-26
The /cat_depts table is seeming increasingly useless and an obstacle to good data design. Suppliers are prone to assigning items to different departments every year as their offerings change, and few or none of them use departments as part of their catalog key. In cases where they do, the amount of time saved by not having to key in 2-4 extra characters per catalog number simply isn't worth the extra maintenance and coding to make this work reliably.
The function of the cat_depts table can be replaced by the /brs_topics table (which should be renamed cat_topics).
Once you start thinking about life after cat_depts, however, one has to ask whether /cat_supp should also be rolled into brs_topics. Obviously suppliers do, unlike departments, have some data significance: suppliers have catalogs, catalog groups, price functions, restock history... most of which data goes in other tables, not cat_supp itself, so it wouldn't be hard to use topics for suppliers too.
That would make it easier to allow the same item to be purchasable from multiple suppliers -- but how do you handle catalog numbers then? The same item can have different catalog numbers depending on where it was ordered from? Possibly this makes sense, but it still would cause trouble: each item still only has one item record, and that record needs a catalog number. Do you then move catalog numbers into a separate table that records item-to-supplier relationships?
Is there any stopping point, short of "going all the way", that seems sensible? Can't we just eliminate departments for now, and worry about further enhancements later after mopping up all the code changes that that alone will require? That's probably a sensible compromise.