ritikprajapat21
commented
3 days ago I want to work on this issue.
Open lokesh1jha opened 3 days ago
ritikprajapat21
commented
3 days ago I want to work on this issue.
Users Table sql CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Products Table sql CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL, image VARCHAR(255), category VARCHAR(50), description TEXT, metadata JSONB, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Categories Table sql CREATE TABLE categories ( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, description TEXT );
Cart Table sql CREATE TABLE cart ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE, total DECIMAL(10, 2) DEFAULT 0.00, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Cart Items Table sql CREATE TABLE cart_items ( id SERIAL PRIMARY KEY, cart_id INT REFERENCES cart(id) ON DELETE CASCADE, product_id INT REFERENCES products(id), quantity INT NOT NULL CHECK (quantity > 0), price DECIMAL(10, 2) NOT NULL, -- Store price at time of addition to cart created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Orders Table sql CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE, date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, status VARCHAR(50), total DECIMAL(10, 2) NOT NULL );
Order Items Table sql CREATE TABLE order_items ( id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id) ON DELETE CASCADE, product_id INT REFERENCES products(id), quantity INT NOT NULL CHECK (quantity > 0), price DECIMAL(10, 2) NOT NULL -- Store price at time of order );
Payment Details Table sql CREATE TABLE payment_details ( id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id) ON DELETE CASCADE, amount DECIMAL(10, 2) NOT NULL, provider VARCHAR(50), status VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Additional Considerations Wishlist Table: If you want to implement a wishlist feature, you can add a wishlist table that links products to users. Product Reviews Table: Consider adding a table for product reviews to capture customer feedback. Shipping Addresses Table: You might also want to create a table for storing user shipping addresses.