A simple database schema to store Shareasale product data

Date: Fri Jul 20 2007
Given the simple needs of the product categorization, the database schema is very simple. I decided to map the CSV file format directly to a database table.

Because I'm using groovy I have available the full power of the Java programming environment. This makes for an extremely wide choice of databases. In this case I'm using the Apache Derby database (also known as JavaDB). It is attractive because it doesn't require a server to be running. Instead the database runs in memory, with data stored on the disk.

I use this script to build the database table. Simply use the "ij" appliction in the Derby/JavaDB distribution.



connect 'jdbc:derby:shareasale;create=true'; 

CREATE TABLE products (
    ProductID           INTEGER NOT NULL,
    Name                CHAR(254),
    MerchantID          INTEGER,
    Merchant            VARCHAR(254),
    Link                VARCHAR(254),
    Thumbnail           VARCHAR(254),
    BigImage            VARCHAR(254),
    Price               VARCHAR(254),
    RetailPrice         VARCHAR(254),
    Category            VARCHAR(254),
    SubCategory         VARCHAR(254),
    Description         LONG VARCHAR,
    Custom1             LONG VARCHAR,
    Custom2             LONG VARCHAR,
    Custom3             LONG VARCHAR,
    Custom4             LONG VARCHAR,
    Custom5             LONG VARCHAR,
    Lastupdated         VARCHAR(254),
    Status              VARCHAR(254),
    Manufacturer        VARCHAR(254),
    PartNumber          VARCHAR(254),
    MerchantCategory    VARCHAR(254),
    MerchantSubcategory VARCHAR(254),
    ShortDescription    LONG VARCHAR,
    ISBN                VARCHAR(254),
    UPC                 VARCHAR(254),
    PRIMARY KEY ( ProductID, Name, Category, SubCategory )
);