Table design

[Accessidiot]

I am trying to figure out how to design a new database. The database is
intended to create a cross reference system for vehicle component part
numbers.
There are 3 different manufacturers of vehicle components.
A user will enter the vehicle details and the part numbers that he or she
knows, so for instance only part numbers from manufacturer "A" would be
entered.
the field for Manufacturer "B" and "C" remain empty.
What I need to achieve though is that if on any vehicle, the part numbers
for Manufacturer "B" or "C" were entered, (So that the system was then aware
of the cross reference between the manufacturers) all instances of that part
number were then cross referenced effectively displaying to the user the
other part numbers.
I don't think I 've explained this too well but lets see!
Thanks

 

[Jeff Boyce]

If you are setting up a column/field for each manufacturer, you are creating
.... a spreadsheet! Access is a relational database, and you won't get good
use of its features and functions if you feed it spreadsheet data.

It sounds like you are saying you have Manufacturers, Components, and
Vehicles.

I'm not following what you want the user to see.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Rick Brandt]

I am trying to figure out how to design a new database. The database
is intended to create a cross reference system for vehicle component
part numbers.
There are 3 different manufacturers of vehicle components.
A user will enter the vehicle details and the part numbers that he or
she knows, so for instance only part numbers from manufacturer "A"
would be entered.
the field for Manufacturer "B" and "C" remain empty.
What I need to achieve though is that if on any vehicle, the part
numbers for Manufacturer "B" or "C" were entered, (So that the system
was then aware of the cross reference between the manufacturers) all
instances of that part number were then cross referenced effectively
displaying to the user the other part numbers.
I don't think I 've explained this too well but lets see!
Thanks

I'm not positive, but it sounds like you are thinking of a table built like
this...

MfgAPartNumber MfgBPartNumber MfgCPartNumber
SomePart
SomeOtherPart
AThirdPart


That would be a very bad way to build the table. A proper table would look
like...

PartNumber Manufacturer
SomePart MfgA
SomeOtherPart MfgC
AThirdPart MfgB

 

[Ken Sheridan]

You'll need four tables for this:

1. Vehicles, with primary key VehicleID and other columns representing
attributes of the vehicles entity, such as VehicleName etc.

2. Manufacturers, with primary key ManufacturerID and columns such as
ManufactureName etc.

3. Parts, with primary key column PartID and other columns such as
PartNumber, PartName etc. Assuming each part is made by one manufacturer
only the table would also have a foreign key column ManufacturerID
referencing the primary key of Manufacturers. The primary key columns of
this and the previous two tables can be autonumbers columns, whose values are
entered automatically.

4. A table which models the many-to-may relationship between Vehicles and
Parts, call it VehicleParts say with two foreign key columns VehicleID and
PartID referencing the foreign keys of Vehicles and Parts. It might also
have other columns representing the attributes of the relationship type, such
as Quantity, in which would be entered how many of the part in question are
used in the vehicle in question.

For data entry you'd have a Vehicles form, in single form view, based on the
Vehicles table and within it a subform, in continuous form view, based on the
VehicleParts table, linked to the parent form on VehicleID. The subform
would have a combo box bound to the PartID column, an unbound text box to
show the manufacturer for the selected part and bound text box controls for
any other columns in the table such as Quantity. The VehicleID does not ned
to be shown on the subform as its value is automatically entered by virtue of
the link with the parent form. The primary key of this table would be a
composite one made up of

When a user navigates to a vehicle record on the main form the subform will
show all the parts for that vehicle, whoever is the manufacturer. If you
wished you could group the rows in the subform by basing it on a sorted
query, e.g.

SELECT VehicleParts.*
FROM Manufacturers INNER JOIN
(VehicleParts INNER JOIN Parts
ON VehicleParts.PartID = Parts.PartID)
ON Manufacturers.ManufactureID = Parts.ManufacturerID
ORDER BY ManufacturerName, PartNumber;

You'd also need forms based on the Parts and Manufacturers tables of course
for entering the records for each part and manufacturer.

If you have a situation where each identical type of part can be made by
more than one manufacturer you'd need a PartTypes table which would have the
columns for attributes such as PartName etc. The Parts table (3 above) would
then have a PartTypeID foreign key column referencing the primary key of this
additional table, rather than having columns for those attributes which are
common to the part type regardless of the manufacturer.

You'll probably find the above easier to follow if you draw it out
diagrammatically on paper with boxes representing each table and lines
representing the relationships between them; what is known as an 'entity
relationships diagram'.

Ken Sheridan
Stafford, England

 

[Van T. Dinh]

Are you saying that the same part can be manufactured by different
manufacturers?

I agree with Jeff's comment about the spreadsheet-like structure. If the
answer to the above question is yes, Parts and Manufacturers are in a
Many-to-Many relationship and to represent the M-to-M relationship, you need
to use a Resolver Table (aka Link Table).

 

[David F Cox]

Auto parts can be made by several different manufacturers, and there can be
various versions of the same part by the same manufacturer. There can also
be cases where A can substitute for B, but not vice versa.

( I did a 6 month contract at FIAT ( :-<)

 

[javelin]

Looking at some of the posts, I agree that this MUST be a relational
database. I have been programing autoparts databases for over 10 years
and they rank up there among the biggest pains.

One of the things you need to be careful about is what I've just
recently been challenged by: what part of the vehicle to relate the
part to. That's right, you can't (or shouldn't) normally relate a part
to the vehicle. Why? Well, a vehicle can have, apparently, more than
one engine configuration. For example, I have a Honda Civic with a 1.5L
engine, and it can come with a 1.6L engine as well. Now, I don't know
that much about auto mechanics, but my understanding is that you can
have some similar parts for those two engines, and some different
parts. Thus, you need to relate your parts to the engine configuration.
That's for engine parts, of course. What about transmission parts? The
car can come in manual or automatic, so now you have different parts
that relate to the transmission on this particular vehicle. Going
further (and getting funner), your wheel base affects the parts you
have, and then there's the body style. Thus, you need to relate the
part to the particular application, not just the vehicle. It's a fun,
fun world in the autoparts industry, and we get to make it all work (or
not).

I'm currently looking for a users' group that supports AAIA database
application design. By the way, go to aftermarket.org, and consider
getting their data structure. At $2500, it's a bit steep, so see if you
can get the customer to cover it.

HTH.
Javelin.