How to structure the database in Bubble

Bubble's database runs on PostgreSQL. A Data Type is a table, a Thing is a row, and a Field is a column. Every record automatically gets: Unique ID (32-character, immutable), Created Date, Modified Date, and Slug. The User Data Type is auto-created with email and password handling. You can have up to 1,000 custom Data Types. Key differences from traditional databases: searches auto-update in real-time on page elements, text search indexes only the first 256 characters, and the database processes approximately 100 rows per second. Deleting a field leaves the underlying data in the database.

In Bubble, relationships are created by adding a field of another Data Type. One-to-many: add a field of the parent type on the child. Example: a Task has a project field (type: Project) — each task belongs to one project, but a project has many tasks. To find all tasks for a project, search Tasks where project = the specific Project. Many-to-many: add a list field. Example: a Course has a students field (list of Users) or a Student has an enrolled_courses field (list of Courses). For large many-to-many relationships, create a junction Data Type (Enrollment with student and course fields) to avoid list size limits and enable extra metadata.

In traditional databases, normalization avoids data duplication. In Bubble, selective denormalization improves performance dramatically. Example: instead of searching Reviews and calculating average_rating on every profile page load (expensive), store average_rating as a field on the User record and update it when a new review is created. Other denormalization candidates: item counts (store comment_count on Post instead of counting with a search), computed totals (store order_total instead of summing line items), and latest values (store last_activity_date instead of searching for the most recent event).

Option Sets are static, developer-managed lists stored as part of your app's code — they load instantly with zero workload unit cost. Data Types are dynamic database tables with privacy rules, user-generated content, and search capabilities. Use Option Sets for: statuses (Active, Inactive, Pending), categories (Electronics, Clothing, Food), roles (Admin, Member, Viewer), countries, colors, and any list that does not change per user. Use Data Types for: user-generated content, records that need privacy rules, data that users create/edit/delete, and anything requiring relationships to other database records.

SaaS pattern: User → Organization (many-to-many via Membership junction with role field) → Project → Task. Store the user's current_organization on User for quick access. Marketplace pattern: User with role (Buyer/Seller), Product → Order → OrderItem (junction between Order and Product with quantity and price). Social pattern: User → Post → Comment, Following (junction between two Users: follower and followed), Like (junction between User and Post). For all patterns, add created_by (User) and created_date fields on every Data Type for audit tracking.