Title: To place a business logic in server side
Question: How to store a business objects in database tables?
Answer:
The devlopment of large projects is hard work. Especially when our clients are remote/mobil users.Also in our life exists the task which are not stay on one place and which algorithm changes in time. In these situations we must to develop the multi-tier application and store the business logic in server-app level. But for some tasks this way is very expensive. So I prefers the some "2 1/2"-tier level when part of business logic we can store in database. I'll try to describe this my opinion.
On server database I have a few own "system" tables with descriptions of project:
1. CLASS table is a list of all available user queries with short
description, select/update/insert/delete statements
2. DESCRIPTION table stores the descriptions of fields for each record in CLASS
3. a few additional tables with security (user, groups, privileges, access modes etc)
PS I do not describe them, as for the basic description they are not so important
In application I have a main datamodule, which contains the
one TDatabase component (linked to my BDE alias or ADO connection).
Also I have a some TDataModule component which are the parent for each other. On this component I dropped the TQuery (linked to main TDatabase), and TUpdateSQL and TDataSource, which are linked to TQuery.
Also in this parent datamodule I included the some additional properties and procedures/functions:
1. to get a descriptions from server by class name
2. to fill a update/delete/insert properties of TUpdateSQL
3. to change a filter (via where clause), a data order (via order by) etc (any clause in sql - I use a some macros in own sqls)
4. post/cancel changes in server database, refresh data
5. to get the new ID (for autoincremental fields)
6. to read a user privileges
7. to open class
8. to get a "lookup" class
PS: I not use a delphi lookup fields. Instead it I store in class descriptions for some fields the additional info (which class I must open for edit/view of data, which fields are linked in "lookup" class etc and more)
9. and more additional features
Each datamodule are successor of this parent DM with "personal" extension in business logic. For example, in the class for details of orders I defined the specific calculations (some subtotals and sums) or procedures (check of outputing qnt). The full logic I included in these DMs only (in visual forms
I use a calls only!).
Also I have a basic visual TForm with grid, navigator, filter panel etc.
This form have a "linked" DataModule. Also this form known how:
1. to fill the info from DM (a columns list for dbgrid (from description in DM), form caption and more)
2. to call a form for edit/append of record (those form not uses the any DB-components!!)
3. to generate report (I use a SMReport)
4. to export data (I use a SMExport)
5. to find a record by some criteria
6. to select a records (the some forms I use for selecting of some recors)
PS: for example, in orders mode my users can select a records from product class and append the wished
7. to open a "lookup" class
PS: for example, in grid with customer list my users can open a class of customer types (instead standard delphi lookup field) or "drill" in orders list for this customer
8. to setup the visualization of data (fonts, colors, order etc, filter, sort and grouping)
9. and more additional features
All the rest forms inherits from this parent and are different one from other in needed possibilities only. For example, if my users want to calculate a sum of some order for specific product type only, of course, I add a calc function in linked DM and drop a button on form for calling of this function in DM
PS: in visual forms (when I need to calc or to do a something) I call the "assigned" method in "assigned" DM.
Also I have a two list - opened DMs and created forms. So if I need open a "lookup" class, I search it in list of DM and create only if I not found.
And with forms I work similar.
Using this schema I can devide the project from my "2 1/2"-tier level on multi-tier in few simple operations (because the all business logic I store in database on db-server and mine DM-classes. The visual forms not contains the logic - only visualization).
PS: this technology I uses in tens projects (current and finished), in small app and in big projects. I described the customers-orders-products schema for availability of understanding only. Of course, sometimes (more often in the beginning of development of small app) the perspective of the extension of functionality up to large client-server
system (and especially multi-tier) is not visible and it's possible to go on easy way - drop on form the ttables (or even to allocate them in separate datamodule), linked it with grids, use a master-detail link and lookup fields etc
But when you decide to expand possibilities of the app and transfer it from local DB (Paradox/DBase/Access etc) on normal DB server and maybe use a 3-tier you will understand, that is necessary to change the approach to DB programming for rise of productivity, easy support and extension of project.
Of course, it's my opinion only, but I have come to such technology by many cuts and tries during 10 years on different DBs and tools of development. Though I still do not have also thirty years I have a large number of successful developments in other departs and if I can to someone reduce this long way, it will be well.
I do not apply for indisputable true and I know many weak places
in the described technology, but they not impasses - it is simply
not up to the end are realized (and it's good:)))
I'll read criticism of other with pleasure too.