Re: help with object design for a database
- From: "H. S. Lahman" <h.lahman@xxxxxxxxxxx>
- Date: Tue, 06 Jun 2006 22:35:40 GMT
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
.
- Follow-Ups:
- References:
- help with object design for a database
- From: drg
- help with object design for a database
- Prev by Date: Re: What is polymorphism?
- Next by Date: Re: What is polymorphism?
- Previous by thread: Re: help with object design for a database
- Next by thread: Re: help with object design for a database
- Index(es):
Relevant Pages
|
