VbzCart/docs: Difference between revisions

From Woozle Writes Code
Jump to navigation Jump to search
Line 7: Line 7:
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==
==Tables==
Fields which are used as keys (i.e. unique identifiers) are in italics
*"#" indicates Primary Key fields
*"@" indicates autonumbered fields
===Administration===
===Administration===
Main data tables:
====Main data tables====
*'''admin_users''' -- users with access to the admin system
*'''admin_users''' -- users with access to the admin system
**''ID'' - int(4) (autonumber)
**'''ID'''#@ - int(4)
**Name - varchar(32)
**'''Name''' - varchar(32)
**Email - varchar(128)
**'''Email''' - varchar(128)
*'''admin_groups''' -- each group has a role to play, and each role requires a particular set of privileges
*'''admin_groups''' -- each group has a role to play, and each role requires a particular set of privileges
**''ID'' - int(4) (autonumber)
**'''ID'''#@ - int(4)
**Name - varchar(32)
**'''Name''' - varchar(32)
**Descr
**'''Descr''' - text -- text describing the purpose of this group
*'''admin_privs''' -- particular privileges; meaning is defined in code
*'''admin_privs''' -- particular privileges; meaning is defined in code
**''ID'' - int(4) (autonumber)
**'''ID'''#@ - int(4)
**Name - varchar(32)
**'''Name''' - varchar(32)
**Descr
**'''Descr''' - text -- text describing this permission
 
====Collection/link tables====
Collection/link tables:
*'''admin_user_groups''' -- users in each group / groups to which each user belongs
*'''admin_user_groups''' -- users in each group / groups to which each user belongs
**''ID_User'' - int(4)
**'''ID_User'''# - int(4)
**''ID_Group'' - int(4)
**'''ID_Group'''# - int(4)
*'''admin_group_privs''' -- privileges each group has / groups having a particular privilege
*'''admin_group_privs''' -- privileges each group has / groups having a particular privilege
**''ID_Group'' - int(4)
**'''ID_Group'''# - int(4)
**''ID_Priv'' - int(4)
**'''ID_Priv'''# - int(4)
===Catalog===
===Catalog===


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.
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.
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.
Line 39: Line 39:
**'''ID'''#@ - int(4) (autonumber)
**'''ID'''#@ - int(4) (autonumber)
**'''ID_Supplier''' - int(4) -- cat_suppliers.ID
**'''ID_Supplier''' - int(4) -- cat_suppliers.ID
**Name - varchar(128) -- name of title, usually according to supplier (not strict)
**'''ID_Similar''' - int(4) -- cat_titles.ID of a similar title whose image can be used, if this title lacks one
**CatKey - varchar(32) -- must be unique within supplier
**'''Name''' - varchar(128) -- name of title, usually according to supplier (not strict)
**Descr - text -- descriptive text (also may be included in search)
**'''CatKey''' - varchar(32) -- must be unique within supplier
**'''Descr''' - text -- descriptive text (also may be included in search)


*'''cat_items''' -- catalog items
*'''cat_items''' -- catalog items
Line 49: Line 50:
**'''ID_IType''' - int(4) -- type of item
**'''ID_IType''' - int(4) -- type of item
**'''ID_IOpt''' - int(4) -- item option (usually size)
**'''ID_IOpt''' - int(4) -- item option (usually size)
**'''ID_ShipCode''' - int(4) -- shipping cost code
**'''Price''' - currency -- price to customer


===Shopping===
===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.
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
*'''shop_carts''' -- shopping carts
**''ID'' - int(4) (autonumber)
**'''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
*'''shop_session''' -- shopping session
**Remote_Client - text -- browser user_agent string
**'''ID'''#@ - int(4)
**Remote_IPAddr - int(4) -- remote host IP address
**'''Remote_Client''' - text -- browser user_agent string
**Remote_Domain - text -- remote host domain info (reverse lookup), if any
**'''Remote_IPAddr''' - int(4) -- remote host IP address
**WhenStarted - timestamp -- when this cart was started (i.e. first item added)
**'''Remote_Domain''' - text -- remote host domain info (reverse lookup), if any
**WhenLocked - timestamp -- when this cart was locked (can't add/remove items)
**'''WhenStarted''' - timestamp -- when session was started
 
**'''WhenLastAct''' - timestamp -- timestamp of last activity on this session
Collection/link tables:
====Collection/link tables====
*'''shop_cart_sessions''' -- a given cart might be accessed from different sessions, when that functionality is available
*'''shop_cart_sessions''' -- a given cart might be accessed from different sessions, when that functionality is available
**''ID_Cart'' - int(4)
**'''ID_Cart'''# - int(4)
**''ID_Sess'' - int(4)
**'''ID_Sess'''# - int(4)


*'''shop_cart_items''' -- items in a cart
*'''shop_cart_items''' -- items in a cart
**ID_Cart - int(4)
**'''ID_Cart'''# - int(4)
**ID_Item
**'''ID_Item'''# - int(4)
===Browsing===
**'''Qty''' - int(4)
These tables have to do with searching for items and how they are displayed; the data are mostly static.
**'''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)

Revision as of 18:41, 21 July 2005

Technology: VbzCart

Having evaluated osCommerce and found that, although it had many features I would like to have on vbz.net, it also lacked some crucial features currently needed (many of which are provided by the existing shopping cart system, and some of which would be needed in order to effectively manage porting the data from the existing system).

So I'm writing a new system, in PHP, based on the data design of the existing VBZ cart system and adding new pieces as needed.

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

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_titles -- catalog titles
    • ID#@ - int(4) (autonumber)
    • ID_Supplier - int(4) -- cat_suppliers.ID
    • ID_Similar - int(4) -- cat_titles.ID of a similar title whose image can be used, if this title lacks one
    • Name - varchar(128) -- name of title, usually according to supplier (not strict)
    • CatKey - varchar(32) -- must be unique within supplier
    • Descr - text -- descriptive text (also may be included in search)
  • cat_items -- catalog items
    • ID#@ - int(4) (autonumber)
    • CatNum - varchar(32) -- catalog number: must be unique, but may change
    • ID_Title - int(4) -- cat_titles.ID
    • ID_IType - int(4) -- type of item
    • ID_IOpt - int(4) -- item option (usually size)
    • ID_ShipCode - int(4) -- shipping cost code
    • Price - currency -- price to customer

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)