Is deriving a value using multiple joins a good idea? I am working on a university project. I need to create a database from conceptual to implementation. That allows customers to create an order with multiple products the specification says that we should store the final cost.

I am having a few issues with this part of the specification, and the lecturer is not forthcoming with a definitive answer.


  Should this be derived from the total products in the order, or should it be entered in by the application logic when the order is complete?


Can anyone shed some light on how to go about this approach. Because currently I have made the assumption that because it says "database driven application" the whole logic needs to be provided by the DBMS. And I have created Views to achieve the joining of tables, because triggers seemed harder to ensure the integrity of the data.
If the specification states that you should store the final cost then you should store the final cost (even if this is is not normal form) but it is not saying anything about how you compute that cost before storing it.

If you are proposing that you compute the price each time you look at it you need to carefully consider if the base data could change over time. If a product can change price you would need to keep a full audit of the prices over time so you could find the right price when deriving the total. If you are maintaining such an audit already then this is "free" but if not it could be a significant amount of extra work to save storing the derived value at the point of sale. If course you could add an extra Entity between order and Product which stores the price paid for that particular product in that particular sale, which means you could easily implement ad-hoc price changes such as temporary "manager's special"s or one-off discounts.

To answer the direct question from your title: finding a values with many joins is fine as long as appropriate index structures are present to make this process efficient. Whether you are best doing this in the database or another layer of your application is subject to much debate and depends on your overall design philosophy and efficiency (follow the design philosophy to be consistent, except where that has significant impact on efficiency (though at this point question the philosophy too instead of just skirting around it this once)). It is probably more efficient for a low-load use case to have the database due the work as this is the sort for thing SQL as a query language was created for and the DB has all the information at its fingertips. For working at larger scales though pushing logic to the outer layers can be preferable because (in a DB server plus web server example) it is easier to scale the web server (a web far is relatively easy to create) than the database side (where you have a whole family of new issues like whether you need master/master replication or one of the other many options, and whichever option you chose how due you make sure every node is in sync at any given time and how much of a hit is acceptable to take to ensure full transactional consistency over all the DB nodes, is "eventually consistent" good enough, and so forth). As the DB side can be ore complex to scale out, there is a strong case for pushing as much logic as possible to outer layers and letting the DB be in charge of nothing more than data storage/retreval and core constraint policing (this also makes it easier to migrate between DB platforms if needed in future).

With regard to triggers, consider their flexibility against the fact that they can be harder to debug as they are essentially things that happen behind your code's back. They have their place, you need to decide if your project is one of those places. Stored procedures for saving are another option, as is moving the logic to another layer of the application.

Your lecturer is being deliberately cagey about your query, and I've made an effort to not give any straight answer but instead listed some "things to consider" (there may be more "things to consider", please try to think about it), because he/she wants you to come to a design decision by yourself to prove that you have an understanding of the general field rather than just an ability to follow specific instructions. Don't worry too much as long as you can offer some justification for the choices you make - in an academic test you will get marks for giving a less-than-optimal solution if you can somehow justify the choices you made and any you rejected. Be careful to state the assumptions you made and factors you thought of but discounted as not relevant to this implementation - if your assumptions are wrong then you'll still get some credit for a reasonable choice based on those iffy assumptions, if you make a bad choice because of bad assumptions (that would be good if the assumptions were correct) and don't document the assumptions you'll get neither half of the credit as the marker can't tell why you made the choice (bad input assumptions or just simple bad thinking).

Some lecturers are clue up enough to scan sites like this to see if people are asking questions about their assignments. Don't be tempted to try get an answer from here that you can just copy+paste as you'll get zero marks (or worse!) if it is noticed that is what you have done.