Re: help with object design for a database

I would throw in a web service while I was at it. And don't forget SAP
integration either.

"drg" <drgenio@xxxxxxxxx> wrote in message
news:1149651597.268749.99320@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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 ;).

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).

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! ;)
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.
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.

A web front end overkill? No, I have a couple of big customers, large
retail stores that send products for service. I talked to them and they
happily agreed to load their own data on a web front end on my system.
But I need it to be a separate system, one I can check data (mainly
when the truck with the stuff arrives) before I accept it on my
database. Then, they can check their things on my website.

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

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.

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.

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.

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.

You think Access can handle all those features? :D

Best regards,
Hernan

H. S. Lahman ha escrito:

Responding to Drg...

I ran into a little trouble while trying to design some classes for a
Java program. It's going to be a database front end basically, so I
figured I should get the data into the database first, to have
something to work with.

That's fine so long as all you want to do is look at different views of
the data. In that case this would be one of the few times I would agree
with Topmind; you really want a RAD IDE like Access.

However, if your application really needs to solve some significant
problem, then you should forget about the database view until you have
solved that problem. (That doesn't seem to be the case here.)

The problem goes like this, It's a management software for my TV repair
shop. So I have to create Service Orders (not worrying about that yet),
and I have to specify the Model number of the device. Later, after I
have fixed this Service Order, I'll have to report which Parts I used
(code, description, etc. as in BC548 - NPN Transistor).

So far this just sounds like classic CRUD/USER processing. You have
data entry for a Service Order that identifies a particular model. To
fix the problem you need the proper parts associated with the model.
Once you fix the problem you have a second data entry problem to track
the parts used.

So I have 3 tables: the Spare Parts table, the Models table and the
SparePartsByModel. I want to write a program that can take the listings
my providers send monthly and update the database records. The problem
is I have, so far, two types of listings.

Why three tables in the RDB?

[Model]
* modelID <--------+
+ ..... |
|
[Part] |
* PartNumber |
+ .... |
+ modelID ----------+

The 1:* relationship between Model and Part allows you to write queries
to get the Parts for a particular Model. That's what SQL and DBMSes do.


One of the providers sends me an XLS file with about 60000 lines, their
SparePartsByModel table. Great, so I iterate through the list three
times: First to get the models, second to get the parts, and third to
load the data by ID field in the SparePartsByModel table. In the
Service menu, the available parts will be displayed for that particular
model.

But the other provider sends me a plain text file with the model, and
another file with the spare parts list. This spare part list also has
the Model field but only for the first match (that is, if I have a
Spare Part used in 10 Models, I get only the first model. Pretty stupid
design, but well, they've been doing it that way for over 10 years now
so it seems to be Good Enough for them). So in the Service menu I'd
have to remove the "show parts available for this model only" filter.

This sounds like a completely different problem where you need to update
the RDB with data from different vendors using different formats. IOW,
instead of a data entry problem you have a decoding problem to get data
into the RDB. That is completely separate from Service Orders and
making repairs. Presumably you only have to do this when the vendor
creates a new model (as opposed to each time you process a service
request).

I would just create two small applications, one for each vendor, to do
the XLS/text conversions and write Model/Part records into the RDB.
IOW, you now have five mini-applications:

(1) update Model/Part DB from XLS vendor for a new model
(2) update Model/Part DB from text vendor for a new model.
(3) create a Service Order with a modelID via data entry
(4) get candidate parts for repair from DB using SO's modelID
(5) update SO for Parts actually used in DB.

The last requires an additional table and a different 1:* relationship
with the [Part] table:

[Service Order]
* SOID <---------------+
+ .... |
|
[Part] |
* partNumber |
+ .... |
+ modelID |
+ SOID ----------------+

The problem is, how can I make a pretty implementation for this?

Vendors are unlikely to be cooperative in standardizing how they specify
parts lists for their models. So I suspect you are stuck with providing
a front end unique for each vendor; (1) and (2) above. That at least
isolates the custom code to a single relatively trivial application. To
the extent that they standardize, you will be able to reuse the same
front end conversion program for sets of vendors. Once you have that
data in the RDB, the (3) - (5) are just typical RAD processing to update
or present RDB data.

[Basically you just need to connect a modelID to a set of partNumbers
(together with Part information for any new parts). That allows further
reuse for the front end. For example, for (2) you could settle on a
text format that your RAD IDE can read for import and then use perl
scripts to convert different text formats from different vendors into
that common import format. Then you can import without worrying about
constructing SQL queries.]


*************
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



.