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]
