Having said that, I just remembered there's a common practice in
designing database that I could never done in any ORM (not only
django's), and that is to have a group of fields as primary key. Ie:
Company table
- company id (pk)
- name
Employee
- company id (pk)
- employee id (pk)
- employee name
EmployeeExpense
- company id (pk)
- employee id (pk)
- expense id (pk)
- amount
The reason of why doing that, is to keep the query faster (use less
join), simpler, can be grouped easily (get sum of employee's expense
amount per company), sometimes can avoid a database query in logic (ie:
expense id sequence format => "company_id-employee_id-running_Number"),
and useful in history/auditing-style table where changes in foreignkey
must not propagate (ie: EmployeeExpenseHistory table keep employee id
and expense id the way it originally inserted no matter if the employee
has been deleted etc).
Evidently, since pk always one field, above design translates into:
Company table
- company id (pk)
- name
Employee
- id (pk)
- employee id (pk)
- employee name
- company id (FK)
=> unique(company id, employee id)
EmployeeExpense
- id (pk)
- expense id (pk)
- amount
- company id (FK)
- employee id (FK)
=> unique(company id, employee id, expense id)
Where as we can see, requires dummy id as primary key (ie: on Employee
table) since it is possible having the same employee id but for
different company, and still require a unique index to enforce
uniqueness of the logical key. This design will quickly become unwieldy
for big tables especially the more deeper relationship tables (ie
EmployeeExpenseDetail) because of the table joining (that's why adding
company id in EmployeeExpense helps).
But what is good about it does not translate equally well to the
framework. The orm framework (and everything that depends on it) would
become much more complex if it supported multiple pk, and since most
people seems to be quite content with having 1 pk, then who cares?
Dody Suria Wijaya wrote:
My approach is, if you would often be needing to get list of news both
by company and user, for performance reason I would put both
foreignkey (one 2 many) to company and user in newsposting model. That
way, the resulting join query only need to join maximum of two tables
and be faster. Although this requires some codes (maybe in
_pre/post_save) to keep the user field is consistent with the company
field.
Otherwise, #1 is better since as Adrian said, you could simply do
newspostings.get_list(user__company__id__exact=123).
