Athleagues DB Diagram.xlsx

	A	B	C	D	E	F	G	H	I
1	Table	Attribute	Data Type	Null	PK	FK	Unique	Other Constraints	Description

2	users								An individual user in the system
3		id	INT	N	Y			AUTO_INCREMENT	The ID associated with the user
4		first_name	VARCHAR(50)	Y					The user's first name
5		last_name	VARCHAR(50)	Y					The user's last name
6		nickname	VARCHAR(50)	Y					The user's nick name
7		nickname_is_flagged	TINYINT	N					Indicates if another user flagged the user's nickname. 0=not flagged, 1=flagged. Defaults to 0.
8		nickname_status_id	INT	N		status.id			The message's status: active, inactive, violation
9		email	VARCHAR(255)	N			Y		The user's unique email address. Critical for fast login lookups.
10		public_email	TINYINT	N					Whether the user wants their email address visible to others at their school. 0=no, 1=yes. Default 0.
11		phone	VARCHAR(255)	Y					The user's phone number
12		public_phone	TINYINT	Y					Whether the user wants their phone number visible to others at their school. 0=no, 1=yes. Default 0.
13		password_hash	VARCHAR(255)	Y					The user's hashed password
14		avatar_url	VARCHAR(255)	Y					An optional user avatar
15		created_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the user signed up.
16		updated_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the user account was last updated.
17		deleted_at	DATETIME	Y				CURRENT_TIMESTAMP	The date and time the user account was deleted.
18								INDEX(first_name, last_name)	Administrators will frequently search for students by name.
19								UNIQUE_INDEX(email)	Critical for fast logins and preventing duplicate accounts.
20
21	user_roles								A role assigned to a user
22		user_id	INT	N	Y	users.id			The ID associated with the user
23		role_id	INT	N	Y	roles.id			The ID associated with the role
24		created_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the user role was created.
25		deleted_at	DATETIME	Y				CURRENT_TIMESTAMP	The date and time the user role was deleted.
26
27	roles								A role in the system
28		id	INT	N	Y			AUTO_INCREMENT	The ID associated with an available role.
29		name	VARCHAR(50)	N					The name of the role.
30		description	VARCHAR(255)	N					The description of the role.
31		created_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the role was created.
32		updated_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the role was last updated.
33		deleted_at	DATETIME	Y				CURRENT_TIMESTAMP	The date and time the role was deleted.
34
35	role_permissions								A permission assigned to a role
36		role_id	INT	N	Y	roles.id			The ID associated with the role
37		permission_id	INT	N	Y	permissions.id			The ID associated with the permission
38		created_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the role's permission was created.
39		deleted_at	DATETIME	Y				CURRENT_TIMESTAMP	The date and time the role was deleted.
40
41	permissions								A permission to be assigned to a role
42		id	INT	N	Y			AUTO_INCREMENT	The ID associated with an available permission.
43		name	VARCHAR(50)	N					The name of the permission.
44		description	VARCHAR(255)	N					The description of the permission.
45		created_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the permission was created.
46		updated_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the permission was last updated.
47		deleted_at	DATETIME	Y				CURRENT_TIMESTAMP	The date and time the permission was deleted.
48
49	status								Statuses of things
50		id	INT	N	Y			AUTO_INCREMENT	The ID of the status
51		name	VARCHAR(255)	N					The name of the status
52
53	schools								Colleges and Universities that have an Athleagues subscription
54		id	INT	N	Y			AUTO_INCREMENT	The ID associated with the school
55		name	VARCHAR(255)	N					The name for the school.
56		domain	VARCHAR(255)	N			Y		The school's domain, used for user registration.
57		status_id	INT	N		status.id			The school's status: active, inactive, suspended
58		logo_url	VARCHAR(255)	Y					An optional school logo
59		created_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the school signed up.
60		updated_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the school information was last updated.
61		deleted_at	DATETIME	Y				CURRENT_TIMESTAMP	The date and time the school was deleted.
62								UNIQUE(domain)	Ensures no two schools can claim the same email domain
63
64	subcriptions								An available Athleagues subscription
65		id	INT	N	Y			AUTO_INCREMENT	The ID associated with an available subscription.
66		name	VARCHAR(255)	N					The name of the subscription.
67		description	TEXT	N					The description of the subscription's features. Possibly contains HTML.
68		monthly_price	DECIMAL(7,2)	N					The monthly price of the subscription.
69		annual_price	DECIMAL(10,2)	N					The annual price of the subscription.
70		status_id	INT	N		status.id			The school's status: draft, active, inactive
71		created_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the subscription was created.
72		updated_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the subscription was last updated.
73		deleted_at	DATETIME	Y				CURRENT_TIMESTAMP	The date and time the subscription was deleted.
74
75	school_subscriptions								A history of all active and inactive subscriptions
76		id	INT	N	Y			AUTO_INCREMENT	The ID associated with a paid subscription.
77		school_id	INT	N		schools.id			The ID associated with the school.
78		subscription_id	INT	N		subscriptions.id			The ID associated with the subscription.
79		payment_id	VARCHAR(255)	N					The ID assigned from the payment processor (like Authorize.net or Stripe)
80		payment_amount	DECIMAL(10,2)	N					The amount paid
81		type	ENUM	N					Was the amount paid for a monthly or annual subscription.
82		created_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the subscription's payment was processed.
83		updated_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the subscription payment was last updated.
84		deleted_at	DATETIME	Y				CURRENT_TIMESTAMP	The date and time the subscription was deleted (refunded).
85
86	locations								A location for leagues and events.
87		id	INT	N	Y			AUTO_INCREMENT	The ID associated with a location.
88		school_id	INT	N		schools.id			The ID associated with the school.
89		parent_location	INT	Y		locations.id			The ID of a parent location (i.e. this location is Court 2, in Gymnasium 1)
90		name	VARCHAR(255)	N					The name of the location.
91		description	TEXT	Y					The description of the location. Possibly contains HTML.
92		address	VARCHAR(255)	Y					A physical address to the location. An address or latitude/longitude are required to generate a map.
93		latitude	DECIMAL(8,4)	Y					The latitude of the location. An address or latitude/longitude are required to generate a map
94		longitude	DECIMAL(8,4)	Y					The longitude of the location. An address or latitude/longitude are required to generate a map
95		status_id	INT	N		status.id			The location's status: draft, active, temporarily closed, permanently closed, coming soon
96		image_url	VARCHAR(255)	Y					An optional image of the location
97		created_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the location was created.
98		updated_at	DATETIME	N				CURRENT_TIMESTAMP	The date and time the location was last updated.
99		deleted_at	DATETIME	Y				CURRENT_TIMESTAMP	The date and time the location was deleted.
100