Good Design Question using SQL DB's?

John Griffith

Delphi Developer

Wed, 12 Nov 2003 23:54:09 GMT

Good Design Question using SQL DB's?

Where one has a table that needs to be looked at in a variety of way through
different SQL queries what is considered "good design practice?" A simple
example:

The data access components will be placed on a Data Module, i.e. TQuery.
The table is customers. The forms for viewing the data are separate forms.
The three views to be uses are:

1. select * from Customer
2. select * from Customer where OrderNo = :OrderNo order by OrderNO
3. select * from Customer where Name = :Name order by Name

Should the data module have 1, 2, or 3 TQuery components names qryCustomer,
qryCustomerByOrder and qryCustomerByName (one for each of the above
queries)?

Alternatively, should one only have one TQuery component, qryCustomer and
then dynamically change the SQL statement in each of the viewing forms?
Which is considered proper design where the goal is to isolate the UI from
the database with 3 tier in mind?

Next, where is the proper place to put the DataSource component? Should it
be on the from that has the UI or on the DataModule?

The three text books I have seem to do both with no consistency. The sample
programs also mix these. Is it strictly a matter of preference or is one
considered "good Design" and the other the work of newbies?

Your input and help would be appreciated.

Cheers!

John

Team

Delphi Developer

Thu, 13 Nov 2003 06:18:06 GMT

Re:Good Design Question using SQL DB's?

Quote

John Griffith wrote in message <3b0fd1a9$1_2@dnews>...
>Where one has a table that needs to be looked at in a variety of way
through
>different SQL queries what is considered "good design practice?" A
simple
>example:

>The data access components will be placed on a Data Module, i.e.
TQuery.
>The table is customers. The forms for viewing the data are separate
forms.
>The three views to be uses are:

>1. select * from Customer
>2. select * from Customer where OrderNo = :OrderNo order by OrderNO
>3. select * from Customer where Name = :Name order by Name

>Should the data module have 1, 2, or 3 TQuery components names
qryCustomer,
>qryCustomerByOrder and qryCustomerByName (one for each of the above
>queries)?

I use as many query components as I need. That way the SQL only needs
to be specified once at design time and one can take advantage of
prepared parameters. If the 3 queries you show are real then: I
strongly advise against one, you should always insist on a Where
clause; the other two need no Order By since they can only return one
record as written.

Quote

>Alternatively, should one only have one TQuery component, qryCustomer
and
>then dynamically change the SQL statement in each of the viewing

forms?

I only do this in the rare case where, based on complex user input,
there could be *many* variations of a particualr query and thus it is
easier to generate it than providing separate query components for
every possible variation.

Quote

>Which is considered proper design where the goal is to isolate the UI
from
>the database with 3 tier in mind?

Whichever way you do this part, it would be done on the server side in
a 3 tier architecture so it doesn't matter.

Quote

>Next, where is the proper place to put the DataSource component?
Should it
>be on the from that has the UI or on the DataModule?

I consider the datasource to be the GUI's "agent" to connect to data -
it belongs on the forms. That way there only needs to be a single
"connection" between the form and a dataset. You only need datasources
on the datamodule if you are setting up master/detail dataset
connections.

--
Wayne Niddery (WinWright Inc.)
RADBooks - http://www.logicfundamentals.com/RADBooks/delphibooks.html
"At the apex of every great tragedy of mankind there stands the figure
of an incorruptible altruist" - Ayn Rand

John Griffit

Delphi Developer

Thu, 13 Nov 2003 06:49:21 GMT

Re:Good Design Question using SQL DB's?

