many-to-many relationship

[Duane Riech]

I have a JDBC question and would appreciate any answers or advice anyone
could give me. I've never used JDBC before so my question may be trivial
or obvious to someone.
I want to write a Java application using JDBC to access a MS Access database.
The schema design is something like this example:
I have a table named "autoparts" which contains numerous, unique parts.
I also have a table name "autos" which has numerous references to the "autoparts" table. However, each "autoparts" entry may be used by numerous "autos", so I have a many-to-many relationship.
I wanted to keep an list/array/Vector of "autopart" entries in each "auto" record, but I don't see a "blob" type when defining a table in MS Access.
Can Access handle this datatype?
If not, is there another MS database which is installed with Win 2000 that will handle this? I don't have a lot of money to buy a high dollar database.
Also, is there a good JDBC book, ISBN # ???, that could show me the best way to write this?

Thanks....

[Paulo Salgado]

One way to handle this kind of relationship is to add a table to your schema that contains the Primary Keys of both entities involved. For instance in your case you could have a table composed of PART_NUMBER (for parts) and MAKE + MODEL (for cars).
Whenever you need to relate both entities use this table in a join operation, so you can retrieve columns from each individual entity.
Regards.
-Paulo

[Lu Battist]

MS Access doesn't have blob datatype but I'm not sure why you would need it. Before diving too far into JDBC, you should spend some time reading up on database design. As for JDBC, Sun has a good tutorial. As for books, my personal favorite is JDBC API Tutorial and Reference ISBN #0201433281 but there may be a new edition out by now and unless you plan to do signifiant JDBC work, you really don't need a book.

[Michael Matola]

Although the main question of this thread from a few days ago is different from yours, along the way in that thread I give an example of the approach Paulo is talking about -- using a bridge table to break up a many-to-many relationship into two one-to-many relationships with the keys of the bridge table being the combination of the keys of the other tables.
In your case you could have a table "auto" that contains identifying data about each auto, "part" that contains data about each part, and, say, "build" (can't think of a good name for this table) that links autos to their constituent parts.

[Stuart Friedberg]

I want to echo what the others have told you. This is neither a JDBC
issue nor an Access issue. This is a fundamental database design
and/or SQL issue. Very fundamental.
If you stick a list of parts inside an auto record, you are wasting
the power of a relational database and SQL. Create a table of autos,
a table of parts, and ANOTHER TABLE that shows the relationship between
autos and their parts. If you have more than one relationship, create
more than one table.
The is-a-part-of table needs two columns to serve as its key: the auto
and the part. The values in both of these columns should be the keys
from the corresponding (auto, part) tables. The is-a-part-of table
might have additional columns, like how many of the part are required.
E.g., 4mm x 15mm socket head capscrews: 142 required. But these are
optional.
To find the parts that belong to an auto, first look up the auto.
Take the key from the auto table record, and use it to find all
matching records in the is-a-part-of table. You will get back a
set of records, one for each part that belongs to that auto. In
SQL terms, this is called a "join".
Assuming appropriate table declarations (I don't know if
Access lets you create tables using SQL, or if you have to
do it in Access directly):
SELECT Part.StockNumber, Part.Description, AutoParts.RequiredCount
FROM Auto, AutoParts, Parts
WHERE Auto.Description = "Mercedes 180 sedan"
AND Auto.Key = AutoParts.Auto
AND AutoParts.Part = Part.Key
There is other SQL syntax that explicitly uses the work JOIN,
but it's not needed for something this simple.
If you expect to do that query often, on large tables, you should
create an index on the auto column of the AutoParts table. But
that's an optimization that can wait until the basic database is
laid out properly.