Schema - guanw88/EventFul GitHub Wiki
Database Schema
Needs to be able to handle:
Events, Registration / Tickets, Categories, Bookmark Events
Users
Column Name |
Data Type |
Details |
id |
integer |
not null, primary key |
username |
string |
not null, indexed, unique |
email |
string |
not null, indexed, unique |
first_name |
string |
not null |
last_name |
string |
not null |
password_digest |
string |
not null |
session_token |
string |
not null, indexed, unique |
created_at |
datetime |
not null |
updated_at |
datetime |
not null |
- Has many organized_events: (primary key: id, foreign key: organizer_id, class: Event)
- Has many tickets: (primary key: id, foreign key: ticket_id, class: Ticket)
- Has many bookmarks: (primary_key: id, foreign_key: user_id, class: Bookmark)
- Has many bookmarked_events: (through: event, source: Bookmark)
Events
Column Name |
Data Type |
Details |
id |
integer |
not null, primary key |
event_date |
date |
not null, indexed |
event_name |
string |
not null |
organizer_id |
integer |
not null, foreign key on user.id |
num_tickets_available |
integer |
not null |
start_datetime |
datetime |
not null |
end_datetime |
datetime |
not null |
address |
string |
not null |
city |
string` |
not null |
state |
string` |
not null, custom validation for only valid states |
zip |
string` |
not null, custom validation for only valid zips |
description |
text |
not null |
image_url |
string |
optional |
private_event_yn |
boolean |
not null, inclusion: true, false |
- Belongs to organizer (primary key: id, foreign key: organizer_id, class: User)
- Has many tickets (primary key: id, foreign key: event_id, class: Ticket)
- Has many event_categories (primary key: id, foreign key: event_id, class: EventCategory)
- Has many categories (through: category, source: EventCategory)
- Has many bookmarks (primary_key: id, foreign_key: event_id, class: Boomark)
Tickets
Column Name |
Data Type |
Details |
id |
integer |
not null, primary key |
event_id |
integer |
not null, indexed |
purchaser_id |
integer |
not null, indexed, foreign key on user.id |
order_no |
integer |
not null, indexed, unique |
purchase_date |
date |
not null |
purchase_price |
money |
not null |
confirmation_code |
string |
not null, indexed, unique |
- Belongs to event (primary key: id, foreign key: event_id, class: Event)
- Belongs to purchaser (primary key: id, foreign key: purchaser_id, class: User)
Categories
Column Name |
Data Type |
Details |
id |
integer |
not null, primary key |
category_name |
string |
not null |
- Has many event_categories (primary key: id, foreign key: category_id, class: EventCategories)
- Has many events (through: event, source; EventCategory)
EventCategories (Joins Table to Track Ticket Categories)
Column Name |
Data Type |
Details |
id |
integer |
not null, primary key |
event_id |
integer |
not null, indexed |
category_id |
integer |
not null, indexed |
- Belongs to event (primary_key: id, foreign_key: event_id, class: Event)
- Belongs to category (primary_key: id, foreign_key: category_id, class: Category)
Bookmarks (Joins Table to Track Bookmarked Events)
Column Name |
Data Type |
Details |
id |
integer |
not null, primary key |
user_id |
integer |
not null, indexed |
event_id |
integer |
not null, indexed |
- Belongs to user (primary_key: id, foreign_key: user_id, class: User)
- Belongs to event (primary_key: id, foreign_key: event_id, class: Event)