DB Store for JPetStore

[Scott Semyan]

Hello all, I am in the process of trying to convert the JPetStore app to
use a database as it's datastore (as opposed to an in-memory data
store). This will show off the data controls and make the app a bit more
'real-world'. I plan to do the initial work in PostgreSQL since the dev
tools are very good, then try to convert over to Derby to ensure the app
is standalone.
 
My first attempt at a data schema is below. I'm sure this will change a
bit as I start to build out the code. I've tried to enforce referential
integrity, and I'm sure I could put more constraints, triggers, etc. in
to make the datastore more robust, but this is not my primary focus. I
plan to use intermediate DAO classes to abstract out my calls to the
data control. Once I have a working prototype of this I'll send it out
for review.
 
Scott Semyan
 
create table Categories
(
  catId serial primary key,
  name varchar(20) not null,
  description varchar(100) not null,
  image varchar(10) not null
);
 
create table Products
(
  productId serial primary key,
  catID int references Categories(catId) not null,
  name varchar(20) not null,
  description varchar(100) not null,
  image varchar(10) not null
);
 
create table Items
(
  itemId serial primary key,
  productId int references Products(productId) not null,
  status varchar(10) not null,
  attr1 varchar(20) null,
  attr2 varchar(20) null,
  attr3 varchar(20) null,
  attr4 varchar(20) null,
  attr5 varchar(20) null,
  listPrice money not null,
  unitCost money not null,
  supplier int not null,
  inventoryQuantity int not null
);
 
create table Accounts
(
    userId serial primary key,
    password varchar(20) not null,
    email varchar(20) not null,
    phone char(12) not null,
    firstName varchar(20) not null,
    lastName varchar(30) not null,
    status varchar(10) not null,
    favCategory varchar(10) null,
    langPref varchar(10) null,
    bannerData varchar(20) null,
    bannerName varchar(20) null,
    myListOpt int not null,
    bannerOpt int not null
);
 
create table Addresses
(
  addressId serial primary key,
  address_type varchar(20) not null,
  userId int references Accounts(userId) not null,
  addr1 varchar(100) not null,
  addr2 varchar(100) null,
  city varchar(100) not null,
  state varchar(20) not null,
  zip varchar(12) not null,
  country varchar(20) not null
);
 
create table LineItems
(
  lineItemId serial primary key,
  itemId int references Items(itemId) not null,
  quantity int not null
);
 
create table Carts
(
  cartId serial primary key,
  userId int references Accounts(userId),
  status varchar(10) not null
);
 
create table CartLineItems
(
   cartId int references Carts(cartId),
   lineItemId int references LineItems(lineItemId),
   primary key (cartId, lineItemId)
);
 
create table Orders
(
   orderId serial primary key,
   orderDate timestamp not null,
   courier varchar(10) not null,
   totalPrice money not null,
   creditCard varchar(20) not null,
   exprDate varchar(20) not null,
   cardType varchar(20) not null,
   locale varchar(20) not null,
   status varchar(10) not null,
   cartId int references Carts(cartId) not null,
   shippingAddress int references Addresses(addressId) not null,
   billingAddress int references Addresses(addressId) not null
);