Create ERD diagrams for backend

[isym444]

[isym444]

1.  User
User(user_id:INTEGER, stripe_customer_id:VARCHAR(255), food_preferences:TEXT, bio:TEXT, created_at:TIMESTAMP, average_rating:DECIMAL(2,1))
(Primary Key: user_id) 
2.  Subscription
Subscription(user_id:INTEGER, tier:VARCHAR(50), status:VARCHAR(50), stripe_subscription_id:VARCHAR(255), start_date:DATE, end_date:DATE, renewal_date:DATE)
(Primary Key: user_id, start_date)
(Foreign Key: user_id references User(user_id))
3.  Host
Host(user_id:INTEGER, average_rating:DECIMAL(2,1))
(Primary Key: user_id)
(Foreign Key: user_id references User(user_id))
4.  ProCook
ProCook(user_id:INTEGER, fee_for_cooking_services:DECIMAL(10,2), average_rating:DECIMAL(2,1))
(Primary Key: user_id)
(Foreign Key: user_id references User(user_id))
5.  Dinner
Dinner(dinner_id:INTEGER, cuisine_type:VARCHAR(100), date:DATE, status:VARCHAR(50))
(Primary Key: dinner_id)
(Status ENUM Constraint: status can be 'pending', 'confirmed', 'cancelled', or 'completed')
6.  DinnerHosts
DinnerHosts(dinner_id:INTEGER, user_id:INTEGER)
(Primary Key: dinner_id, user_id)
(Foreign Keys: dinner_id references Dinner(dinner_id), user_id references Host(user_id))
7.  DinnerProCooks
DinnerProCooks(dinner_id:INTEGER, user_id:INTEGER)
(Primary Key: dinner_id, user_id)
(Foreign Keys: dinner_id references Dinner(dinner_id), user_id references ProCook(user_id))
8.  Rating
Rating(user_id:INTEGER, dinner_id:INTEGER, stars:INTEGER, comment:TEXT)
(Primary Key: user_id, dinner_id)
(Foreign Keys: user_id references User(user_id), dinner_id references Dinner(dinner_id))
9.  Payment
Payment(user_id:INTEGER, dinner_id:INTEGER, currency:VARCHAR(10), amount:DECIMAL(10,2), status:VARCHAR(50), created_at:TIMESTAMP, updated_at:TIMESTAMP, stripe_payment_id:VARCHAR(255))
(Primary Key: user_id, dinner_id, created_at)
(Foreign Keys: user_id references User(user_id), dinner_id references Dinner(dinner_id))
10. Address
Address(country:VARCHAR(100), city:VARCHAR(100), street:VARCHAR(255), post_code:VARCHAR(20), county:VARCHAR(100), house_apartment_number:VARCHAR(50))
(Primary Key: post_code)
11. HostAddresses
HostAddresses(user_id:INTEGER, post_code:VARCHAR(20))
(Primary Key: user_id, post_code)
(Foreign Keys: user_id references User(user_id), post_code references Address(post_code))
12. DinnerAttendees
DinnerAttendees(dinner_id:INTEGER, user_id:INTEGER)
(Primary Key: dinner_id, user_id)
(Foreign Keys: dinner_id references Dinner(dinner_id), user_id references User(user_id))

 

[isym444]

-- 1. Profiles Table (formerly DinnitUser) CREATE TABLE profiles ( user_id TEXT PRIMARY KEY, stripe_customer_id TEXT, food_preferences jsonb, bio TEXT, created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW(), average_rating DECIMAL(2,1), email TEXT UNIQUE, full_name TEXT, avatar_url TEXT subscription_id TEXT, is_premium BOOLEAN DEFAULT FALSE, subscription_status TEXT );

-- 2. Subscription Table CREATE TABLE subscription ( user_id TEXT, tier VARCHAR(50), status VARCHAR(50), stripe_subscription_id TEXT, start_date DATE, end_date DATE, renewal_date DATE, PRIMARY KEY (user_id, start_date), FOREIGN KEY (user_id) REFERENCES profiles(user_id) );

-- 3. Host Table CREATE TABLE host ( user_id TEXT PRIMARY KEY, average_rating DECIMAL(2,1), FOREIGN KEY (user_id) REFERENCES profiles(user_id) );

