Database Setup

Schema

Above image shows the Schema of the Database for the Store App.

The Store Database is made up of the following tables that have relationship with each other as shown in the above Schema -

• "item" Table.
  • Stores the Product information.
• "item_category" Table.
  • Stores a list of Categories that can used to categorize a Product.
• "item_image" Table.
  • Stores the Images of a Product.
• "item_attr" Table.
  • Stores the Additional Attributes information of a Product.
• "supplier" Table.
  • Stores the Supplier information.
• "contact_type" Table.
  • Stores the different Contact types of a Supplier Contact information.
• "supplier_contact" Table.
  • Stores the contact information of a Supplier.
• "item_supplier_info" Table.
  • Stores the Supplier's listed Price for a Product.
• "item_supplier_inventory" Table.
  • Stores the Supplier's available to sell quantity for a Product.

CREATE TABLE queries used for Database setup

Products Table (item)

CREATE TABLE item 
(_id INTEGER PRIMARY KEY AUTOINCREMENT, 
item_name TEXT NOT NULL, 
item_sku TEXT NOT NULL, 
item_description TEXT NOT NULL, 
category_id INTEGER, 
CONSTRAINT unique_item_sku UNIQUE (item_sku) ON CONFLICT FAIL, 
CONSTRAINT fk_category_id FOREIGN KEY (category_id) REFERENCES item_category(_id)
)

Category Table, for Products's Categories (item_category)

CREATE TABLE item_category
(_id INTEGER PRIMARY KEY AUTOINCREMENT, 
category_name TEXT NOT NULL, 
CONSTRAINT unique_category_name UNIQUE (category_name) ON CONFLICT FAIL
)

Product's Images Table (item_image)

CREATE TABLE item_image
(item_id INTEGER, 
image_uri TEXT, 
is_default INTEGER NOT NULL DEFAULT 0, 
CONSTRAINT unique_image_uri UNIQUE (item_id, image_uri), 
CONSTRAINT fk_item_id FOREIGN KEY (item_id) REFERENCES item(_id) ON DELETE CASCADE
)

Product's Additional Attributes Table (item_attr)

CREATE TABLE item_attr
(item_id INTEGER, 
attr_name TEXT NOT NULL, 
attr_value TEXT NOT NULL, 
CONSTRAINT unique_attr_name UNIQUE (item_id, attr_name), 
CONSTRAINT fk_item_id FOREIGN KEY (item_id) REFERENCES item(_id) ON DELETE CASCADE
)

Suppliers Table (supplier)

CREATE TABLE supplier
(_id INTEGER PRIMARY KEY AUTOINCREMENT, 
supplier_name TEXT NOT NULL, 
supplier_code TEXT NOT NULL, 
CONSTRAINT unique_supplier_code UNIQUE (supplier_code) ON CONFLICT FAIL
)

Contact Types Table, stores the types of Contact that can be recorded (contact_type)

CREATE TABLE contact_type
(_id INTEGER PRIMARY KEY, 
type_name TEXT NOT NULL, 
CONSTRAINT unique_type_name UNIQUE (type_name)
)

Supplier's Contact Information Table (supplier_contact)

CREATE TABLE supplier_contact
(contact_type_id INTEGER, 
contact_value TEXT NOT NULL, 
is_default INTEGER NOT NULL DEFAULT 0, 
supplier_id INTEGER, 
CONSTRAINT unique_record UNIQUE (supplier_id, contact_value) ON CONFLICT REPLACE, 
CONSTRAINT fk_contact_type_id FOREIGN KEY (contact_type_id) REFERENCES contact_type(_id), 
CONSTRAINT fk_supplier_id FOREIGN KEY (supplier_id) REFERENCES supplier(_id) ON DELETE CASCADE
)

Supplier's Products Table with Price information (item_supplier_info)

CREATE TABLE item_supplier_info
(item_id INTEGER, 
supplier_id INTEGER, 
unit_price REAL NOT NULL DEFAULT 0.0, 
CONSTRAINT unique_record UNIQUE (item_id, supplier_id) ON CONFLICT REPLACE, 
CONSTRAINT fk_item_id FOREIGN KEY (item_id) REFERENCES item(_id) ON DELETE CASCADE, 
CONSTRAINT fk_supplier_id FOREIGN KEY (supplier_id) REFERENCES supplier(_id) ON DELETE CASCADE
)

Product's Table with its listed Suppliers and inventory information (item_supplier_inventory)

CREATE TABLE item_supplier_inventory
(item_id INTEGER, 
supplier_id INTEGER, 
available_quantity INTEGER NOT NULL DEFAULT 0, 
CONSTRAINT unique_record UNIQUE (item_id, supplier_id) ON CONFLICT REPLACE, 
CONSTRAINT fk_item_id FOREIGN KEY (item_id) REFERENCES item(_id) ON DELETE CASCADE, 
CONSTRAINT fk_supplier_id FOREIGN KEY (supplier_id) REFERENCES supplier(_id) ON DELETE CASCADE
)

Index on the Inventory column of 'item_supplier_inventory' Table

CREATE INDEX quantity_idx ON item_supplier_inventory (available_quantity)