At 11:31 PM 24/08/2004 +0000, you wrote:

>I posted this in my normal news reader, but I'm pretty sure it
>didn't make it to Yahoo. Please forgive if this is a double post.
>I'm not used to Yahoo groups.
>
>
>
>I have just used SQL2GDB to convert an MS Access database to
>Firebird (actually a .gdb I renamed). Using IB Expert, I notice all
>fields have been assigned a default domain, something like: RDB$3.
>
>What are the implications of just using those defaults as opposed to
>creating my own?

No implications. Just ignore them.

>The obvious implication is that if I create my own,
>there will be fewer since it appears that every field was assigned
>its own, unique domain. Which leads to the next question, What are
>the implications to having all those unique domains rather than a
>few that get re-used?

Again, just ignore the default ones, don't try to re-use them. It will
work, of course, but your problem is that, when the db gets backed up and
restored, the internal domain identifiers might change.

Because of the way you created the database, you denied yourself the
ability to take advantage of domains for packaging a group of data attributes.

>I am interested be cause I am basically lazy and this database has
>over 100 tables with a good number of fields. I tend to fall asleep
>doing such redundant tasks.

If you want to standardise columns to domains, it won't be a redundant
task. What I'd do with your database is extract all of its metadata to a
script, spend a worthwhile few hours deciding how I want to define my
domains and then edit the metadata to reflect these generalisations. After
that, rebuild the database and pump the data in from the original database.

If you want to take a more ad hoc approach, this will work with your
existing database:

1. Let's say these are a couple of your current table definitions. If you
extract their metadata, you will see this:

CREATE TABLE TABLE1 (
ID INTEGER NOT NULL,
DATA VARCHAR(30) );

CREATE TABLE OTHER (
ID INTEGER NOT NULL,
DATA1 VARCHAR(30),
DATA2 VARCHAR(30));

2. You want a domain definition for these "Data" items, that always
guarantees they will be interchangeable for some requirement, e.g. you want
to UNION THE data columns for some reason. So create this domain:

CREATE DOMAIN D_DATA VARCHAR(30);
COMMIT;

3. Now you can do this:
ALTER TABLE TABLE1
ALTER DATA TYPE D_DATA;
ALTER TABLE OTHER
ALTER DATA1 TYPE D_DATA;
ALTER DATA2 TYPE D_DATA;
COMMIT;

4. Now, extract the metadata again and - surprise!!

CREATE TABLE TABLE1 (
ID INTEGER NOT NULL,
DATA D_DATA );

CREATE TABLE OTHER (
ID INTEGER NOT NULL,
DATA1 D_DATA,
DATA2 D_DATA);

You could also do the same on the ID columns in these tables, *provided*
they are not part of any constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE,
etc.) You would need to drop the constraints first.

I'm NOT a great fan of applying large numbers of metadata changes in an ad
hoc manner (as you'll realise from reading my book!!). I'd want to warn a
lazy person that he'll spend a lot more time and effort on volumes of ad
hoc metadata changes than he would on generating and maintaining a very
sound script and pumping the data in. (...repair holes in the roof in
summertime...) The script-and-pump approach is also going to find bad
design and bad data in a much more constructive way...

>I bought Helen's book (great work Helen) and tried to figure this out
>myself, but either I missed it, or the book didn't address it, or I
>was too dense to get it. Could be the later since my head sometimes
>misses the keyboard and bounces off a very hard table top.

The book does mention it in a passing note *somewhere* (I remember writing
a paragraph about it) but it's not a topic - it's just not relevant to what
anyone needs to do in dynamic DDL. You don't have to create domains for
*everything* (or, indeed, *anything*). Either you can just go with the
default domains and ignore domains entirely; or you can use CREATE DOMAIN
as a highly useful way to tighten up your design.

./heLen