An Example 3 Tier Schema

Having introduced Three Tier Database Schemas in a previous blog, it's now time to return to the subject and discuss how to go about creating one.

As an example we'll take a small section of an imaginary internet purchasing system that tracks purchase events for the customer. Such events may include:

1. ORDERED – the user has placed an order
2. PROCESSING – The order is being processed
3. DISPATCHED – The goods are on their way
4. DELIVERED – The goods have been delivered

...and we'll call this system ORDERS

Obviously, the first thing to do is to create three users; one for each layer of the schema. In this case we’ll call them:

1. ORDERS
2. ORDERS_CODE
3. ORDERS_USER

Where ORDERS is the data layer, ORDERS_CODE contains the stored procedures and ORDERS_USER is the account that executes the stored procedures.

Remember that all the SQL below should be executes as the SYSTEM user.

There are three parts to the SQL:

Creating the Data Layer
Adding the Code Tier
Enabling User Tier Access

Creating the Data Tier

The SQL in this section creates the tables, generates the appropriate synonyms and grants specific privileges.

-- First clear up any mess
DROP INDEX ORDERS.Purchase_Events_idx;
DROP TABLE ORDERS.Purchase_Events;
DROP TABLE ORDERS.Purchase_Event_Codes;
DROP SEQUENCE ORDERS.EVENT_SEQUENCE;

CREATE SEQUENCE ORDERS.EVENT_SEQUENCE  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 200  NOCYCLE;

-- Then create the tables
CREATE TABLE ORDERS.Purchase_Event_Codes (
Event_Code Number(1) NOT NULL,
Event_TYPE varchar2(32),
Description varchar2(128),
CONSTRAINT PK_Event_Code PRIMARY KEY (Event_Code)
);

CREATE TABLE ORDERS.Purchase_Events (
PK NUMBER NOT NULL,
Purchase_Code VARCHAR2(24) NOT NULL,
Event_Code Number(1) NOT NULL,
Event_Date TIMESTAMP NOT NULL,
PRIMARY KEY(PK),
CONSTRAINT FK_EVENT_CODE 
FOREIGN KEY(Event_Code) 
REFERENCES ORDERS.Purchase_Event_Codes(Event_Code)
);

-- Generate an index or two 
CREATE INDEX ORDERS.Purchase_Events_idx  ON ORDERS.Purchase_Events ( Purchase_Code   );

-- Insert any static data
INSERT INTO ORDERS.Purchase_Event_Codes (Event_Code,Event_Type, Description) 
VALUES (1,'ORDERED', 'An order has been placed');

INSERT INTO ORDERS.Purchase_Event_Codes (Event_Code,Event_Type, Description) 
VALUES (2,'PROCESSING','The order is being processed');

INSERT INTO ORDERS.Purchase_Event_Codes (Event_Code,Event_Type, Description) 
VALUES (3,'DISPATCHED','The order has been dispatched.');

INSERT INTO ORDERS.Purchase_Event_Codes (Event_Code,Event_Type, Description) 
VALUES (4,'DELIVERED','Carrier Confirms Delivery.');


-- Sort out permissions - firstly by removing all the default permissions
REVOKE ALL ON ORDERS.Purchase_Event_Codes FROM ORDERS_CODE;
REVOKE ALL ON ORDERS.Purchase_Events FROM ORDERS_CODE;

-- Only grant those permissions that are actually used by the stored procs
GRANT SELECT ON ORDERS.Purchase_Event_Codes TO ORDERS_CODE;
GRANT SELECT, INSERT ON ORDERS.Purchase_Events TO ORDERS_CODE;
GRANT select ON ORDERS.EVENT_SEQUENCE TO ORDERS_CODE;

-- Create synonyms so that the code layer can access the data more indirectly
CREATE OR REPLACE SYNONYM ORDERS_CODE.ORDERS_Purchase_Event_Codes for ORDERS.Purchase_Event_Codes;
CREATE OR REPLACE SYNONYM ORDERS_CODE.ORDERS_Purchase_Events for ORDERS.Purchase_Events;
CREATE OR REPLACE SYNONYM ORDERS_CODE.ORDERS_EVENT_SEQUENCE FOR ORDERS.EVENT_SEQUENCE;

Having defined what a three tier schema design is any why you'd want to bother creating one (Security) and defined the data layer for a simple order tracking schema, the next blog in the series will complete the system, showing how to define the stored procedures that access the data, and how to limit access to the stored procedures (more security).

Posts in this series:

1. Introduction to 3 Tier Schemas in Oracle
2. An Example 3 Tier Schema
3. A Three Tier Schema: Adding the Code and User Tiers