Skip to content

Lane Operational Database

Jump to bottom Edit New page
Andy Theuninck edited this page May 29, 2015 · 1 revision

MasterSuperDepts

Name Type Info
superID INT
super_name VARCHAR(50)
dept_ID INT

Use: A department may belong to more than one superdepartment, but has one "master" superdepartment. This avoids duplicating rows in some reports. By convention, a department's "master" superdepartment is the one with the lowest superID.

ShrinkReasons

Name Type Info
shrinkReasonID INT PK Increment
description VARCHAR(30)

Use: Maintain list of reasons for marking shrink

SpecialDeptMap

Name Type Info
specialDeptModuleName VARCHAR(100) PK
dept_no INT PK

autoCoupons

Name Type Info
coupID INT PK
description VARCHAR(30)

Apply coupons to transactions automatically

couponcodes

Name Type Info
Code VARCHAR(4) PK
Qty INT
Value Real

Use: Standard UPC coupon codes. Code is the UPC suffix, Qty is required quantity, value is the coupon's value.

custPreferences

Name Type Info
card_no INT PK
pref_key VARCHAR(50) PK
pref_value VARCHAR(100)

Use: Store customer-specific preferences This table supplements custdata and is available at the lanes.

custReceiptMessage

Name Type Info
card_no INT Indexed
msg_text VARCHAR(255)
modifier_module VARCHAR(50)

Use: Create member-specific messages for receipts.

  • card_no is the member number
  • msg_text is the message itself
  • modifier_module is [optionally] the name of a class that should be invoked to potentially modify the message. An equity message, for example, might use a modifier module to check and see if payment was made in the current transaction

custdata

Name Type Info
CardNo INT Indexed
personNum TINYINT
LastName VARCHAR(30) Indexed
FirstName VARCHAR(30)
CashBack MONEY
Balance MONEY
Discount SMALLINT
MemDiscountLimit MONEY Default=0
ChargeLimit MONEY Default=0
ChargeOk TINYINT Default=1
WriteChecks TINYINT Default=1
StoreCoupons TINYINT Default=1
Type VARCHAR(10) Default='PC'
memType TINYINT
staff TINYINT Default=0
SSI TINYINT Default=0
Purchases MONEY Default=0
NumberOfChecks SMALLINT Default=0
memCoupons INT Default=1
blueLine VARCHAR(50)
Shown TINYINT Default=1
LastChange TIMESTAMP
id INT PK Increment Default=0

Use: This is one of two "primary" tables dealing with membership (the other is meminfo). Of the two, only custdata is present at the checkout. Column meaning may not be quite identical across stores.

[Probably] The Same Everywhere:

  • CardNo is the member's number. This identifies them.
  • personNum is for stores that allow more than one person per membership. personNum starts at 1. The combination (CardNo, personNum) should be unique for each record.
  • FirstName what it sounds like.
  • LastName what it sounds like.
  • Discount gives the member a percentage discount on purchases.
  • Type identifies whether the record is for an actual member. If Type is 'PC', the person is considered a member at the register. This is a little confusing, but not everyone in the table has to be a member.
  • blueLine is displayed on the checkout screen when the member's number is entered.
  • id just provides a guaranteed-unique row identifier.

[Probably] Just For Organizing: The register is mostly unaware of these settings, but they can be used on the backend for consistency checks e.g., make sure all staff members have the appropriate percent discount

  • staff identifies someone as an employee. Value: 1?
  • memType allows a little more nuance than just member yes/no. FK to memtype.memtype
  • SSI probably because of a historic senior citizen discount. (Sounds like it is obsolete or at least not used.)

WFC Specific:

  • ChargeOk=1 if member may run a store charge balance; =0 may not.
  • MemDiscountLimit is their store charge account limit.
  • ChargeLimit is their store charge account limit.
  • Balance is a store charge balance as of the start of the day, if the person has one. Some records are for organizations, esp vendors, that have charge accounts. Is updated from newBalanceToday_cust by cronjob arbalance.sanitycheck.php
  • memCoupons indicates how many virtual coupons (tender MA) are available.

