Database Schema - mhmunem/Grocery-Comparison-App GitHub Wiki

image

Database Schema Documentation

Tables

1. units

  • Description: Product information about different units.
  • Columns:
    • id (serial, primary key): Unique identifier for each unit.
    • name (text, not null): Name of the unit e.g.: L, mL, KG etc.

2. chains

  • Description: Stores information about different chains.
  • Columns:
    • id (serial, primary key): Unique identifier for each chain.
    • name (text, not null): Name of the chain e.g.: Pacn'Sav, New World etc.
    • image_logo (text, not null): URL or path to the chain's logo image.

3. stores

  • Description: Stores information about different store locations.
  • Columns:
    • id (serial, primary key): Unique identifier for each store.
    • name (text, not null): Name of the store location.
    • chainID (int, foreign key, not null): References chains.id.

4. products

  • Description: Product information.
  • Columns:
    • id (serial, primary key): Unique identifier for each product.
    • name (text, not null): Name of the product.
    • brand (text): Brand of the product e.g.: Value, Pams etc.
    • details (text): Details of the product.
    • amount (numeric, not null): Amount of the product e.g.: 1L, 5KG etc.
    • image (text): URL or path to the product's image.
    • unitID (int, foreign key, not null): References units.id.
    • categoryID (int, foreign key): References category.id.

4. store_products

  • Description: Junction table of store and product information.
  • Columns:
    • id (serial, primary key): Unique identifier for each product.
    • storeID (int, foreign key, not null): References stores.id.
    • productID (int, foreign key, not null): References products.id.
    • price (numeric): Product price at a certain store.

5. shopping_list

  • Description: Information about shopping list items.
  • Columns:
    • id (serial, primary key): Unique identifier for each shopping list item.
    • amount (numeric, not null): Amount of the product in the shopping list.
    • productID (int, foreign key): References products.id.

6. category

  • Description: Information about different product categories.
  • Columns:
    • id (serial, primary key): Unique identifier for each category.
    • name (text, not null): Name of the categories e.g.: fruits, veges, meat etc.

7. price_history

  • Description: Information about the product price at different times.
  • Columns:
    • id (serial, primary key): Unique identifier for each time.
    • price (numeric, not null): Product price at a certain store at a certain time.
    • date (date, not null): Product price record time.
    • productID (int, foreign key, not null): References products.id.

Relationships

  • stores.chainID references chains.id: Each store is associated with a chain.
  • store_products.productID references products.id: Each product is available in a store.
  • store_products.storeID references stores.id: Each product is available in a store.
  • products.unitID references units.id: Each product is measured in a unit.
  • products.categoryID references category.id: Each product is in a specific category.
  • shopping_list.productID references products.id: Each shopping list item is associated with a product.
  • price_history.productID references products.id: Each product has a price history.
⚠️ **GitHub.com Fallback** ⚠️