VbzCart/tables

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart
Revision as of 22:42, 3 October 2013 by Woozle (talk | contribs) (→‎Users: user_tokens)
Jump to navigation Jump to search

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_folders: where things are stored and how to access them (sort of a mini-FileFerret)
  • /cat_topic: categorization
  • /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

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...

Users

Customers can be users, but only when they've created a user account. Users can also be admins and (eventually) suppliers and vendors.

Restocking

obsolete tables

Shopping

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

Stock Management

Caching

Management

See User:Woozle/datamgr.php for the code that handles this.

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.

Miscellaneous

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.

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables&oldid=19775"