[Probably] Ignored: To the best of my (Andy's) knowledge, these have no meaning on the front or back end.

  • CashBack
  • WriteChecks
  • StoreCoupons
  • Purchases
  • NumberOfChecks
  • Shown

Maintenance:

  • Single edit: fannie/mem/search.php
  • Single add: fannie/mem/new.php
  • Batch import: fannie/mem/import/*.php
  • custdata.Balance is updated from newBalanceToday_cust by cronjob arbalance.sanitycheck.php

customReceipt

Name Type Info
text VARCHAR(80)
seq INT PK
type VARCHAR(20) PK

Use: This table contains strings of text that originally lived in the lane's ini.php. At first it was only used for receipt headers and footers, hence the name. Submit a patch if you want a saner name.

Current valid types are:

  • receiptHeader
  • receiptFooter
  • ckEndorse
  • welcomeMsg
  • farewellMsg
  • trainingMsg
  • chargeSlip

dateRestrict

Name Type Info
upc VARCHAR(13) PK
dept_ID INT PK
restrict_date DATE
restrict_dow SMALLINT
restrict_start TIME
restrict_end TIME

Use: Store restrictions for selling products at certain dates & times. Restrictions can be specified by UPC or department number as well as by exact date or day of week. If start and end times are entered, restriction will only apply during that span

departments

Name Type Info
dept_no SMALLINT PK
dept_name VARCHAR(30) Indexed
dept_tax TINYINT
dept_fs TINYINT
dept_limit MONEY
dept_minimum MONEY
dept_discount TINYINT
dept_see_id TINYINT Default=0
modified DATETIME
modifiedby INT
margin DOUBLE
salesCode INT
memberOnly SMALLINT Default=0

Use: Departments are the primary level of granularity for products. Each product may belong to one department, and when items are rung up the department setting is what's saved in the transaction log

dept_no and dept_name identify a department

dept_tax,dept_fs, and dept_discount indicate whether items in that department are taxable, foodstampable, and discountable (respectively). Mostly these affect open rings at the register, although WFC also uses them to speed up new item entry.

dept_limit and dept_minimum are the highest and lowest sales allowed in the department. These also affect open rings. The prompt presented if limits are exceeded is ONLY a warning, not a full stop.

margin is desired margin for products in the department. It can be used for calculating retail pricing based on costs. By convention, values are less than one. A value of 0.35 means 35% margin. This value has no meaning on the lane.

salesCode is yet another way of categorizing items. It is typically used for chart of account numbers. Often the financial accounting side of the business wants to look at sales figures differently than the operational side of the business. It's an organizational and reporting field with no meaning on the lane.

memberOnly restricts sales based on customer membership status. Values 0 through 99 are reserved. 100 and above may be used for custom settings. Currently defined values: 0 => No restrictions 1 => Active members only (custdata.Type = 'PC') 2 => Active members only but cashier can override 3 => Any custdata account except the default non-member account

disableCoupon

Name Type Info
upc VARCHAR(13) PK
threshold SMALLINT Default=0
reason text

Use: Manually disable a specific manufacturer coupon UPC

drawerowner

Name Type Info
drawer_no SMALLINT PK
emp_no SMALLINT

Use: Manage assignments when more than one cash drawer is present.

employees

Name Type Info
emp_no SMALLINT PK
CashierPassword VARCHAR(50)
AdminPassword VARCHAR(50)
FirstName VARCHAR(50)
LastName VARCHAR(50)
JobTitle VARCHAR(50)
EmpActive TINYINT
frontendsecurity SMALLINT
backendsecurity SMALLINT
birthdate DATETIME

Use: Table of cashiers. emp_no identifies a cashier uniquely. CashierPassword and AdminPassword are numeric passcodes used pretty interchangably (should probably match). EmpActive toggles whether an account can actually log in. frontendsecurity is used to restrict certain actions at the register based on security level.

globalvalues

Name Type Info
CashierNo INT
Cashier VARCHAR(30)
LoggedIn TINYINT
TransNo INT
TTLFlag TINYINT
FntlFlag TINYINT
TaxExempt TINYINT

Use: A small subset of session values. Storing this in SQL ensures it will survive a browser crash or reboot to pick up more-or-less when the transaction left off.

houseCouponItems

Name Type Info
coupID INT PK
upc VARCHAR(13) PK
type VARCHAR(15)

Use: WFC runs custom barcoded coupons with upc prefix 499999. See houseCoupons for more detail. Type here should be 'QUALIFIER', 'DISCOUNT', or 'BOTH'.

houseCoupons

Name Type Info
coupID INT PK
description VARCHAR(30)
startDate DATETIME
endDate DATETIME
limit SMALLINT
memberOnly SMALLINT
discountType VARCHAR(2)
discountValue MONEY
minType VARCHAR(2)
minValue MONEY
department INT
auto TINYINT Default=0

Use: WFC runs custom barcoded coupons with upc prefix 499999, then the coupon ID (zero padded to 5 digits). There's a tool in legacy for putting these together that may eventually make it into fannie.

startDate is the first day the coupon is valid

endDate is the last day the coupon is valid

limit is the number of times the coupon can be used in one transaction

memberOnly means the coupon can only be used by a member (custdata.Type='PC')

minType and minValue specify how one qualifies for the coupon - certain item(s), purchase amount, etc

  • 'Q' must purchase at least minValue qualifying items (by quantity)
  • 'Q+' must purchase more than minValue qualifying items (by quantity)
  • 'D' must purchase at least minValue items from qualifying departments (by $ value)
  • 'D+' must purchase more than minValue items from qualifying departments (by $ value)
  • 'M' is mixed. Must purchase at least minValue qualifying items and at least one discount item
  • '$' must puchase at least minValue goods (by $ value)
  • '$+' must puchase more than minValue goods (by $ value)
  • '' blank means no minimum purchase

discountType and discountValue specify how the discount is calculated

(item related discounts)

  • 'Q' discount equals discountValue times unitPrice for the cheapest qualifying item (essentially percent discount)
  • 'P' discount equals unitPrice minus discountValue for the cheapest qualifying item (essentially an sale price)
  • 'FI' discount equals discountValues times quantity for the cheapest qualifying item (works with by-weight items)

(department related discounts)

  • 'FD' discount equals discountValue times quantity for the cheapest qualifying item
  • 'AD' discount equals discountValue times sum(quantity) for ALL qualifying items

(other discounts)

  • 'F' discount equals discountValue
  • '%' discountValue is a percent discount for all discountable items

Qualifying items are stored in houseCouponItems. Not all coupons require entries here. Records can be items (by UPC) or departments (by department number). Some minimum and discount types only work with one or the other.

houseCouponItems.coupID is the coupon ID

houseCouponItems.upc is an item UPC or a department number.

houseCouponItems.type is only relevant to the mixed (M) minimum type. Values are:

  • 'QUALIFIER' counts as a qualifying item for mixed
  • 'DISCOUNT' counts as a discount item for mixed
  • 'BOTH' can be a qualfying item or a discount item If not using the mixed minimum, always choose 'BOTH'

The nuts and bolts of this are in the UPC.php parser module (IT CORE).

houseVirtualCoupons

Name Type Info
card_no INT PK
coupID INT PK
description VARCHAR(100)
start_date DATETIME
end_date DATETIME

Use: Assign house coupons to members so they can be applied without scanning a barcode

memberCards

Name Type Info
card_no INT PK Default=0
upc VARCHAR(13) PK Default=

Use: WFC has barcoded member identification cards. card_no is the member, upc is their card.

memberCardsView

View

Name Type Info
upc VARCHAR(13)
card_no INT

memchargebalance

Name Type Info
CardNo INT
availBal MONEY
balance MONEY

Use: DEPRECATED 4Jan14 no longer used

View showing member charge balance. Authoritative, up-to-the-second data is on the server but a local lookup is faster if slightly stale data is acceptable.

memtype

Name Type Info
memtype TINYINT PK Default=0
memDesc VARCHAR(20)
custdataType VARCHAR(10)
discount SMALLINT
staff TINYINT
ssi TINYINT

Use: Housekeeping. If you want to sort people in custdata into more categories than just member/nonmember, use memtype.

The custdataType, discount, staff, and ssi are the default values for custdata's Type, discount, staff, and ssi columns when creating a new record of a given memtype.

parameters

Name Type Info
store_id SMALLINT PK
lane_id SMALLINT PK
param_key VARCHAR(100) PK
param_value VARCHAR(255)
is_array TINYINT

Use: Partial replacement for ini.php.

Values with store_id=0 (or NULL) and lane_id=0 (or NULL) are applied first, then values with the lane's own lane_id are applied second as local overrides. A similar precedent level based on store_id may be added at a later date.

products

Name Type Info
upc VARCHAR(13) Indexed
description VARCHAR(30) Indexed
brand VARCHAR(30)
formatted_name VARCHAR(30)
normal_price MONEY
pricemethod SMALLINT
groupprice MONEY
quantity SMALLINT
special_price MONEY
specialpricemethod SMALLINT
specialgroupprice MONEY
specialquantity SMALLINT
special_limit TINYINT
start_date DATETIME
end_date DATETIME
department SMALLINT Indexed
size VARCHAR(9)
tax SMALLINT
foodstamp TINYINT
scale TINYINT
scaleprice MONEY
mixmatchcode VARCHAR(13)
modified DATETIME
advertised TINYINT
tareweight DOUBLE
discount SMALLINT
discounttype TINYINT
line_item_discountable TINYINT Default=0
unitofmeasure VARCHAR(15)
wicable SMALLINT
qttyEnforced TINYINT
idEnforced TINYINT
cost MONEY
inUse TINYINT
numflag INT Default=0
subdept SMALLINT
deposit DOUBLE
local INT Default=0
store_id SMALLINT Default=0
default_vendor_id INT Default=0
current_origin_id INT Default=0
id INT PK Increment Default=0

Use: This table lists items in the system.

upc is how items are identified. Regardless of whether it's an integer or a varchar, it should always have length 13. Whether or not to include check digits is up to the individual store.

id provides a unique row identifier, but upc should probably be unique too. If not, you'll have to add code to either let the cashier choose which matching record or to limit which records are pushed to the registers.

description is used for screen display, reporting, and receipts. formatted_name is an alternative that will be used instead of description if it has a non-NULL, non-empty value. brand and description are intended to be distinct fields for use in things like shelf tags and signage. formatted_name can be used to combine these two fields or otherwise create a standardized screen/receipt description containing extra information.

Pricing: When an item has pricemethod 0, the price is simply normal_price. If pricemethod is greater than 0, groupprice and quantity are used to calculate the price. There is variance here by implementation, but generally pricemethod 1 or 2 will yield the most obvious grouped pricing. Example: buy one, get the second 50% off normal_price => 1.00 pricemethod => 1 (or maybe 2) groupprice => 1.50 quantity => 2 If discounttype is greater than zero, the special* columns get used instead but otherwise behavior should be similar.

start_date and end_date indicate the start and end of a sale. The current register code does not check these to validate sales.

department and subdept are an item's department and subdepartment settings.

tax indicates if an item is taxable and at what rate

foodstamp indicates whether an item can be purchased using foodstamps

scale indicates whether an item should be sold by weight

scaleprice indicates what type of random-weight barcodes are used. Value zero means UPC-A where the last 4 digits contains price with max value $99.99. Value one means EAN-13 where the last 5 digits contain price with max value $999.99.

mixmatchcode relates to pricing when pricemethod is greater than zero. Items with the same mixmatchcode are considred equivalent when determining whether the customer has reached the required quantity.

modified [ideally] lists the last time a product was changed. Not all back end tools remember to update this and of course direct updates via SQL may forget too.

tareweight is a default tare for by weight items

discount indicates whether an item is eligible for percentage discounts on a whole transactions. Value 0 means exclude from discounts.

discounttype indicates if an item is on sale 0 => not on sale 1 => on sale for everyone 2 => on sale for members Values greater than 2 may be used, but results will vary based on whose code you're running

line_item_discount indicates whether an item is eligible for line item discounts. Value 0 means not eligible.

unitofmeasure might be used for screen display and receipt listings of quantity.

qttyEnforced forces the cashier to enter an explicit quantity when ringing up the item

idEnforced forces the cashier to enter the customer's date of birth. This flag should be set to the age required to purchase the product - e.g., 21 for alcohol in the US.

cost is the item's cost

isUse indicates whether the item is currently available for sale. Whether cashiers can bypass this setting probably varies by front end implementation.

local indicates whether the item is locally sourced.

deposit is a PLU. The product record with this UPC will be added to the transaction automatically when the item is rung.

default_vendor_id is the identifier (vendors.vendorID) for the vendor who typically supplies the product.

current_origin_id is the identifier (origins.originID) for the geographical location where the product is currently sourced from.

Other columns: size, advertised, wicable, and numflag have no current meaning on the front or back end. Or no current implementation. The meaning of idEnforced is pretty clear, but setting it won't do anything.

subdepts

Name Type Info
subdept_no SMALLINT PK
subdept_name VARCHAR(30) Indexed
dept_ID SMALLINT PK

Use: A department may contain multiple subdepartments. In most implementations I've seen, invidual products can be tagged with a subdepartment, but that setting doesn't go into the final transaction log

tenders

Name Type Info
TenderID SMALLINT PK
TenderCode VARCHAR(2) Indexed
TenderName VARCHAR(25)
TenderType VARCHAR(2)
ChangeMessage VARCHAR(25)
MinAmount MONEY Default=0.01
MaxAmount MONEY Default=1000
MaxRefund MONEY Default=1000
TenderModule VARCHAR(50) Default='TenderModule'

Use: List of tenders IT CORE accepts. TenderCode should be unique; it's what cashiers type in at the register as well as the identifier that eventually shows up in transaction logs.

ChangeMessage, MinAmount, MaxAmount, and MaxRefund all do exactly what they sound like.

TenderName shows up at the register screen and on various reports.

TenderType and TenderID are mostly ignored.

unpaid_ar_today

Name Type Info
card_no INT PK
old_balance MONEY
recent_payments MONEY

Use: Listing of overdue balances. Authoritative, up-to-the-second data is on the server but checking a local table is faster if slightly stale data is acceptable

Add a custom footer
Clone this wiki locally