The Application Database Design

David AsaoluMarch 4, 2025About 3 min

The Application Database Design 관련

How to Build a Social Learning Platform using Next.js, Stream, and Supabase

Social media and real-time communication have transformed how people interact, making it easier to share ideas, collaborate, and learn from others, regardless of location. From professional networks to online study groups, these platforms allow vario...

How to Build a Social Learning Platform using Next.js, Stream, and Supabase

In the previous section, we created two database tables: instructors and students, which store instructors and students separately. Instructors can also upload headshot images to Supabase Storage.

In this section, you'll learn how to create these tables, define their access policies, and retrieve or modify data within the tables.

Announcements (data type)	Instructors (data type)	Students (data type)
`id` (int8)	`id` (uuid)	`id` (uuid)
`created_at` (timestamptz)	`created_at` (timestamptz)	`created_at` (timestamptz)
`author_name` (text)	`name` (text)	`email` (text)
`interest` (text)	`email` (text)	`name` (text)
`author_title` (text)	`occupation` (text)	`interest` (text)
`author_id` (uuid)	`bio` (text)	`following_list` (uuid[])
`content` (text)	`url` (text)
`likes` (uuid [])	`interest` (text)
`author_image` (text)	`image` (text)
	`followers` (uuid[])

Note

The instructors table includes an image column that stores the instructor's headshot URL. You can obtain this by creating a Supabase bucket named headshot and uploading the image when the instructor signs up.

The instructors and students tables have two primary keys: id and email.

Supabase allows you to define policies for your tables, controlling the operations different users can perform within the application.

Next, let’s create the access policies for each table.

Access Policy for the Announcements Table

The announcements table has four access policies:

ALTER POLICY "Enable delete for users based on user_id"
ON "public"."announcements"
TO public
USING (
  ((SELECT auth.uid() AS uid) = author_id)
);

ALTER POLICY "Enable insert for authenticated users only"
ON "public"."announcements"
TO authenticated
WITH CHECK (true);

ALTER POLICY "Enable read access for all users"
ON "public"."announcements"
TO public
USING (true);

ALTER POLICY "Enable update for authenticated users"
ON "public"."announcements"
TO authenticated
USING (
  (auth.role() = 'authenticated'::text)
);

Access Policy for the Instructors Table

The instructors table has three policies:

ALTER POLICY "Allow only authenticated users"
ON "public"."instructors"
TO authenticated
USING (
  (auth.role() = 'authenticated'::text)
);

ALTER POLICY "Enable insert for authenticated users only"
ON "public"."instructors"
TO authenticated
WITH CHECK (
  true
);

ALTER POLICY "Enable read access for all users"
ON "public"."instructors"
TO public
USING (
  true
);

Access Policy for the Students Table

The students table has three access policies:

ALTER POLICY "Enable insert for authenticated users only"
ON "public"."students"
TO authenticated
WITH check (
  true
);

ALTER POLICY "Enable update for only authenticated users"
ON "public"."students"
TO authenticated
USING ((auth.role() = 'authenticated'::text))

ALTER POLICY "Read access for only authenticated users"
ON "public"."students"
TO authenticated
USING (
  true
);