Home > Articles > Data

  • Print
  • + Share This
Like this article? We recommend

Building the Tables

The simple sales force application we build will enable a user to access information on all products sold by our company as well as all customers of our company. The user can create/modify new sales orders and when all is complete, can sync modified data back to our organization's central server RDBMS. Our "starter" database schema is very simple (four tables), with the goal being to highlight SQL Anywhere Studio capabilities and mobile development techniques.

To complete the goals of the application, we need four tables in order to store sales and product information: CUSTOMER, PRODUCTS, OPPORTUNITIES, and SALES. Figures 8 through 11 show the formats used in these tables.

Figure 8Figure 8 Creating the CUSTOMER table.


Figure 9Figure 9 Creating the PRODUCTS table.


Figure 10Figure 10 Creating the OPPORTUNITIES table.


Figure 11Figure 11 Creating the SALES table.


The ASA SQL statements used to create these tables is as follows:

CREATE TABLE "DBA"."CUSTOMER"
(
    "ID"                integer NOT NULL,
    "FIRST_NAME"            varchar(50) NULL,
    "LAST_NAME"             varchar(50) NOT NULL,
    "STREET_ADDRESS_NUMBER"     integer NULL,
    "STREET_NAME"           varchar(50) NULL,
    "CITY"              varchar(50) NULL,
    "STATE"             varchar(2) NULL,
    "ZIP"               integer NULL,
    "REMARKS"               text NULL,
    PRIMARY KEY ("ID")
)

CREATE TABLE "DBA"."PRODUCTS"
(
    "ID"                integer NOT NULL,
    "NAME"              varchar(200) NOT NULL,
    "PRICE"             money NOT NULL,
    "PRODUCT_NUMBER"            varchar(50) NOT NULL,
    "QUANTITY_IN_STOCK"         integer NOT NULL,
    "REMARKS"               text NULL,
    PRIMARY KEY ("ID")
)

CREATE TABLE "DBA"."OPPORTUNITIES"
(
    "ID"                integer NOT NULL,
    "CUSTOMER_ID"           integer NOT NULL,
    "PRODUCT_ID"            integer NOT NULL,
    "CONTACT_DATE"          datetime NOT NULL,
    "REMARKS"               text NULL,
    PRIMARY KEY ("ID")
)

CREATE TABLE "DBA"."SALES"
(
    "ID"                integer NOT NULL,
    "CUSTOMER_ID"           integer NOT NULL,
    "PRODUCT_ID"            integer NOT NULL,
    "QUANTITY"              integer NOT NULL,
    "TOTAL_PRICE"           money NULL,
    "REMARKS"               text NULL,
    PRIMARY KEY ("ID")
)

As a final step, we need to prepare our database for "real-world" (or something approximating the real world) usage by creating a user account in the database. This, too, can be accomplished via the Sybase Central interface. For starters, I created a user named "salesperson" with a password of "sales" in our starter database. This user can log into the database (via Sybase's included dbisql query tool or via our own app) using this username/password combination.

  • + Share This
  • 🔖 Save To Your Account