-- 4. ProCook Table CREATE TABLE procook ( user_id TEXT PRIMARY KEY, fee_for_cooking_services DECIMAL(10,2), average_rating DECIMAL(2,1), FOREIGN KEY (user_id) REFERENCES profiles(user_id) );

-- 5. Dinner Table CREATE TABLE dinner ( dinner_id SERIAL PRIMARY KEY, -- This automatically creates a sequence and sets it as default cuisine_type VARCHAR(100), date DATE, status VARCHAR(50) CHECK (status IN ('pending', 'confirmed', 'cancelled', 'completed')) );

-- 6. DinnerHosts Table CREATE TABLE dinnerhosts ( dinner_id INTEGER, user_id TEXT, guest_capacity INTEGER, price_per_guest DECIMAL(10, 2), paid_chef BOOLEAN, post_code VARCHAR(20), PRIMARY KEY (dinner_id, user_id), FOREIGN KEY (dinner_id) REFERENCES dinner(dinner_id), FOREIGN KEY (user_id) REFERENCES host(user_id) );

-- 7. DinnerProCooks Table CREATE TABLE dinnerprocooks ( dinner_id INTEGER, user_id TEXT, PRIMARY KEY (dinner_id, user_id), FOREIGN KEY (dinner_id) REFERENCES dinner(dinner_id), FOREIGN KEY (user_id) REFERENCES procook(user_id) );

-- 8. Rating Table CREATE TABLE rating ( user_id TEXT, dinner_id INTEGER, stars INTEGER CHECK (stars >= 1 AND stars <= 5), comment TEXT, PRIMARY KEY (user_id, dinner_id), FOREIGN KEY (user_id) REFERENCES profiles(user_id), FOREIGN KEY (dinner_id) REFERENCES dinner(dinner_id) );

-- 9. Payment Table CREATE TABLE payment ( user_id TEXT, dinner_id INTEGER, currency VARCHAR(10), amount DECIMAL(10,2), status VARCHAR(50), created_at TIMESTAMP, updated_at TIMESTAMP, stripe_payment_id TEXT, PRIMARY KEY (user_id, dinner_id, created_at), FOREIGN KEY (user_id) REFERENCES profiles(user_id), FOREIGN KEY (dinner_id) REFERENCES dinner(dinner_id) );

-- 10. Address Table CREATE TABLE address ( country VARCHAR(100), city VARCHAR(100), street VARCHAR(255), post_code VARCHAR(20) PRIMARY KEY, county VARCHAR(100), house_apartment_number VARCHAR(50) );

-- 11. HostAddresses Table CREATE TABLE hostaddresses ( user_id TEXT, post_code VARCHAR(20), PRIMARY KEY (user_id, post_code), FOREIGN KEY (user_id) REFERENCES profiles(user_id), FOREIGN KEY (post_code) REFERENCES address(post_code) );

-- 12. DinnerAttendees Table CREATE TABLE dinnerattendees ( dinner_id INTEGER, user_id TEXT, PRIMARY KEY (dinner_id, user_id), FOREIGN KEY (dinner_id) REFERENCES dinner(dinner_id), FOREIGN KEY (user_id) REFERENCES profiles(user_id) );

-- 13. DinnerMessages table (dinner-user-messages) -- Create the table with both foreign key references CREATE TABLE dinner_messages ( id UUID DEFAULT uuid_generate_v4() PRIMARY KEY, dinner_id INTEGER REFERENCES dinner(dinner_id) ON DELETE CASCADE, user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE, message TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL, FOREIGN KEY (dinner_id) REFERENCES dinner(dinner_id), FOREIGN KEY (user_id) REFERENCES auth.users(id) );

-- Create indexes for better query performance CREATE INDEX idx_dinner_messages_dinner_id ON dinner_messages(dinner_id); CREATE INDEX idx_dinner_messages_user_id ON dinner_messages(user_id);

[isym444]

n.b. some modifications were necessary to the tables so most up-to-date version is in the SQL queries to create the tables

[isym444]

Decided to add postcode to dinnerhosts relationship as attribute to allow hosts to host at different locations and so they don't have to necessarily give the rest of the personal data to us to store. The postcode will just help to allow the search nearby function to work. The exact address can then be posted in the event page.