|
|
| Line 6: |
Line 6: |
|
| |
|
| I will be taking notes on this page and gradually refining them into something resembling documentation, with the ultimate goal of producing a system which could be released under an [[htwiki:FOSS|open source]] license. | | I will be taking notes on this page and gradually refining them into something resembling documentation, with the ultimate goal of producing a system which could be released under an [[htwiki:FOSS|open source]] license. |
| ==Tables==
| |
| *"#" indicates Primary Key fields
| |
| *"@" indicates autonumbered fields
| |
| ===Administration===
| |
| ====Main data tables====
| |
| *'''admin_users''' -- users with access to the admin system
| |
| **'''ID'''#@ - int(4)
| |
| **'''Name''' - varchar(32)
| |
| **'''Email''' - varchar(128)
| |
| *'''admin_groups''' -- each group has a role to play, and each role requires a particular set of privileges
| |
| **'''ID'''#@ - int(4)
| |
| **'''Name''' - varchar(32)
| |
| **'''Descr''' - text -- text describing the purpose of this group
| |
| *'''admin_privs''' -- particular privileges; meaning is defined in code
| |
| **'''ID'''#@ - int(4)
| |
| **'''Name''' - varchar(32)
| |
| **'''Descr''' - text -- text describing this permission
| |
| ====Collection/link tables====
| |
| *'''admin_user_groups''' -- users in each group / groups to which each user belongs
| |
| **'''ID_User'''# - int(4)
| |
| **'''ID_Group'''# - int(4)
| |
| *'''admin_group_privs''' -- privileges each group has / groups having a particular privilege
| |
| **'''ID_Group'''# - int(4)
| |
| **'''ID_Priv'''# - int(4)
| |
| ===Catalog===
| |
| These tables describe the items available for sale and correctly describe and price items added to the shopping cart.
| |
|
| |
|
| 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.
| | ==Documents== |
| | *[[VbzCart Tables]] |
| | ==Notes== |
| | ===Identifying Sessions=== |
| | I've been debating about the idea of allowing carts to work using IP address/domain plus browser ID (user_agent) because cookies don't always seem to work (and some people have them turned off), and I think I've come up with a reasonable compromise. |
|
| |
|
| 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.
| | The problem is that one user might connect through a dial-up (dynamic IP), place orders in a cart and disconnect, and then another user with the same browser and OS might happen to connect through the same dial-up -- which would cause that user to be assigned the previous user's shopping cart. This isn't a ''serious'' problem, because no personal information will be conveyed, but it can be off-putting to go to a store and find that your cart already has items in it. I would be inclined to immediately go somewhere else. |
|
| |
|
| *'''cat_suppliers''' -- catalog suppliers (i.e. manufacturers, wholesalers)
| | What I've come up with is the following: |
| **'''ID'''#@ - int(4) | | *sessions IDed via IP/browser have a much shorter expiration than cookie sessions |
| **'''Name''' - text(64) -- supplier's name | | *sessions IDed via IP/browser will show a warning to indicate the shorter expiration time (should show the actual session time remaining) |
| **'''CatKey''' - text(8) -- supplier's identifying characters in catalog numbers | | *cookies have never been used for checkout; we have always used POST data (?session=xxxxx), so that's not an issue |
| **'''MinCostPerOrd''' - currency -- supplier's minimum order, dollar amount
| |
| **'''MinQtyPerDesign''' - int(4) -- supplier's default minimum order per design (can be overridden for specific Titles)
| |
| :'''Note''': other contact data should probably be wiki-based. We'll have a namespace for vbz catalog data, probably "vbzcat:", and supplier information will be stored in pages named something like "vbzcat:lb.page", "vbzcat:lb.address", "vbzcat:lb.phone", and so on. (MediaWiki 1.5 will, if I understand right, allow regular non-template pages to be used as templates -- so we can have one page showing all the contact info together without having to enter it twice.)
| |
|
| |
|
| *'''cat_titles''' -- catalog titles
| | ===Sequence of Events=== |
| **'''ID'''#@ - int(4)
| | *On receiving one or more items to add to the cart: |
| **'''ID_Supplier''' - int(4) -- cat_suppliers.ID
| | **Try to match with existing session: |
| **'''ID_Similar''' - int(4) -- cat_titles.ID of a similar title whose image can be used, if this title lacks one | | ***Check for session cookie; if found, use that |
| **'''Name''' - varchar(128) -- name of title, usually according to supplier (not strict)
| | ***If no session cookie, look for unexpired IP/browser match |
| **'''CatKey''' - varchar(32) -- must be unique within supplier
| | **If no usable existing session found, create a new one (empty cart) |
| :'''Note''': I was also going to include a "Descr" (descriptive text) field, but I'm inclined to think that should be wiki-based too (like "vbzcat:lb-00000.descr").
| | **Add items to the session cart |
| | | **Display cart & session info |
| *'''cat_items''' -- catalog items | |
| **'''ID'''#@ - int(4) | |
| **'''CatNum''' - varchar(32) -- catalog number: must be unique, but may change
| |
| **'''ID_Title''' - int(4) -- cat_titles.ID
| |
| **'''ID_IType''' - int(4) -- cat_itypes.ID: type of item
| |
| **'''ID_IOptn''' - int(4) -- cat_ioptns.ID: item option (e.g. size)
| |
| **'''ID_ShipCost''' - int(4) -- applicable shipping cost calculation data | |
| **'''Price''' - currency -- price to customer | |
| | |
| *'''cat_itypes''' -- catalog item types
| |
| **'''ID'''#@ - int(4)
| |
| **'''ID_Parent''' - int(4) -- cat_itemtypes.ID of parent, if any
| |
| **'''NameSng''' - varchar(32) -- word for single item of this type (e.g. "shirt","box")
| |
| **'''NamePlr''' - varchar(32) -- word for multiple items of this type (e.g. "shirts","boxes")
| |
| **'''Descr''' - text(256) -- longer name, e.g. "compact disc" instead of "CD". If blank, use NameSng
| |
| **'''isType''' - flag -- if TRUE, this type may be used for actual items; if not, it is a folder (type category)
| |
| :'''Note''': should "Descr" be plural? Or will we need DescrSng and DescrPlr?
| |
| | |
| *'''cat_ioptns''' -- catalog options | |
| **'''ID'''#@ - int(4) | |
| **'''CatKey''' - varchar(8) -- appended to catalog number (with dash prefix)
| |
| **'''Descr''' - varchar(64) -- appended to item description (with separator of some kind)
| |
| **'''Sort''' - varchar(8) -- sorting order when options for a title appear together in a list
| |
| | |
| ===Shopping===
| |
| | |
| 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.
| |
| ====Main data tables====
| |
| *'''shop_carts''' -- shopping carts | |
| **'''ID'''#@ - int(4) | |
| **WhenCreated - timestamp -- when this cart was created
| |
| **WhenLocked - timestamp -- when this cart was locked (can't add/remove items)
| |
| | |
| *'''shop_session''' -- shopping session
| |
| **'''ID'''#@ - int(4)
| |
| **'''Remote_Client''' - text -- browser user_agent string
| |
| **'''Remote_IPAddr''' - int(4) -- remote host IP address
| |
| **'''Remote_Domain''' - text -- remote host domain info (reverse lookup), if any
| |
| **'''WhenStarted''' - timestamp -- when session was started
| |
| **'''WhenLastAct''' - timestamp -- timestamp of last activity on this session
| |
| ====Collection/link tables====
| |
| *'''shop_cart_sessions''' -- a given cart might be accessed from different sessions, when that functionality is available
| |
| **'''ID_Cart'''# - int(4)
| |
| **'''ID_Sess'''# - int(4)
| |
| | |
| *'''shop_cart_items''' -- items in a cart
| |
| **'''ID_Cart'''# - int(4)
| |
| **'''ID_Item'''# - int(4)
| |
| **'''Qty''' - int(4) | |
| **'''CatNum''' - varchar(32) -- catalog number as sold
| |
| **'''Descr''' - text -- description as sold
| |
| **'''Price''' - currency -- price as sold
| |
| **'''ShipMin''' - currency -- quoted minimum standard shipping cost (e.g. if sent with a lot of other items)
| |
| **'''ShipMax''' - currency -- quoted maximum standard shipping cost (e.g. if sent by itself)
| |
| ==Future Changes==
| |
| Shipping code should be tied to the item Option (i.e. no ID_ShipCode field in cat_items), which would include size and other variants (i.e. cat_itypes and cat_iopts will be combined). Each Supplier should have its own list of item types, to allow for different variation (e.g. LB tie-dye shirts of a given size weigh more than ZR shirts of the same size because they use thicker cloth). I'm using the schema as it is because converting the existing data will be a complicated task and we need to get this thing working ASAP, but I picture something like this in the near future:
| |
| *'''cat_itypes''' - catalog item types | |
| **'''ID'''#@ - int(4) | |
| **'''Name''' - text
| |
| **'''ID_Parent''' - int(4) -- cat_itypes.ID of parent type, if any
| |
| *'''cat_ioptns''' - catalog item options
| |
| **'''ID'''#@ - int(4)
| |
| **'''Key''' - string(8) -- Supplier.CatKey-Title.CatKey[-Key] = complete catalog number (Key can be blank)
| |
| **'''Descr''' - string(64) -- description of this option (e.g. "eXtra Large")
| |
| *'''cat_itype_ioptns''' - options available for each item type, per supplier
| |
| **'''ID''' - int(4) -- unique identifier
| |
| **'''ID_Supplier'''# - int(4)
| |
| **'''ID_IType'''# - int(4)
| |
| **'''ID_IOptn'''# - int(4)
| |
| **'''ID_ShipCode''' - int(4) -- shipping cost type for this combination
| |
| **'''Descr''' - string(128) -- description of this option/type combination (e.g. "eXtra Large shortsleeve t-shirt")
| |
| We would then remove the ID_IType and ID_ShipType fields from cat_items; ID_IOpt would point to cat_itype_ioptns instead of cat_ioptns... or we could rename it ID_ITypeOptn, but that's... a bit ugly. I'll have to talk myself into it. Also, I think I used a scheme something like this in an earlier revision of the MS Access version of VbzCart and ended up changing it to what it is now; that may have been for a good reason, or it may have been because it seemed too unwieldy... I think I was generating a cat_itype_ioptns entry for ''every possible combination'' of IType and IOptn, and that doesn't make sense. You just need a tool to help manage them, and to copy existing sets for tweaking. It also didn't have an ID_Supplier key.
| |