On 3/10/06, Gavin <[EMAIL PROTECTED]> wrote:
Rule 1 of database design - Don't replicate data. Ever. You need to have a serious look at your requirements, and make sure that any given datum has one, and only one representation in the table. If you're looking for some pointers on the sort of representations you should be striving for, try searching for "Third Normal Form" - http://en.wikipedia.org/wiki/Third_normal_form is not a bad starting point.
In your case, I would be asking questions like:
- Will the expenditure account always be the same as the Reimbursement account? If so, don't keep the account reference twice.
- Will the reimburse_for amount always equal price*count on the related expenditure? If so, don't keep the amount separately
- Can a single order be part of multiple expenditures? If not, why does Order relate 1-n with Expenditure?
An entity-relationship model might help you sort out these details:
http://en.wikipedia.org/wiki/Entity-relationship_model
Once you have your model sorted out, you can use SQL to pull data from multiple tables, and you can use SQL to perform calcuations based upon data from multiple tables.
for example:
SELECT tableA.field1, (tableA.field1 * tableB.field2) AS total FROM tableA INNER JOIN tableB ON tableA.join_id = tableB.id
Django provides access to this sort of thing using a combination of select, where and tables clauses. Select allows you to add additional attributes to the objects you are retrieving; tables allows you to force other sources of data into a query, and where allows you to add additional constraints (such as a join constrain). The previous SQL query would end up something like:
>>> tableAs.get_list(select={'total', 'tableA.field1 * tableB.field2'}, tables='tableB', where='tableA.join_id=tableB.id')
[obj1, obj2,...]
Then, to look at your total:
>>> obj1.total
42
However - the first step is to get your data model right.
Russ Magee %-)
I can compute these values by inspecting both the Orders and Reimburse
tables. Is that the best way?
Rule 1 of database design - Don't replicate data. Ever. You need to have a serious look at your requirements, and make sure that any given datum has one, and only one representation in the table. If you're looking for some pointers on the sort of representations you should be striving for, try searching for "Third Normal Form" - http://en.wikipedia.org/wiki/Third_normal_form is not a bad starting point.
In your case, I would be asking questions like:
- Will the expenditure account always be the same as the Reimbursement account? If so, don't keep the account reference twice.
- Will the reimburse_for amount always equal price*count on the related expenditure? If so, don't keep the amount separately
- Can a single order be part of multiple expenditures? If not, why does Order relate 1-n with Expenditure?
An entity-relationship model might help you sort out these details:
http://en.wikipedia.org/wiki/Entity-relationship_model
Once you have your model sorted out, you can use SQL to pull data from multiple tables, and you can use SQL to perform calcuations based upon data from multiple tables.
for example:
SELECT tableA.field1, (tableA.field1 * tableB.field2) AS total FROM tableA INNER JOIN tableB ON tableA.join_id = tableB.id
Django provides access to this sort of thing using a combination of select, where and tables clauses. Select allows you to add additional attributes to the objects you are retrieving; tables allows you to force other sources of data into a query, and where allows you to add additional constraints (such as a join constrain). The previous SQL query would end up something like:
>>> tableAs.get_list(select={'total', 'tableA.field1 * tableB.field2'}, tables='tableB', where='tableA.join_id=tableB.id')
[obj1, obj2,...]
Then, to look at your total:
>>> obj1.total
42
However - the first step is to get your data model right.
Russ Magee %-)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/django-users
-~----------~----~----~----~------~----~------~--~---
