[ Current Status ]   |   [ CVS ]   |   [ Mail List ]   |   [ Files ]   |   [ Jobs ]

SourceForge Logo

Project Home


Features &




Related Project

Database Design

Database Schema (PostgreSQL)

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