Re: help with object design for a database

Responding to Drg...

Sorry if I didn't make myself clear. What I meant to say is, I have
tested a few commercial solution, but didn't find one I like. The
biggest problem with these solutions is their user interface. I know
it's very hard to make a good UI, but these programs I tested are real
crap. You can't do the data entry with the keyboard alone. There are
literally thousands of buttons you have to press and windows popping up
all the time to tell you unnecessary things, and of course, absolutely
no keyboard shortcuts. And most programmers have a very questionable
taste for the UI, and use some DISGUSTING skins with strong colors and
ugly icons. I have a thing for web design so I work to make simple,
easy to understand interfaces, and also, for this kind of applications,
I try to maximize the use of the keyboard. I have seen a couple of POS
(Point of Sale) implementations done on Windows, where the operator
needs to use the mouse for some things. My god! A MOUSE on a cash
register? (apparently a computer with monitor, keyboard, mouse, barcode
reader and receipt printer is cheaper than a cash register. But what do
I know, right ;).

I would seek to minimize the keyboard for a POS site because the users are unlikely to be good typists. But taste in UI design is an entirely separate issue, so let's not go there. With RAD IDEs like Access you get to design your own UI and that can be pure form-based.

Regarding to the second part, yes, what I want is a frontend so I can
AUTOMATICALLY update my vendors' lists. I don't want to convert
anything, the program should open the file I have, auto detect its
type, and load it on the database or throw an error. Yes, this is an
operation that happens only once a month for each vendor, but I have 5
providers so far (and growing).

By conversion I meant loading the vendor data into the DB. You are going to have to write some code somewhere to parse the vendor format and convert it into a format that the RDB can eat (text import format, SQL queries, whatever).

You could probably autodetect which vendor the input file was from the syntax, but it would probably be much easier to tell it who the vendor is or use something like a dedicated directory location to determine that.

However, those are details. The important point is that processing the vendor model data is an entirely separate application from the rest of the features you are talking about. It is run at different times for different purposes and will probably be a batch execution rather than interactive.

Another reason why I want it in Java is that I need alternate ways to
access the data. Right now I'm using a computer in the front desk,
where I process some 20 orders a day, sometimes up to 40. It has to be
fast, I have a line of customers I have to deal with, I can't spend
time with a mouse! ;)

If all you have is 40 customers a day, a mouse is not going to impact your throughput. But with RAD IDEs like Access you can make a pure form-based UI where the clerk enters text fields from the keyboard.

I need to have computers on every technician's bench too, so they can
upgrade the orders status in real time, and also have stock control.

That's not a problem; it is what RAD IDEs are designed to do!

Why real time order status? Because I will make a web front end for my
customers to check their order status on-line, and maybe an automated
telephone front end too.

Now you are talking about a browser-based UI rather than a GUI. The distributed processing makes that a somewhat different ball game. However, the modern RAD IDEs can handle that too if it isn't too complicated. As you describe your application, it isn't very complicated and there are quite distinct contexts for how the data is accessed. That makes synchronization and data integrity much easier, so I would expect the RAD infrastructure to be adequate.

The automated phone is a different problem. You are going to have to write some code for that interface because the RAD IDEs don't have infrastructure support for that yet AFAIK. But, like the model/part data, it is a separate application that accesses the RDB on a standalone basis in a very special situation with limited access.


Also, I have a (still very small) warehouse. I'd like to check things
there on a PDA with wireless access.

Probably not a problem if the PDA is running something like WinCE. Then you could have Access on the PDA and it will provide the infrastructure to talk to the distributed RDB.

In the worst case it would be like the telephone hookup and vendor model data. You would have to write a small application to support that mode.

And the most important feature I want: intelligence. I'm going to add a
"magic" search field, where I can type anything and the system will
know what to do with it.
If I type in a service order number, the system will show the order
number and the status. Unless the user is a technician, in that case it
will show the repair menu.

And how does it know who the user is? This approach also assumes that there is exactly one role for each user. That's not very robust. It is pretty basic UI design for the user to indicate what needs to be done and the UI then responds with the appropriate interface. The UI standards refer to this sort of thing a "user empowerment" -- the user gets to decide what is seen and done, not the software. (More precisely, not the software developer's view of what should be seen and done.)

If I type in a model number, the system will ask if I'm looking for an
order with that model number, or if I want to create an order with that
number.

Why not let the user do that directly ("I want to look at an order" or "I want to create an order")? That is what menus are for; they make the application user-friendly by giving the user direct control.

However, there is nothing to prevent you from doing as you suggest with a RAD IDE. That is, this is a matter of UI design, not RAD IDE facilities.

If I type in a serial number, the system will detect the format of the
serial number and guess the brand and if possible the model number, and
create a new order. Unless that serial is already on the database and
has a still open service order, so the system will display the open
order.

That is standard fare for the RAD IDEs.

The same goes for a name (will display orders for that name), part
number (will display pending buy orders for that part number, and to
which service order that part belongs), whatever.

That's possible but you are asking for trouble if it depends upon the ability to distinguish patterns /within/ fields that are supposed simple domains in the RDB. IOW, it violates 1st Normal Form. As soon as you need to parse the field to distinguish, say, an order number from a part number, you open the door for foot-shooting (e.g., a particular vendor chooses a format for a new part that happens to match your format for an order number).

This is one reason why good UIs let the user define the semantic context /before/ one goes after the specific data.

You think Access can handle all those features? :D

Yes. The only places where you need to write java code are for the special situations where the input is not a standard GUI or browser UI. And then, you only need just enough code to convert the input into a form that the DB or a RAD IDE can handle. IOW, the only java code you seem to need here is for input conversion of specialized formats.

However, I don't see that as the real issue. You are describing this problem as if there was some monolithic application that was going to do everything. That does not seem to be the case. It seems much more likely that you would have small, dedicated, independent applications to deal with the special situations.


*************
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
hsl@xxxxxxxxxxxxxxxxx
Pathfinder Solutions -- Put MDA to Work
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
Pathfinder is hiring: http://www.pathfindermda.com/about_us/careers_pos3.php.
(888)OOA-PATH



.