At 10:04 PM 24/02/2003 +1000, you wrote:

>Now when I began this whole endeavour, I considered using MSAccess, however
>was discouraged from it due to Access not being of the quality of other
>RDBMS (such as Firebird, or Interbase). So I took to the task of learning
>about Firebird, which I was more then happy to do, as the skills picked up
>creating this application (albeit small) would no doubt be helpful.

If this is an experimental project, then good on yer. But it has rather
seemed that you have emphasised moving external data around, rather than
making the database the "soul" of your application...

>In any case, isn't MSAccess an RDBMS also?

Microsoft calls it that...but the key here is that it's a desktop database
with a lot of tricks built in to let you share data with external data
sources. If this is as much of a key requirement as it seems to be, then
Access might be enough to serve your purposes. On the "down side", Access
isn't scalable at all, so if you got around to thinking about client/server
you'd be faced with changing.

>So why is it better to user
>Access, then to use a product such as Firebird.

See above. Also, Firebird has no inbuilt reporting capability. Access does.

>Is it because Firebird is more aimed at being a client/server product.

Indirectly, I suppose I'd have to say Yes. You want to be able to do a lot
of Microsoftish things that aren't doable with a client/server RDBMS
without good to excellent capability in the client environment. Access
comes with a lot of tools that make these things simple to do. More than
adequate for a cherry farmer's seasonal requirements but probably well
short of what a developing vineyard would need.

>Whereas to emulate a client server with Access, you have to create a
>database, then create a second 'database' which actually links to all the
>tables in the first database. In this way you can distribute the second
>database, so that the data is shared (because all data is in the first
>database, and any number of copies of the second database may exist because
>all that they do is point to the first).

You've lost me here. Access's lack of client/server capability has to do
with its architecture, its locking design and its dependence on physical
file-like structures within, that are slave to the I/O system of the
operating system.

I can't visualise an application model like what you describe above.

> >That lawn definitely needed a tractor.
>ie not being limited in the future by lack of forethought at design time?

Not lack of forethought as much as market growth. What starts out as a
handy desktop tool for the payroll clerk grows into a major cashflow
management system for the chief accountant. A lot of projects are like
that. We don't always have the luxury of a set of requirements without a
legacy application that, often as not, was built using the favourite
spreadsheets in lieu of a data model. We frequently have to compromise
the design to fit the budget and the deadline.

>This is one of the reasons why I steered away from Access. I wanted to use
>an RDBMS that would continue to meet any needs. Rather then use Access and
>find out later that upgrading the application would require converting
>database from Access to Firebird (or another product like it).

That is a wise approach, provided you design a scalable system whose
integrity you can protect with the mechanisms of a serious RDBMS. But you
want to pass files of data around from divers sources, you want to pass
various versions of an apparently quite small database around on portable
media. It doesn't fit well with either integrity or scalability, to me.

> >You have to know what you want to do before the guides
> >become useful by telling you how to do it.
>True, I may be lacking in explaining to you what I am trying to achieve, but
>I have created detailed DFD, and Entity-relationship diagrams, and am
>working through this project with a fair idea of what I want to achieve.

Fair enough.

>My
>issues are largely with how to achieve them (ie the subject of all the
>emails throughout the previous days). I posted a more detailed explanation
>of my project in a post (in response to Svein a few minutes ago). I have
>created metadata for what my database is to look like, and if it sheds any
>light at all, I will post it below.

It will be useful to have your scripts on hand when you start to ask
questions about the SQL. :-)

Without looking too closely at anything else I already see problems with
your domains. One is in the use of CHAR columns. Don't use char for
columns of variable length - use varchar. Only use char where the column
will always be filled right out.

>CREATE DOMAIN D_Dummy as CHAR(4);
>CREATE DOMAIN D_Address AS CHAR(38) NOT NULL;
>CREATE DOMAIN D_ABN AS INTEGER NOT NULL;

Don't use integer for ABN. It looks like a number but it's really an
alphanumeric string. If you tried to store my ABN as an integer, it would
overflow. Use char; or be safe and use varchar because nothing stays the
same, esp. re the ATO.

>CREATE DOMAIN D_Branch AS INTEGER DEFAULT 001 NOT NULL;

001 is not an integer. 1 is.

>CREATE DOMAIN D_Name AS CHAR(200);
>CREATE DOMAIN D_Town AS CHAR(27) NOT NULL;
>CREATE DOMAIN D_State AS CHAR(3) NOT NULL
> CHECK (VALUE IN ('ACT', 'NSW', 'NT', 'QLD', 'SA', 'VIC', 'WA', 'TAS',
>'OTH'));
>CREATE DOMAIN D_Postcode AS INTEGER NOT NULL

Postcode should be a char(4) type too, and only if you're sure Australian
postcodes are the only ones you ever need to store.

> CHECK ((VALUE > 0) AND (VALUE <= 9999)); <-- makes this wrong

CHECK (VALUE IS NOT NULL) is about the best you can do here.

>CREATE DOMAIN D_Country AS CHAR(20);
>CREATE DOMAIN D_Contact AS CHAR(20);
>CREATE DOMAIN D_Phone AS INTEGER; Should be char. These are digits, not
>numbers...how do you do (02) 4389 1685 as an integer?
>CREATE DOMAIN D_TFN AS INTEGER NOT NULL;
>CREATE DOMAIN D_Date AS DATE; Why make it a domain?
>CREATE DOMAIN D_Surname AS CHAR(30) NOT NULL; should be varchar
>CREATE DOMAIN D_Given AS CHAR(15); Too short & shd be varchar
>CREATE DOMAIN D_Memo AS CHAR(500); consider blob sub_type 1 to cut down
>the breadth of this row structure.
>CREATE DOMAIN D_Monetary AS DECIMAL(10,2); Accountants usually want 3 or
>4 digits to the right and more than 8 digits at the left.
>CREATE DOMAIN D_ID AS INTEGER;
>CREATE DOMAIN D_Hour AS DECIMAL(4,2);

Enough for the hour and the day.

heLen