Thanks for the input, that helps clarify my thinking on it. I was already
coming to that conclusion but wasn't sure that it was correct.
A couple of more little questions along this same line. Since you put as
many TQuery components as you need to set up the SQL at design time on the
Server DM and you would normally have a Provider Component for each query
component, in a 2-tier where all the provider components reside on the
Client DM, would you add a Provider for each of these additional queries or
would you dynamically change the dataset property for the Provider (assuming
of course you didn't need more than one open at a time)?

In other words where possible is it better design to minimize the number of
components or should this be of no concern?

Cheers!

John

"Wayne Niddery (TeamB)" <winwri...@chaffhome.com> wrote in message
news:3b10296a$1_1@dnews...

Quote

> John Griffith wrote in message <3b0fd1a9$1_2@dnews>...
> >Where one has a table that needs to be looked at in a variety of way
> through
> >different SQL queries what is considered "good design practice?" A
> simple
> >example:

> >The data access components will be placed on a Data Module, i.e.
> TQuery.
> >The table is customers. The forms for viewing the data are separate
> forms.
> >The three views to be uses are:

> >1. select * from Customer
> >2. select * from Customer where OrderNo = :OrderNo order by OrderNO
> >3. select * from Customer where Name = :Name order by Name

> >Should the data module have 1, 2, or 3 TQuery components names
> qryCustomer,
> >qryCustomerByOrder and qryCustomerByName (one for each of the above
> >queries)?

> I use as many query components as I need. That way the SQL only needs
> to be specified once at design time and one can take advantage of
> prepared parameters. If the 3 queries you show are real then: I
> strongly advise against one, you should always insist on a Where
> clause; the other two need no Order By since they can only return one
> record as written.

> >Alternatively, should one only have one TQuery component, qryCustomer
> and
> >then dynamically change the SQL statement in each of the viewing
> forms?

> I only do this in the rare case where, based on complex user input,
> there could be *many* variations of a particualr query and thus it is
> easier to generate it than providing separate query components for
> every possible variation.

> >Which is considered proper design where the goal is to isolate the UI
> from
> >the database with 3 tier in mind?

> Whichever way you do this part, it would be done on the server side in
> a 3 tier architecture so it doesn't matter.

> >Next, where is the proper place to put the DataSource component?
> Should it
> >be on the from that has the UI or on the DataModule?

> I consider the datasource to be the GUI's "agent" to connect to data -
> it belongs on the forms. That way there only needs to be a single
> "connection" between the form and a dataset. You only need datasources
> on the datamodule if you are setting up master/detail dataset
> connections.

> --
> Wayne Niddery (WinWright Inc.)
> RADBooks - http://www.logicfundamentals.com/RADBooks/delphibooks.html
> "At the apex of every great tragedy of mankind there stands the figure
> of an incorruptible altruist" - Ayn Rand

Delphi Developer

Thu, 13 Nov 2003 10:05:18 GMT

Re:Good Design Question using SQL DB's?

... besides, if you query should accept different criteria, at different
times, called from different modules, and you got to keep it active at all
times, you obviously have a problem with using a single component. I guess
one could play with filters, but I'd steer away from those little monsters
if I could.

Team

Delphi Developer

Thu, 13 Nov 2003 23:55:07 GMT

Re:Good Design Question using SQL DB's?

Quote

John Griffith wrote in message <3b1032fa_2@dnews>...
>A couple of more little questions along this same line. Since you
put as
>many TQuery components as you need to set up the SQL at design time
on the
>Server DM and you would normally have a Provider Component for each
query
>component, in a 2-tier where all the provider components reside on
the
>Client DM, would you add a Provider for each of these additional
queries or
>would you dynamically change the dataset property for the Provider
(assuming
>of course you didn't need more than one open at a time)?

Same thing - typically there are going to be Provider properties set
to match the particular query component, therefore I'm going to add as
many providers as I need. I'd rather have more tidy little components
on a datamodule where I can inspect and set properties with the Object
Inspector than to write a whole lot of code to do the very same thing.

Quote

>In other words where possible is it better design to minimize the
number of
>components or should this be of no concern?

It is mostly of no concern. The overhead is very small. Remember, all
the executable code of a component is only in your app once no matter
if you have one or 1000 instances of that component. So the only
overhead is the data portion of each (e.g. the SQL property of a
TQuery), but you cannot save much if anything here anyway if you are
going to write a ton of code to assign all of these at runtime, you
are just trading data/resource space for code space.

The same is true of datamodules - do not cram all the datasets for the
app on one or two datamodules to try to save space or keep things
"simple" (this kind of simple is self-defeating <g>). A datamodule is
just another component, so use as many as make logical sense in order
to keep the number of components on each down to a manageable level.

I will trade a little executable size any day for a more organized and
easier to maintain application.

John Griffit

Delphi Developer

Fri, 14 Nov 2003 04:43:55 GMT

Re:Good Design Question using SQL DB's?

Thank you all for the input. It's too bad none of the books address simple
but important issues like these. It can save time and lead to better more
readable and manageable code. I was wondering about the use of additional
data modules but you answered before I asked. What you have to say makes
good sense. One more question, then does this imply that it also makes good
sense to have multiple server modules when doing three tier deployment since
the same problem of soon having too many components and logic on a single
module would exits on the server side as well. If that is the recommended
approach, are there any problems or concerns that one should be aware of?

Thanks again all for the help.

Cheers! John

"Wayne Niddery (TeamB)" <winwri...@chaffhome.com> wrote in message
news:3b112128$1_1@dnews...

Quote

> John Griffith wrote in message <3b1032fa_2@dnews>...
> >A couple of more little questions along this same line. Since you
> put as
> >many TQuery components as you need to set up the SQL at design time
> on the
> >Server DM and you would normally have a Provider Component for each
> query
> >component, in a 2-tier where all the provider components reside on
> the
> >Client DM, would you add a Provider for each of these additional
> queries or
> >would you dynamically change the dataset property for the Provider
> (assuming
> >of course you didn't need more than one open at a time)?

> Same thing - typically there are going to be Provider properties set
> to match the particular query component, therefore I'm going to add as
> many providers as I need. I'd rather have more tidy little components
> on a datamodule where I can inspect and set properties with the Object
> Inspector than to write a whole lot of code to do the very same thing.

> >In other words where possible is it better design to minimize the
> number of
> >components or should this be of no concern?

> It is mostly of no concern. The overhead is very small. Remember, all
> the executable code of a component is only in your app once no matter
> if you have one or 1000 instances of that component. So the only
> overhead is the data portion of each (e.g. the SQL property of a
> TQuery), but you cannot save much if anything here anyway if you are
> going to write a ton of code to assign all of these at runtime, you
> are just trading data/resource space for code space.

> The same is true of datamodules - do not cram all the datasets for the
> app on one or two datamodules to try to save space or keep things
> "simple" (this kind of simple is self-defeating <g>). A datamodule is
> just another component, so use as many as make logical sense in order
> to keep the number of components on each down to a manageable level.

> I will trade a little executable size any day for a more organized and
> easier to maintain application.

> --
> Wayne Niddery (WinWright Inc.)
> RADBooks - http://www.logicfundamentals.com/RADBooks/delphibooks.html
> "At the apex of every great tragedy of mankind there stands the figure
> of an incorruptible altruist" - Ayn Rand

Team

Delphi Developer

Fri, 14 Nov 2003 06:27:23 GMT

Re:Good Design Question using SQL DB's?

Quote

John Griffith wrote in message <3b116716$2_1@dnews>...
>One more question, then does this imply that it also makes good
>sense to have multiple server modules when doing three tier
deployment since
>the same problem of soon having too many components and logic on a
single
>module would exits on the server side as well. If that is the
recommended
>approach, are there any problems or concerns that one should be aware

of?

Again I think the principle holds fine. If the entire set of
requirements for a project is very large then it makes great sense to
try to partition it off into smaller more manageable modules. It is
easier to develop each module separately, and may allow earlier
deployment of parts of the project as well. The only additional issue
is where communications is required between those modules, but these
days that is getting easier and any one of several methods can be used
(COM/DCOM, CORBA, MIDAS, TCP/IP, HTTP, SOAP, etc).

Other Threads

1. Desperately seeking a good DB design

2. Good DB-Design-Tool, Experiences?

3. Best way to Design DB Field

4. DB Design Question

5. DB design question: stringfield vs memo

6. As Requested: Good SQL book and Examples of SQL's LIKE statement

7. question on Database design in SQL Server

8. Can't connect to SQL Server 7 using NT Security using the BDE

9. Design and SQL question?

10. Using Access 97 DB's in Delphi 2