Database design Suggestions

[Ron A.]

I have started creating a relational database for parts research and I am
having problems with setting up the tables with multiple vehicles related to
1 part. This is what I have so far:

Vehicletbl:
VehicleID - Primary Key
ModelID
VIN
Serial
BodySerial
ChassisSerial
EngineModel
EngineSerial
TransModel
TransSerial
Remarks

Modelstbl:
ModelID - Primary Key
ModelYear
Make
Model
MFG

Partstbl:
PartID - Primary Key
PartName
PartDescription
PartNO
NSN
CategoryID
VehicleID
ModelID
UnitPrice
SupplierID
Remarks

Categoriestbl:
CategoryID - Primary Key
CategoryName
Remarks

Supplierstbl:
SupplierID - Primary Key
CompanyName
ContactName
ContanctTitle
Phone

I need to relate 1 part to several different vehicles and I am having
trouble designing to achieve that. Any help with this is greatly appreciated.

 

[Vincent Johns]

I have started creating a relational database for parts research and I am
having problems with setting up the tables with multiple vehicles related to
1 part. This is what I have so far:

Vehicletbl:
VehicleID - Primary Key
ModelID
VIN
Serial
BodySerial
ChassisSerial
EngineModel
EngineSerial
TransModel
TransSerial
Remarks

Modelstbl:
ModelID - Primary Key
ModelYear
Make
Model
MFG

Partstbl:
PartID - Primary Key
PartName
PartDescription
PartNO
NSN
CategoryID
VehicleID
ModelID
UnitPrice
SupplierID
Remarks

Categoriestbl:
CategoryID - Primary Key
CategoryName
Remarks

Supplierstbl:
SupplierID - Primary Key
CompanyName
ContactName
ContanctTitle
Phone

I need to relate 1 part to several different vehicles and I am having
trouble designing to achieve that. Any help with this is greatly appreciated.

Since you ALSO need to relate 1 vehicle to several different parts
(currently via [Partstbl].[VehicleID]), you'll need to set up a
"many-to-many" relationship, involving another Table.

Let's call the new Table [VehiclePartstbl], to contain fields like these:

[VehiclePartstbl]:
[VehiclePartstblID] <-- primary key (maybe not needed)
[VehicleID]
[PartID]

Each record in this Table will reflect the presence/use of some part in
some specific vehicle. You might add fields to indicate other
information such as date replaced or repaired, part serial number, etc.,
but the [VehicleID] and [PartID] foreign keys are the essential parts
for setting up the relationship.

You would probably want to delete the [Partstbl].[VehicleID] field from
[Partstbl], as its former meaning of "the unique vehicle to which this
part belongs" will have become obsolete, and [Partstbl] will have become
a list of generic descriptions of part types (similar to a catalog), not
of individual parts that can be installed or repaired.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.