Schema/API Design comments (Salif) #3
Description
1.Fields like first_name and name in tables like roommate and chore are currently allowed to be null. It might make more sense to make these fields required to prevent any incomplete or inconsistent records from sneaking into the database.
2.Adding unique constraints to key fields, like email in the roommate table, would prevent duplicate entries. This would ensure each user’s email is unique, which will keep the data clean and make user identification straightforward.
3.The enums in schema.sql (like status_enum and frequency_enum) should enforce strict constraints. Right now, any value can technically slip in, which could lead to inconsistency. Tightening this up to only allow valid, pre-defined values will help keep the data accurate.
4.Adding ON DELETE CASCADE to foreign key relationships would automatically remove any associated data when a primary record is deleted. This would avoid leaving orphaned data in the database that doesn’t relate to anything.
5.Using more descriptive column names like roommate_id or chore_id instead of generic id names can make it much easier to read and understand complex queries at a glance.
6.Adding indexes on frequently queried fields, like status in the chore_assignment table, could improve search performance. This would be especially helpful if the data grows over time, making queries faster and more efficient.
7.Keeping the primary key type consistent across tables (like using bigint everywhere or switching to UUIDs) would make scaling easier, especially if there’s a plan to expand the system in the future.
8.Renaming some endpoints for better alignment with RESTful standards could make the API more intuitive. For example, renaming GET /get_chore to GET /chores gives a more standard and predictable interface.
9.For fields that are optional, setting a default value instead of using NULL could simplify the data handling process. This would reduce the need for extra checks in the code and make working with the data smoother.
10.Implementing a consistent response format across all endpoints will make responses predictable and easier to work with. This is especially useful for developers using the API, as they’ll know exactly what to expect.
11.Adding support for batch operations, like a POST /chores/batch endpoint, would make it easier for clients to add or update multiple records at once. This would be more efficient than sending individual requests for each record.
12.Make sure date fields are timezone-aware to avoid any potential confusion or inconsistency for users across different time zones. It’s an easy way to make sure all data is consistent no matter where it’s accessed from.
13.Adding pagination to list endpoints (like GET /chores or GET /bills) would improve performance for both the server and clients, especially as data scales. This will keep responses manageable for large datasets.
14.Versioning the API (like using v1/chores) would allow for smoother updates in the future without breaking existing clients. It’s a best practice that signals when major changes are introduced.
15.Re-evaluating some of the join-heavy queries, especially for high-traffic endpoints like GET /get_chore, could improve efficiency. Simplifying these queries or using denormalization could reduce database load and speed things up.