Seeking Advice on PK Naming Conventions for a Multi-tenant Hybrid System (Web + Offline Sync)

Using Django Using the ORM
1

Hi everyone,

I am architecting a Student Management System (SMS) using Django 6.x as the backend and PostgreSQL as the primary database. The project has a unique requirement: it serves both Web users (via sub-domains) and Offline clients (using MS Access via a central REST API).

We are using UUIDs for all primary keys to ensure data integrity during offline-to-online synchronization. Currently, I am debating the naming convention for Primary Keys (PK) to avoid confusion and conflicts during complex joins and API payloads.

I would love to get the community’s insight on which PK naming convention would be most maintainable in the long run, especially when considering Foreign Key (FK) referencing.

The options I am considering:

  • id (The Django Default): Standard convention, but can lead to ambiguous column names in raw SQL joins.

  • {table}_id (e.g., student_id): Matches the FK name in related tables, making joins explicit and API payloads clearer.

  • id_{table} (e.g., id_student): Less common, but keeps all ID columns grouped together in alphabetical views.

  • {table}_pk (e.g., student_pk): Explicitly identifies the primary key, but differs from the FK name (student_id).

My context:

  • Multi-tenancy: All tables include an institute_id (FK).

  • Hybrid Sync: MS Access clients push and pull data. Using student_id as the PK appears to align well with FK naming and sync logic, but I’m curious whether this introduces unseen issues within Django.

  • Scalability: We plan to integrate an LMS and a Mobile App in the future.

My questions:

  1. From a Django perspective, does overriding the default id with {table}_id (using primary_key=True) cause significant friction with Django internals or third-party packages?

  2. Which convention is most effective at preventing ambiguous column issues during complex reporting and cross-database syncing?

  3. Is there a performance or best-practice reason to prefer the generic id even in a multi-platform, offline-capable environment?

Looking forward to your expert opinions and real-world experiences.

2

Welcome @mitsbd !

This is far from being a unique requirement. This type of joint access is quite common among organizations for whom data is a global-shared resource.

If you’re doing raw SQL joins, you should be qualifying the names anyway from the perspective of clarity of the query. Using id as the name doesn’t create any additional issues. (e.g. If your “student” table has a primary key “student_id”, and some other table “class” has a foreign key named “student_id”, then your join will need to be written as student.student_id = class.student_id. I don’t see where this helps anything.)

It also has the advantage of being consistent. You know what your primary key field name is regardless of the table being referenced. Keeping it the same actually reduces the likelihood of certain types of errors caused by accidentally joining on non-key fields.

Additionally, you may have another issue, where you might want to track a specific attribute named “something_id”, such as a student ID number. In this case, you might want to have a field named student_id that is not the primary key (and it shouldn’t be.) That type of situation - regardless of the table - can require workarounds that lessens the value of the convention.

No.

I don’t think it matters, provided the convention is made explicitly clear.

Performance? No. Best-practice? :person_shrugging: I’ve never encountered an issue with using id as the primary key column name, so I’m not sure how changing the name is going to improve the situation.