At 07:04 AM 24/02/2003 +1000, you wrote:

> >firstly databases don't store queries - your application has the queries.
>When I say query I think I meant to say stored 'views'. But isn't a view
>really just a query (I cringe as I ask this..).

Yes, it is a query *specification* which is stored for a specific
purpose. It specifies an output set on whose data you can operate as
though it were a table.

>So in effect, regardless of which method to use, I should only store the
>tables in the database, all queries should be kept to outside of the
>database.

A query is a client-side task. SQL is the language that allows a query to
specify the data it wants to look at (select statements) or operate on
(update, insert, delete and, for stored procedures, to execute).

>In which case, why are views used?

Views can be used almost wherever a table is used. You can select from
them (complete with WHERE, GROUP BY, ORDER BY, etc.). In most cases, a
view can be made "updatable", by use of triggers. Updates to data in a
view of course are applied to the underlying tables.

A common use is to isolate one subset of the data in a table, or a group of
related tables, to an appointed user, for just the kind of security
situation you have described.

Because a view is a database object, it is subject to privileges, just as
tables and stored procedures are. Did you realise that the only users who
have access to the data in a database without being granted specific
privileges are the SYSDBA and the user which owns the database (if this
user is not SYSDBA)? Lookup Security in the guides. In SQL, look up GRANT
and REVOKE.

> >tracking a version number stored by you in your own version table data.
>Do you mean that it is standard practice for a database to contain a table
>entirely dedicated to storing application information? ie version number.

If there is a need to be updating versions, then yes. You would typically
store the information in a table with restricted privileges and read it at
logon. However, designing a database that is going to get metadata updates
remotely is NOT beginner stuff.

> >fourth, if data security is an issue and data needs to be distributed (i.e.
> >if you can't have one client getting access to another client's data), then

>It is an issue, also there is the possibility that the client will bring
>data files to the accountant. The accountant may open the files to print
>reports etc, but not want the client data merged with his own data (which
>would be the case if only one database was used).

Portable "data files" are not database tables. Fb/IB doesn't store data in
"data files". It does have some mechanisms by which you can make external
files visible to the database engine, as if they were database tables. See
the EXTERNAL FILE option of CREATE TABLE. You can use these mapped tables
for both importing and exporting, provided you can find a way to translate
the external file to fixed length fields and, on the export side, the
application which is going to read the exported data is capable of working
with fixed length fields.

There are several third-party tools available that can convert external
file formats like CSV, et al. into Fb/IB tables. Look at the IBPhoenix
site to find these.

A database is normally an enterprise-wide thing, and maintaining a separate
database for each client of the enterprise is not a good thing, either for
data integrity or for the reasons you raise by your questions.

> >I'm assuming you want each of your clients to use this application and not
> >you (the accountant) to use it for all clients.
>A little of both. There are instances where the client would use the
>application from his office, but also instances where the accountants will
>actually maintain the data for several clients.

Then your database design should be aimed at a single database, with the
client-accessible data restricted via views and privileges. If you want to
isolate one accountant's visibility from the other accountants in the
enterprise, you define an Accountant entity and use privileges to restrict
what each can see. You link an accountant to her clients using a foreign
key relationship.

If, on the other hand, you are really talking about some kind of "package"
that an accounting firm with a single accountant buys off the shelf and
operates just for her own customers then, of course, it is an enterprise of
one. If you need to provide isolation by accountant, it would be a bad
design decision to assume that a one-accountant enterprise will never have
more than one accountant.

You would still need to have a user in the enterprise, who has privileges
to everything.

heLen