If I may ask a few philosophical questions and get some of your
collective wisdom., thank you.
A few thoughts as setup for the questions first.
Base is:
- a desktop database manager
- a database front end / BI (reports for those that don't like
buzzwords) design tool
- a GUI based IDE
- a GUI based rich client
The target audience ( user ) of the Base application as a database
development tool is:
- unlikely to have a strong database background
- unlikely to have a software development background
- likely to have some experience with word processor and spread sheet
applications
Alright, I think that list is acceptable to most of us.
After a small hiatus I am getting back to helping work on the "Mid
Level Tutorial" that I've mentioned before. Specifically the
development of the actual database application that professor Casanova
has described in the text of the tutorial. As part of that work, he
has put together a UML diagram describing the data schema, along with
mock screen and report layouts.
The first step of course is to turn the UML diagram into an actual
physical schema - and - therein is the question.
Back in the good old days we had some real constrains on names, table
names, column names and the like. Some most likely remember the old 8
character limits imposed by some early RDBMS, even the big name ones.
But those days are long gone
Many of us are use to the idea of a virtual data model and a physical
data model - meat and potatoes, if you will, of the DBA and data
modeler. But DBAs and data modelers are not the expected users of the
Base application in general and certainly not the target reader of a
mid level tutorial. Others of us are experienced database application
developers ( business applications in other words ) and are used to
data dictionaries, one use of which is to help map those often near
cryptic physical column names back to the descriptive name used when
the virtual ( business level ) design was done.
( as an aside - who has actually gone ahead and written that script to
produce a data dictionary from a hsqldb Base database...come on you
know we've all been meaning to get to it sooner or later - who wants
to share? ;> )
OK - all that is to get this. Looking at the conceptual design
reflected in the UML diagram I see lots of quite descriptive column
names. I can do the normal, and comfortable, step of changing these to
something less so - or - and this is where I am leaning, leaving them
just as they are. Long, multi word phrases and verbose.
Let me tell you why - because if you are using point and click, drag
and drop and wizard driven development tools it doesn't really matter
and for the target user probably makes life easier.
Case in point - the database includes a table 'Assignment', simply
creating a script to build the physical table to match the the UML
diagram would give this:
CREATE TABLE "Assignment" (
"Assignment ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)
NOT NULL PRIMARY KEY,
"Patient ID" INTEGER NOT NULL,
"Therapist ID" INTEGER NOT NULL,
"Date assigned" DATE DEFAULT CURRENT_DATE NOT NULL,
"Date case closed" DATE,
CONSTRAINT FK_PAT_ASMT FOREIGN KEY ("Patient ID") REFERENCES "Patient"
("ID Number"),
CONSTRAINT FK_THP_ASMT FOREIGN KEY ("Therapist ID") REFERENCES
"Therapist" ("ID Number"),
CONSTRAINT CK_CLOSE_DT CHECK( "Date case closed" >= "Date assigned" )
);
As I say, verbose column names indeed. But when you use the form
wizard against that table you get nice meaningful labels. Same for
reports. None of that, run the wizard now go back and select each
label and change the string from, for example, "DateClosed" to "Date
case closed" - no need, it's much more likely the form is 'as desired'
when the wizard is finished then not.
A few days ago Villeroy said that he doesn't use the GUI query
designer - he prefers to type in the SQL commands directly - for him
then those column names would be a pain in the rump IMO. But again, I
think not using the GUI tools must be looked at as the great exception
with regards to the target users. The average Base user is going to
use the GUI query designer, the same for the report creation tools. In
report builder, drag that column onto the report and there you go - no
extra edit to the label is needed.
So for the moment this is exactly what I have done for the database to
be included with the tutorial - but believe some would disagree, so if
you do tell me why, please.
Another case. Lookup lists - again we all know that it is best to use
lookup tables right - not to put hard coded lists in the properties of
the list control. Well, yes I agree of course, in principle. I think
it again must be viewed from the typical target user.
Here is the therapist table:
CREATE TABLE "Therapist" (
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT
NULL PRIMARY KEY,
"First Name" VARCHAR(50) NOT NULL,
"Surname" VARCHAR(50) NOT NULL,
"Gender" BOOLEAN NOT NULL,
"Date of Birth" DATE,
"Street and number" VARCHAR(200),
"City" VARCHAR(100),
"Postal code" VARCHAR(15),
"State" CHAR(2),
"Tax number" VARCHAR(40),
"Academic degree" VARCHAR(40),
"License number" VARCHAR(40),
"Hiring date" DATE NOT NULL,
"Termination date" DATE,
CONSTRAINT "CK_TERM_DT" CHECK( "Termination date" > "Hiring date" )
);
Specifically Gender and State - In the case Gender professor Casanova
having learned from his research realized that this could be a
boolean field ( Male = TRUE, Female = FALSE or perhaps vice versa
would be more PC for the OOo world, where it seems all users are
female when mentioned in discussions) and then use radio buttons for
the form UI. Fair enough, we have probably all done that at one time
or another. But IMO for our target user it is better practice to
handle these types of fields, really an enumerative type, as the
string we would want represented. So in this case a divergence from
his design and this table structure instead
CREATE TABLE "Therapist" (
"ID Number" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT
NULL PRIMARY KEY,
"First Name" VARCHAR(50) NOT NULL,
"Surname" VARCHAR(50) NOT NULL,
"Gender" CHAR(6) NOT NULL,
"Date of Birth" DATE,
"Street and number" VARCHAR(200),
"City" VARCHAR(100),
"Postal code" VARCHAR(15),
"State" CHAR(2),
"Tax number" VARCHAR(40),
"Academic degree" VARCHAR(40),
"License number" VARCHAR(40),
"Hiring date" DATE NOT NULL,
"Termination date" DATE,
CONSTRAINT CK_THP_GNDR CHECK( "Gender" in ( 'Male', 'Female' ) ),
CONSTRAINT CK_TERM_DT CHECK( "Termination date" > "Hiring date" ),
CONSTRAINT CK_ST_CASE CHECK( "State" = UCASE("State" ) )
);
Why? Well, for one thing it will save the user from having to use a IF
statement in either a query or as part of a report function to get
that string for display.
Also notice that there is no FK relation for the State field. Once
again a decision based on the target user or more precisely the target
use. In this situation it makes sense to me to expect that this will
be a relatively small number of values ( remembering that the target
user is most likely building this for personal or personal work group
use). So that the best way to handle data validation is simply to use
a combo box on any input form with a data source command of "SELECT
"State", "State" FROM "Therapist", offering quick entry of redundant
data, easily added new values and consistent case with check
constraint. Another way to look at that is that this use of the combo
box with a self referencing select statement should be similar to the
data input experience of most spread sheet users.
But - do any of you think that is a bad thing for a tutorial to show -
should a tutorial instead stick to look up tables for this type of thing?
Anyway - if you read along this far - thanks for your time - and
thanks for comments you might want to toss my way on any of this -
think of it as 'Best practices'. What would you NOT want to be showing
users or conversely what would you think particularly important to
pass along.
Thanks
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
