RdsDiagram
Consumer_info
consumer id: INTEGER
First Name: CHARACTER(255)
Last Name: CHARACTER(255)
Gender: BOOLEAN
DOB: DATE
email: CHARACTER(255)
Address- street: CHARACTER(50)
Address- city: CHARACTER(24)
Address- house: CHARACTER(4)
Address- other: CHARACTER(50)
notification preference: INTEGER
app version: CHARACTER(6)
Phone: CHARACTER(12)
Rewards
reward id: INTEGER
Description: VARCHAR(1500)
Redemption Points: INTEGER
Reward Pic ref: VARCHAR(520)
enabled: BOOLEAN
# of rewards remaining: INTEGER
expiry Date: DATE
expiry time: TIME
Consumer_rewards_history
consumer id: INTEGER
reward id: INTEGER
Redemption Date: DATE
Redemption time: TIME
Consumer_rewards_and_coupons_stats
consumer rewards and coupons stats: INTEGER
consumer id: INTEGER
# of rewards recieved -lifetime: INTEGER
Current Reward Points: INTEGER
# of reward points redeemed -lifetime: INTEGER
# of reward points earned -lifetime: INTEGER
# of coupons redeemed- lifetime: INTEGER
# of coupons recieved: INTEGER
# of coupons definitely viewed: INTEGER
Consumer_purchase_history
consumer id: INTEGER
Purchasable Item Id: INTEGER
Purchased Item Retailer ID: INTEGER
Purchase Date: DATE
Purchase Time: TIME
Purchasable Items
Purchasable Item Id: INTEGER
Item Purchased Category: CHARACTER(255)
Item Purchased Subcategory: CHARACTER(255)
Item purchased price: DECIMAL(0,0)
Retailer_Info
Retailer Id: INTEGER
Retailer Name: CHARACTER(255)
Retailer Address- street: CHARACTER(255)
retailer address- house: CHARACTER(255)
retailer address- other: CHARACTER(255)
retailer address GPS coordinates: INTEGER
retailer logo pic ref: VARCHAR(520)
Retailer Contacts
Retailer Id: INTEGER
Name: CHARACTER(255)
Phone: CHARACTER(10)
email address : CHARACTER(255)
Primary : BOOLEAN
Retailer Item Inventory
Retailer Id: INTEGER
Purchaseable Item ID: INTEGER
Purchasable Item Total Count: INTEGER
Purchaseable item current count: INTEGER
Consumer_purchase_stats
consumer id: INTEGER
purchaseable item id: INTEGER
purchased item retailer id:: INTEGER
# of item purchased - total lifetime: INTEGER
# of items purchased- last year: INTEGER
# of items purchased- last 6 months: INTEGER
# of items purchased - last month: INTEGER
# of items purchased- last week: INTEGER
Consumer Retailer Preference
consumer id: INTEGER
consumer preferred retailer id: INTEGER
prefer offers from retailer: BOOLEAN
Consumer Item Category preference
consumer id: INTEGER
consumer preferred item category: CHARACTER(255)
prefer offers for category: BOOLEAN
consumer preferred item sub category: CHARACTER(255)
Offers
offer id: INTEGER
offer description: VARCHAR(1000)
offer type: CHARACTER(50)
offer creation date: DATE
offer discount %: INTEGER
offer expiry relative date: INTEGER
offer expiry fixed date: DATE
offer expiry redemption count: INTEGER
offer item category: CHARACTER(255)
offered item or items category pic reference: VARCHAR(520)
offer coupon text: VARCHAR(1000)
retailer id: INTEGER
enabled: BOOLEAN
offer item id: INTEGER
Retailer_enabled_offers
retailer id: INTEGER
offer id: INTEGER
Coupons
coupon id: INTEGER
coupon text: VARCHAR(1000)
coupon QR code: INTEGER
coupon barcode: INTEGER
coupon pic reference: VARCHAR(500)
coupon expiry date: DATE
coupon retailer id: INTEGER
valid: BOOLEAN
Consumer_coupons_history
consumer id: INTEGER
coupon id: INTEGER
Redemption Date: DATE
Redemption time: TIME
Enabled Rewards
reward id: INTEGER
Consumer Valid Coupons
coupon id: INTEGER
consumer id: INTEGER