Database Design Suggestions

[Ron A.]

I am hoping someone can look at my tables and relationships for a vehicle
parts database I am woking on and give me some suggestions. I posted before
on the subject and got some great help, but I just seem to be stuck. I want
to create a quick reference tool for mechanics that will allow them to look
up parts in a database so they do not have to repeat the time consuming
process of parts research everytime they need a part for a vehicle. Some of
the issues I have run into during this is that I have many vehicles with the
same model number that have different manufacturers and many suppliers for
the same parts. Here is what I have so far:

[PartsTbl]
PartID <--primary key and has one-to-many with [PartsForRegNo]
[PartsForModels]
PartName
CategoryID <--foreign key from [CategoyTbl] with one-to-many
AuxiliaryEnginePart
PartDescription

[RegNoTbl]
RegNoID <--primary key and has one-to-many with [PartsForRegNoTbl]
RegNo
ModelID <--foreign key from [ModelTbl] with one-to-many
VIN
BodyStyle
EngineModel
EngineSerial
AuxEngineSerial
TransModel
TransSerial

[ModelTbl]
ModelID <--primary key and one-to-many with [RegNoTbl][PartsForModelsTbl]
ModelYear
Make
ModelName
MFGID <--foreign key from [MFGTbl] and has one-to-many with [MFGTbl]

[SuppliersTbl]
SupplierID <--primary key and has one-to-many with [PartsSuppliersTbl]
CompanyName
ContactName
ContactTitle
Address
City
State
Zipcode
Phone
Fax
HomPage

[CategoryTbl]
CategoryID <--primary key
CategoryName

[MFGTbl]
MFGID <--primary key
MFGName

[PartsForModelsTbl] <--junction tbl
PartsModelID <--primary key
PartID <--foreign key
ModelID <--foreign key

[PartsForRegNoTbl] <--junction tbl
PartsRegNoID <--primary key
PartID <--foreign key
RegNoID <--foreign key

[PartsSupplierTbl] <--junction tbl
PartsSupplierID <--primary key
SupplierID <--foreign key
PartID <--foreign key

All relationships have referential integrity selected and join type #2
except for the [CategoryTbl] & [MFGTbl]. They have join type #3 and no
referential integrity.

 

[Chris2]

I am hoping someone can look at my tables and relationships for a vehicle
parts database I am woking on and give me some suggestions. I posted before
on the subject and got some great help, but I just seem to be stuck. I want
to create a quick reference tool for mechanics that will allow them to look
up parts in a database so they do not have to repeat the time consuming
process of parts research everytime they need a part for a vehicle. Some of
the issues I have run into during this is that I have many vehicles with the
same model number that have different manufacturers and many suppliers for
the same parts. Here is what I have so far:

Ron A.

There is a lot of specialized information here that I'm not sure
about (not knowing much about cars).

But the last thing you mention, "Some of the issues I have run into
during this is that I have many vehicles with the same model number
that have different manufacturers and many suppliers for the same
parts. Here is what I have so far:"

Parts
PartID <-- Primary Key

Manufacturers
ManufacturerID <-- Primary Key

Suppliers
SupplierID <-- Primary Key

PartsSuppliers
PartID <--\
SupplierID <--- Composite Primary Key

PartsManufacturers
PartID <--\
ManufacturerID <--- Composite Primary Key

That's pretty much the basic way to arrange it.

It appears you have the first four tables from the data you gave
(just structured a little differently, with a separate primary key
on the many-to-many tables).

Now you just need the PartsManufacturers table (named in your naming
system).


Sincerely,

Chris O.

 

[Ron A.]

Thanks for the reply. I have a supplier parts table instead of manufacturer
because who we get it from is usually more relevant. Do you think that a
form based on the PartsTbl with subforms of the junction tables would be a
good start for a form? Also, how do you get the data in fields to show in a
combo box for a form? Thanks in advance for any help.

 

[Chris2]

Thanks for the reply. I have a supplier parts table instead of manufacturer
because who we get it from is usually more relevant. Do you think that a
form based on the PartsTbl with subforms of the junction tables would be a
good start for a form? Also, how do you get the data in fields to show in a
combo box for a form? Thanks in advance for any help.

Ron,

Going from a vague discussion of the logical and physical database
design to Forms design is a big jump.

IMO, anyway, you shouldn't think of Forms as "based on a specific
table". A Form should be based on what you *need* it for. The
Queries and/or Tables it winds up drawing on will be determined by
the needs of the form. If you need Form that shows your parts and
has SubForms for manufacturers and suppliers (and the other
information), then that's what you have to make. Whether or not
it's a "good idea" is dependent on end-user needs.

As for getting data into combo boxes, that's definitely a Forms
question, and belongs over on:

microsoft.public.access.forms
microsoft.public.access.formscoding
microsoft.public.access.formsprogramming


Note: Googling for "MS Access Combo Boxes" can yield a lot of
interesting results.


Sincerely,

Chris O.


PS, you can also check out:

Websites:

http://www.mvps.org/access
http://allenbrowne.com/
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Top


Sincerely,

Chris O.