Author: Mike Shkolnik
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:
CLASS table is a list of all available user queries with short description,
select/update/insert/delete statements
DESCRIPTION table stores the descriptions of fields for each record in CLASS
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:
to get a descriptions from server by class name
to fill a update/delete/insert properties of TUpdateSQL
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)
post/cancel changes in server database, refresh data
to get the new ID (for autoincremental fields)
to read a user privileges
to open class
to get a "lookup" class. 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)
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:
to fill the info from DM (a columns list for dbgrid (from description in DM), form
caption and more)
to call a form for edit/append of record (those form not uses the any
DB-components!!)
to generate report (I use a SMReport)
to export data (I use a SMExport)
to find a record by some criteria
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
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
to setup the visualization of data (fonts, colors, order etc, filter, sort and
grouping)
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.
|
