| ||||
| ||||
|
|
[ Current Status ] |
[ CVS ] |
[ Mail List ] |
[ Files ] |
[ Jobs ]
|
|
||
|
Home
EasyPOS
|
|
|
Database Design
--------------------------------------------------------------------------------
-- users and security profiles required for build 1
drop table users;
create table users (
username text primary key not null,
passwd text not null,
profile text not null,
last_login abstime,
logged_in bool default 'f',
terminal int -- current terminal logged into
);
drop table user_profile;
create table user_profile (
profile text primary key not null,
default_page text,
can_bad_cust bool default 't',
can_alter_cust bool default 't',
can_pos bool default 't',
can_receipt bool default 't',
can_table bool default 't',
can_internet bool default 'f',
can_drink bool default 'f',
can_auth_timesheet bool default 'f',
can_view_purch bool default 'f',
can_purch bool default 'f',
can_book bool default 't',
can_maintain bool default 'f',
can_report bool default 't',
can_music bool default 'f',
can_cancel_ord bool default 'f',
can_change_ord bool default 'f',
can_send_email bool default 'f',
can_quit bool default 't',
no_auto_logout bool default 'f' -- this user overrides auto-logout feature of terminal
);
-- create some basic security profiles
insert into user_profile (profile,default_page) values ('TRAIN',1);
insert into user_profile (profile,default_page) values ('USER',1);
insert into user_profile (profile,default_page) values ('DRINK',7);
insert into user_profile (profile,default_page) values ('MANAGER',1);
insert into user_profile (profile,default_page) values ('OWNER',1);
update user_profile set can_internet='t',can_drink='t',can_auth_timesheet='t',
can_view_purch='t',can_music='t',can_cancel_ord='t',can_change_ord='t',
can_send_email='t' where profile='MANAGER' or profile='OWNER';
update user_profile set can_maintain='t',can_purch='t' where profile='OWNER';
-- create some users
insert into users (username,passwd,profile) values ('1','1','USER');
insert into users (username,passwd,profile) values ('TRAIN','train','TRAIN');
insert into users (username,passwd,profile) values ('DRINK','drink','DRINK');
insert into users (username,passwd,profile) values ('MANAGER','manager','MANAGER');
insert into users (username,passwd,profile) values ('JACK','sprat','OWNER');
--------------------------------------------------------------------------------
-- stores file for mulitple branches - required for build2
drop table stores;
create table stores (
id int not null primary key,
store_name text,
address text,
suburb text,
postcode text, -- US postcodes sometimes have alpha chars !!
phone text,
manager text,
modem text,
update_hour int,
update_minute int,
is_branch bool
);
-- create some stores
COPY "stores" FROM stdin;
1 Demoland Central 1 Central Tower Demoville 90210 8290 7574 Jack Sprat 82908801 \N \N f
2 Downtown Demoland 34 The Pavillion East Demoville 90211 8345 4567 Peter Tosh 8908802 3 0 t
3 Demoland Rural Division 277 Cornjack way South Demoville 90212 8233 4200 Jerry Lee Cooper 8909320 4 0 t
\.
--------------------------------------------------------------------------------
-- customers file. Required for build1
drop sequence customers_id_seq;
drop table customers;
create table customers (
id serial primary key,
name text,
address text,
phone text,
suburb text,
map_ref text,
comments text,
bad_cust bool,
date_created date,
min_order_value decimal(9,2),
deliv_charge decimal(9,2),
-- de-normalized stats held against the customer
lastorder_num int,
lastorder_date date,
total_value decimal(9,2),
last_value decimal(9,2),
avg_value decimal(9,2),
avg_period int
);
-- enable internet accounts, and gather useful marketting guff while
-- we are at it
drop table income_ranges;
create table income_ranges (
id int primary key,
descr text
);
copy income_ranges from stdin;
1 up to 20,000
2 up to 40,000
3 up to 60,000
4 up to 80,000
5 over 80,000
\.
drop table mar_status;
create table mar_status (
code char primary key,
descr text
);
copy mar_status from stdin;
S Single
M Married
D Divorced / Sep
W Widowed
\.
drop table customer_internet;
drop sequence customer_internet_id_seq;
create table customer_internet (
id serial primary key,
passwd text not null,
email text,
rating text, -- How do the staff of this shop rate this user ?
gender char, -- Male / Female that is
birthday date,
company text, -- who do they work for
income int,
mar_status char,
own_house bool,
renting bool,
make_of_car text,
year_of_car int,
how_heard text -- how did they hear about us
);
-- if nothing else you can always login to a business that has a decent database and
-- run the followin query :
-- "select i.id,c.name,c.suburb,c.phone,i.email from customers c,customer_internet i where
-- c.id = i.id and i.gender='F' and i.mar_status='S' and i.income >= 4
-- and i.make_of_car ~ "/^bm|^por|^merc|^aud/i";
--
-- of course, you may prefer Pontiacs .. each to his own
--------------------------------------------------------------------------------
-- item categories
drop table category;
create table category (
code text not null primary key,
descr text,
allow_discount bool,
icon text,
tax_rate decimal(5,2),
x_label text,
y_label text,
z_label text
);
-- create some categories now
--
-- this is deliberatly setup for a restaraunt & pizza delivery business
-- but we will include some templates for other types of businesses later on such as :
-- florist, mountain bike shop, miniature wargames shop, video shop, and second hand bookshop
COPY "category" from stdin;
P Pizza t pizza.png \N Size Topping Base
T Pasta t pasta.png \N Pasta Sauce Serve
S Steak t steak.png \N Method Style Cooking
C Chicken t chicken.png 3.5 Type Dish \N
E Seafood t seafood.png \N Type Dish \N
Z Specials f specials.png \N Type Options \N
R Drinks f drinks.png 10.0 Size Drink Mixer
A Appetisers t appetisers.png \N Type Dish \N
D Dessert f dessert.png \N Size Serve \N
\.
-- Note in our demo here, drinks and special offers are not discounted (ie - if the customer
-- has a 10% discount, that does not apply to drinks and special offers). Also, there is an
-- an additional 10% drinks tax added to drinks, as well as a 3.5% Poultry tax on all chicken
-- orders. (There is no such thing as a poultry tax - it is just an illustration)
--------------------------------------------------------------------------------
-- items database. Please read this section twice - it is easy once you get it.
--
-- In GtkPizza, items are 1-liner stock items, which is easy on the brain, but very
-- difficult to generate new sets of custom data. In gShop we use a 3 dimensional cube
-- to represent all the pricing and variations for a category of item.
--
-- Take Pizza for example - there are say 10 different topping combinations (Mexican, hawiian, etc)
-- as one dimension, and the size (small,large,family,party) as the other dimension - that is
-- 40 combinations so far. On top of this, you can order your pizza as thin, pan, cheesy crust
-- or new york style - that is now 40 * 4 = 160 combinations. GtkPizza would require 160 stock items
-- to cope with this. It gets worse when you look at pasta - fettucini, tagliatelle, rotella, etc
-- combined with a huge variety of sauces - alfredo, carbonara, fantasia, bolognaise, etc. You want
-- that entree sized or main course sir ? .. arghh !
--
-- The problem is not confined to pizza bars either - your average mountain bike shop has a similar
-- problem, as does a video shop, bookshop, florist, and miniature wargames supplier.
--
-- The solution is to define a pair of product dimensions for each category of item, that defines
-- a 2D pricing grid. On top of this is an optional third dimension that presents service options
-- that apply a fixed modification to the price.
--
-- The system can then generate unique stock codes on the fly for individual combinations of things,
-- and it fits intuitively with the touchscreen grid approach for data entry, as well as being able
-- to generate web-based order screens that dont appear to overwhelm the user with a million options.
--
-- the real stock code for an item then is going to be
-- Category-Xaxis-Yaxis-Zaxis
--
-- It also makes reporting a breeze, since (at least [my|postgre]SQL) can do regular expression searches
-- then you can ask for reports like 'How many Hawiian vs Mexican pizzas have we sold, regardless of
-- the size ?', 'What is the most popular pasta sauce ?', 'Break down our turnover into flowers wrapped
-- in plain paper vs flowers wrapped in foil' and get them out in 1 line of SQL code, without any
-- table joins.
drop table item_x;
drop table item_y;
drop table item_z;
-- horizontal x axis of the 2D pricing grid
create table item_x (
category text not null,
code text not null,
descr text,
long_descr text,
icon text
);
create unique index item_x_idx on item_x (category,code);
-- vertical y axis of the 2D pricing grid
create table item_y (
category text not null,
code text not null,
descr text,
long_descr text,
icon text
);
create unique index item_y_idx on item_y (category,code);
-- 2D pricing grid for each category
drop table price_grid;
create table price_grid (
category text not null,
code_x text not null,
code_y text not null,
price decimal(9,2)
);
create unique index price_grid_idx on price_grid (category,code_x,code_y);
-- 3rd dimension of item
create table item_z (
category text not null,
code text not null,
descr text,
icon text,
price_mod decimal(9,2)
);
create unique index item_z_idx on item_z (category,code);
-- now to create some items
COPY item_x from stdin;
P S Small Small 9inch small.png
P L Large Large 12inch large.png
P F Family Family 15inch family.png
P P Party Party Size 18inch party.png
\.
COPY item_y from stdin;
P MEX Mexican Ham, Peperoni, Chili, Caps, Mushroom mexican.png
P HAW Hawiian Ham, Pineapple hawiian.png
P AME American Cheese & Peperoni american.png
P ROM Roman Ham, Egg roman.png
P MAR Margherita Cheese and Herbs margherita.png
\.
COPY item_z from stdin;
P O Original thin.png 0.00
P P Pan thick.png 1.00
P N NewYork Style newyork.png 2.50
P C CheesyCrust cheesy.png 1.50
\.
-- That was pretty painless : we just defined 4 * 5 * 4 = 80 different types of pizza
-- Will not bother putting the pricing grid in here - it is easy to do that on the screen
--------------------------------------------------------------------------------
-- suburbs table required for build1
drop table suburbs;
create table suburbs (
suburb text not null primary key,
postcode text, -- some US postcodes are alphanumeric
map_ref text,
store_id int, -- closest or preferred store
min_order decimal(9,2),
deliv_charge decimal(9,2)
);
-- no need to create data now, as gShop will automatically build this file up from
-- the data that is entered
-- different delivery charge to each suburb depending on what store the delivery is
-- coming from. This is important for chain stores, since they all need to synchronise
-- their databases, you still want to see only the relevant delivery charges being applied
-- from any given store.
drop table suburb_min;
create table suburb_store (
suburb text,
store_id int,
min_order decimal(9,2),
deliv_charge decimal(9,2)
);
--------------------------------------------------------------------------------
-- Order headers required for build 1
drop table order_type;
create table order_type (
code text not null primary key,
descr text
);
copy order_type from stdin;
D Delivery
P Pickup
FD Future Delivery Order
FP Future Pickup Order
B Booking
IP Internet Pickup Order
ID Internet Delivery Order
SD Standing Delivery Order
SP Standing Pickup Order
T Table Account
C Cash / POS
\.
drop table orders;
drop sequence orders_id_seq;
create table orders (
id serial primary key,
store_id int,
type text,
customer_id int, -- only applys to delivery orders and internet orders
name text,
date datetime,
table_num int, -- only applys to sit down orders of type T
eta abstime, -- only applys to pickup and delivery orders
comments text,
-- denormalized fields
total_price decimal(9,2)
);
-- order detail lines
drop table ordet;
create table ordet (
store_id int, -- to match the order id
id int, -- order's id
seq_num int,
item_code text, -- see notes on the 4-part item code above = Cat-X-Y-Z
descr text,
qty decimal(5,2),
price decimal(9,2)
);
create unique index order_idx on ordet (store_id,id,seq_num);
-- basic auditing on order detail lines, so that employees cannot fake the
-- computer order up to match the till for their friends
drop table ordhist;
create table ordhist (
store_id int,
id int,
seq_num int,
date abstime,
item_code text,
descr text,
qty decimal(5,2),
price decimal(9,2)
);
--------------------------------------------------------------------------------
-- Tables table required for build1 - but not sure if 'tables' is always a valid SQL name !
drop table seating_status;
create table seating_status (
code text primary key,
descr text
);
copy seating_status from stdin;
A Available
O Occupied
U Un-available
B Booked
\.
drop table seating;
drop sequence seating_id_seq;
create table seating (
id serial primary key,
x_pos int, -- graphical screen position
y_pos int, -- graphical screen position
capacity int,
guests int,
status text,
starttime abstime, -- If occupied, when did they start, in Unixtime
-- derive the time spent at runtime = time(NULL) - starttime;
-- denormalized fields
total_bill decimal(9,2)
);
--------------------------------------------------------------------------------
-- receipting table to track what has been received
drop table receipts;
drop sequence receipts_id_seq;
create table receipts (
id serial primary key,
store_id int, -- relates back the store
date datetime,
order_id int, -- relates back to the order header
total decimal(9,2),
type text,
card_num text -- if they paid by card, what was the card number, or the eftpos receipt num
);
--------------------------------------------------------------------------------
-- info on which printers and terminals are available
drop table printers;
drop sequence printers_id_seq;
create table printers (
id serial primary key,
store int, -- which store is this printer in ?
descr text, -- make and model
spooler_name text, -- lpr -Pspooler_name is invoked, so all
-- terminals need to be setup consistently
-- to be able to hit all printers
columns int, -- how many columns can this printer do ?
double_paper bool, -- does this printer do double paper ?
-- if not, we have to spool 2 copies of a docket
cut_paper bool, -- should be sent a 'cut paper' command after
-- each job ?
cut_command text, -- hex digits to send to cut the paper
can_color bool, -- can it do color ?
color_on text, -- hex digits to turn color on
color_off text, -- hex digits to turn color off
can_cashdraw bool, -- can this printer open a cashdrawer ?
cashdraw_open text, -- hex digits to open a cashdrawer
can_receipt bool,
can_report bool
);
copy printers from stdin;
1 1 Main Receipt Printer receipt 40 f f \N f \N \N t f
2 1 Main Report Printer report 132 f f \N f \N \N f t
\.
drop table op_systems;
drop sequence op_systems_id_seq;
create table op_systems (
id serial primary key,
descr text
);
copy op_systems from stdin;
1 Linux
2 Win98
3 WinNT
4 MacOS
5 Alpha Tru64
6 Solaris
7 Other Unix
8 VMS
9 MS-DOS
10 BeOS
\.
drop table terminals;
drop sequence terminals_id_seq;
create table terminals (
id serial primary key,
store int, -- which store is this terminal in ?
descr text,
receipt_printer int,
report_printer int,
can_socket bool, -- can this terminal accept incoming sockets ?
hostname text,
port int, -- which port to use for socket connections ?
op_system int, -- relates to op system table
phone_ext text, -- telephone extension
can_music bool default 'f', -- can this terminal do music control ?
logout_timeout int, -- max seconds of inactivity before logout
has_barcode bool,
barcode_device text, -- serial port for barcode device
has_callerid bool,
callerid_device text -- serial port for callerid device
);
copy terminals from stdin;
1 1 Main gShop Server 1 2 t localhost 6801 1
\.
--------------------------------------------------------------------------------
-- Simple internal email database
drop table email;
create table email (
id serial primary key,
from_user text not null,
from_terminal int not null,
from_store int not null,
address_to int, -- 1 = specific user, 2 = profile, 3 = terminal, 4 = store, 0 = all
to_user text,
to_profile text,
to_terminal int,
to_store int,
date_sent abstime,
date_read abstime,
is_read bool default 'f',
message text
);
--------------------------------------------------------------------------------
-- in-store advertising
drop table adverts;
create table adverts (
id serial primary key,
descr text,
frame_delay int, -- milliseconds between frames
frames text[], -- mySQL porters beware !! - this field
-- uses a postgreSQL array'ed field
-- the contents of the field simply points to
-- an image file that is scaled into the advert canvas
);
--------------------------------------------------------------------------------
-- System setup table
drop table global_options;
create table global_options (
id int, -- id 1 is used
cash_till_mode bool, -- true if cash-till mode (else store-wide mode)
POS_touchscreen bool, -- use touch panel for POS transactions
POS_adverts bool, -- display adverts on POS transactions
);
--------------------------------------------------------------------------------
-- yet to do in the way of SQL tables
--
-- mySQL equivalent
-- Timesheet data
-- Bookings for tables
-- Special header extensions for future orders
-- Special header extensions for standing orders
--
--------------------------------------------------------------------------------
|