Skip to content

Database Schema Recommendations

Based on a review of the models and existing migrations, here are my recommendations for database schema improvements before the initial deployment.

General Recommendations

  1. Review blank=True and null=True: Many models use null=True, blank=True on character fields (CharField, TextField). For character fields, it's generally better to use default="" and blank=True instead of null=True. This avoids having two different "empty" values (None and an empty string) for the same field. This is not a blocking issue, but it's a good practice for data consistency.

    • Affected Models: RcrainfoProfile, WasteLine (many JSONFields), Player.

  2. Abstract Base Models: The project already uses BaseModel in trak/apps/teams/models.py and pegasus/apps/employees/models.py to add created_at and updated_at fields. This is great! However, I noticed that WasteLine, WasteCode, DotLookup, Profile, RcrainfoProfile, RcrainfoSiteAccess, and UserAPIKey do not inherit from BaseModel. It would be beneficial for all models to have these timestamp fields for auditing and debugging purposes. I recommend creating a common base model in a central place (e.g., trak/apps/utils/models.py) and having all models inherit from it.

  3. UUIDs as Primary Keys: TrakUser, Invitation, Profile, and RcrainfoProfile use UUIDField as a primary key. This is a good practice, especially for models that might be exposed to the public. I recommend using UUIDs as primary keys for all new models. For existing models, it might be too much work to change them now, but it's something to consider for the future.

App-Specific Recommendations

rcra app (wasteline, manifest, rcrasite)

  1. JSONFields: The WasteLine model has many JSONFields. This is fine, but it can make querying difficult. If you find yourself needing to query based on the contents of these fields, consider creating separate models for them. For example, if you need to query for WasteLines with a specific management_method, it would be better to have a ManagementMethod model with a ForeignKey from WasteLine. Since this is the initial deployment, now is the time to make these changes.

  2. WasteCode.STATE_CHOICES: The WasteCode model has a large STATE_CHOICES list. This could be moved to a separate file or a more structured format if it becomes hard to manage. This is not a major issue, but something to keep in mind.

  3. on_delete properties: In trak/apps/rcra/wasteline/models.py the manifest field on the WasteLine model has on_delete=models.CASCADE. This means if a Manifest is deleted, all its WasteLines will also be deleted. This seems reasonable. However, in trak/apps/profile/models.py, RcrainfoSiteAccess.profile is on_delete=models.PROTECT. This will prevent the deletion of a RcrainfoProfile if it has any RcrainfoSiteAccess objects associated with it. This is a good way to prevent accidental data loss. I recommend reviewing all on_delete properties to ensure they match the intended behavior. SET_NULL is also a good option when you want to keep the object but remove the relationship.

profile app

  1. Profile and RcrainfoProfile: There are two profile models, Profile and RcrainfoProfile, with a one-to-one relationship between Profile and TrakUser, and a one-to-one relationship between Profile and RcrainfoProfile. This seems a bit complex. It might be simpler to combine Profile and RcrainfoProfile into a single model, or at least to have RcrainfoProfile have a direct OneToOneField to TrakUser. This would simplify queries and reduce the number of joins needed to get all of a user's profile information.

  2. rcrainfo_profile OneToOneField: The rcrainfo_profile on the Profile model is a OneToOneField with on_delete=models.SET_NULL. This means that if a RcrainfoProfile is deleted, the rcrainfo_profile field on the Profile will be set to NULL. However, the relationship is one-to-one, so it might be better to use on_delete=models.CASCADE and delete the Profile as well, or to use on_delete=models.PROTECT to prevent the deletion of the RcrainfoProfile if it's linked to a Profile.

teams app

  1. BaseTeamModel: The BaseTeamModel is a great way to ensure that models are associated with a team. I recommend that any new models that should be scoped to a team inherit from this model.

Summary of Recommendations

Before creating the first deployment, I recommend the following actions:

  1. Create a central BaseModel with created_at and updated_at fields and have all models inherit from it.
  2. Review null=True on CharFields and consider using default="" instead.
  3. Simplify the Profile and RcrainfoProfile models. I would be happy to help with this refactoring.
  4. Review on_delete properties on all ForeignKey and OneToOneField fields.
  5. Consider breaking out JSONField data into separate models in the WasteLine model if you need to query that data.