Hi @Jake_NL your approach is a bit naive due of your lack of knowledge but that’s absolutely fine. Keep in mind that SaaS and DB queries are not easy to set up correctly . As I was lazy to type here is what “4o” generated and I found it as good enough to start with.
The only part I do not understand is
Why a user will have permissions to change content??? I this some “Questionary” or what?
Anyway, here we go.
1. Data Structure/Layout
Your current data structure, as described, is a CSV file with:
- Rows representing research questions (~900 rows)
- Columns representing companies (~8,000 columns)
- Each cell containing extensive text answers
While this flat-file approach works for small datasets, it’s not efficient or scalable for larger datasets like yours. A relational database or a document-based NoSQL database would be more appropriate.### 2. Database Choice
Relational Database (SQL):
- Pros:
- Enforces data integrity through schema and relationships.
- Powerful querying capabilities with SQL.
- Suitable for structured data with relationships.
- Examples: PostgreSQL, MySQL, SQLite
- Cons:
- Can be complex to scale horizontally.
- Schema changes can be challenging with very large datasets.
Document-based NoSQL Database (e.g., MongoDB):
- Pros:
- Flexible schema design, ideal for unstructured or semi-structured data.
- Easier horizontal scaling.
- Good for storing nested data (e.g., JSON documents).
- Cons:
- No enforced relationships, which can lead to data inconsistency.
- Complex querying can be less efficient compared to SQL databases.
Given your use case, a relational database might be more appropriate due to the structured nature of your data (questions and companies).
2. No-Code Backend Development Platforms/API Tools
Several no-code and low-code platforms can help you build and manage your backend without extensive coding:
- Airtable:
- User-friendly interface for database management.
- API for integration with Webflow.
- Best for simple to moderately complex databases.
- Zapier:
- Excellent for automating workflows between apps.
- Can be used to connect Webflow with your database.
- Retool:
- Powerful for building internal tools.
- Can connect to various databases and APIs.
- Allows building custom interfaces for data management.
- Firebase:
- Backend-as-a-Service (BaaS) offering real-time databases and APIs.
- Easy integration with web and mobile apps.
- Best for real-time data syncing and NoSQL structures.
Recommended Setup
- Database: Use PostgreSQL or MySQL for your relational database needs. These databases offer strong support for complex queries and data integrity, which will be beneficial given your structured dataset.
- Backend Management: Use Retool for creating internal tools to manage and update your data. Retool can connect directly to your database and provide a user-friendly interface for CRUD operations.
- Integration with Webflow: Use Zapier to automate the data fetching and updating process between Webflow and your database. Alternatively, consider using Webflow’s native integrations if they meet your needs.
Example Workflow
- Data Entry/Management:
- Use Retool to create an interface for managing research questions, companies, and answers.
- This interface will write directly to your PostgreSQL/MySQL database.
- Data Fetching:
- Webflow makes API calls to your database via an intermediary service (e.g., a custom Node.js/Express API hosted on Heroku or AWS Lambda).
- This service fetches the relevant data for the selected company and returns it to Webflow.
- Data Updating:
- Users edit data on the Webflow site.
- Webflow triggers an API call (via Zapier or a custom service) to update the database with the new data.
Conclusion
By transitioning to a relational database structure and leveraging tools like Retool and Zapier, you can create a scalable and efficient backend for your SaaS/webapp. This setup will support dynamic data fetching and updating while maintaining data integrity and performance.
PART TWO
Since you’re developing a SaaS application, you should include a User table to manage user information and permissions. This table will help you track who is accessing the data and control their access rights.
Database Schema
1. Companies Table:
company_id (Primary Key)company_name- Other company-specific fields
2. Questions Table:
question_id (Primary Key)question_text
3. Answers Table:
answer_id (Primary Key)company_id (Foreign Key)question_id (Foreign Key)answer_text
4. Users Table:
user_id (Primary Key)usernameemailpassword_hashrole (e.g., admin, editor, viewer)- Other user-specific fields
5. UserPermissions Table (if you need fine-grained permissions):
user_id (Foreign Key)company_id (Foreign Key)permissions (e.g., read, write)
Relational Database Structure
-
Companies Table:
company_id (Primary Key)company_name
-
Questions Table:
question_id (Primary Key)question_text
-
Answers Table:
answer_id (Primary Key)company_id (Foreign Key)question_id (Foreign Key)answer_text
-
Users Table:
user_id (Primary Key)usernameemailpassword_hashrole (e.g., admin, editor, viewer)
-
UserPermissions Table (Optional):
user_id (Foreign Key)company_id (Foreign Key)permissions (e.g., read, write)
Updated Workflow
-
Data Entry/Management:
- Use Retool to create an interface for managing research questions, companies, answers, and users.
- This interface will write directly to your PostgreSQL/MySQL database.
-
User Authentication and Authorization:
- Implement user authentication (e.g., using JWT tokens or sessions) and authorization to control access to the data.
- Use a library like Passport.js for Node.js if you’re using a custom backend.
-
Data Fetching:
- Webflow makes API calls to your backend service (e.g., a Node.js/Express API).
- This service fetches the relevant data for the authenticated user and returns it to Webflow.
-
Data Updating:
- Users edit data on the Webflow site.
- Webflow triggers an API call (via Zapier or a custom service) to update the database with the new data, respecting user permissions.
Example User Table Entry
| user_id |
username |
email |
password_hash |
role |
| 1 |
john_doe |
john@example.com |
$2a$10$CwTycUXWue0Thq9StjUM0u |
admin |
| 2 |
jane_doe |
jane@example.com |
$2a$10$7VdIHnSxjTmsq3AEZG8LR. |
editor |
Example UserPermissions Table Entry
| user_id |
company_id |
permissions |
| 1 |
101 |
read, write |
| 2 |
102 |
read |
Backend Platforms and Tools
@Stan note: These following options are just pure base info and you can use other tools as eg. Supabase has comprehensive Authentication and User permissions included etc.
Authentication and Authorization:
- Auth0: Easy to integrate, supports various authentication methods, and scales well.
- Firebase Authentication: Simple integration, especially if you use Firebase for other backend services.
Database Management:
- Retool: For building internal tools and admin panels.
- Supabase: An open-source alternative to Firebase, with integrated authentication and database management.
Integration with Webflow:
- Zapier: For simple integration tasks.
- Custom Backend: A Node.js/Express API hosted on a platform like Heroku or AWS Lambda for more control and flexibility.
By including a User table and handling user authentication and permissions, you can ensure your SaaS application is secure and scalable, providing appropriate access to users based on their roles and permissions.
NOTE: I personally will do not create SaaS in WF
m2c
