DATABASE DESIGN (Normalized + Modular)
──────────────────────────────
🔵 USER MODULE
──────────────────────────────
1. USERS Table
Column Name Data Type Key Description
user_id INTEGER PK. Unique ID for user
username TEXT User's full name
email TEXT UNIQUE User email address
password TEXT User password
2. BOOKMARKS Table
Column Name Data Type Key Description
bookmark ID. INTEGER PK Unique ID for the bookmark
user_id. INTEGER FK → USERS Reference to user
place_type. TEXT Type: attraction, hotel, etc.
place_id INTEGER INDIRECT FK ID of place from respective table
created_at. TEXT Timestamp of bookmark
Note: place_id is a polymorphic key (depending on place_type).
──────────────────────────────
ADMIN MODULE
──────────────────────────────
3. ADMINS Table
Column Name Data Type Key Description
admin_id. INTEGER PK Unique ID for admin
admin_name TEXT Admin's full name
email. TEXT UNIQUE Admin email address
password. TEXT Admin password
4. ATTRACTIONS Table
Column Name Data Type Key Description
attraction_id INTEGER PK Unique attraction ID
name TEXT Name of the attraction
category TEXT Historical, cultural, etc.
location TEXT Address/location
description TEXT Description/details
rating REAL Average rating
image_ref TEXT Image filename or URL
contact TEXT Contact details (phone/email)
5. RESTAURANTS Table
Column Name Data Type Key Description
restaurant_id INTEGER PK Unique ID for restaurant
name TEXT Restaurant name
location TEXT Location
description TEXT Details
rating REAL User rating
food_type TEXT Veg / Non-Veg / Multi-Cuisine
image_ref TEXT Image path/URL
contact TEXT Contact details
price_range TEXT e.g., Low, Medium, High
6. HOTELS Table
Column Name Data Type Key Description
hotel_id INTEGER PK Unique hotel ID
name TEXT Hotel name
location TEXT Address
description TEXT Hotel description
rating REAL Average user rating
image_ref TEXT Image
contact TEXT Contact number or email
price_range TEXT e.g., Budget, Premium
7. LODGES Table
Column Name Data Type Key Description
lodge_id INTEGER PK Unique lodge ID
name TEXT Lodge name
location TEXT Location
description TEXT Lodge description
rating REAL User rating
image_ref TEXT Image
contact TEXT Contact details
price_range TEXT Pricing category
8. CAFES Table
Column Name Data Type Key Description
cafe_id INTEGER PK Unique cafe ID
name TEXT Cafe name
location TEXT Location
description TEXT Cafe description
rating REAL Average rating
image_ref TEXT Image
contact TEXT Phone/email
price_range TEXT Cost range (Low/High)
