Database design, need suggestions

[Guest]

Database design need suggestions and forms- multiple primary key proble

The purpose of this database is to be a list of parts in the department as well as to easily see which parts are at which station/substation (and the # there), as well as tracking whether it is critical or not (yes/no)

I also want to be able to see which station/substations each individual part is at

Here's the design of my database

Parts Table - Key Fields: Part Number, Part Description Other fields: Part Name, # parts ( I decided this based on the excel data that has already been collected.. this is more of an informal database so many of the parts (ie screws) have the same arbitrary part Number but different part descriptions

Station Table- Key Fields: StationName, Other Fields: StationDescriptio

SubStationtable: Key Fields: Substation, StationNam

PartsStationJunction Table- Key Fields: StationName, SubStation, Part Number, Part Description, Other Fields: # Part at Statio

Relationships:
All One to Many relationships linking primary keys:
Station Table to SubStation (StationName field
SubStation Table to Junction Table (StationName ,SubStation
Parts Table to Junction Table (Part Number, Part Description

So, with this setup, the user will have to add the part to the master list first (Parts Table), and then "assign" the parts to indivual stations with the amount there

Is this a good way of setting up this database? I'm new to database design, just would like some suggestions

One other related question:

The data itself works with the tables fine, but I'm having problems with the forms

I setup a form with selecting Station, then Selecting SubStation, and it bringing up the corresponding part list. However, when I want to user to add a part, since there will be an incredible amount of parts to select from, I need some sort of list that is brought up to select from (so it can be searched through) instead of simply a combo box.
Is there a good way of doing this in access

Thanks

Matt Lawso

 

[John Vinson]

Database design need suggestions and forms- multiple primary key problem

The purpose of this database is to be a list of parts in the department as well as to easily see which parts are at which station/substation (and the # there), as well as tracking whether it is critical or not (yes/no).

I also want to be able to see which station/substations each individual part is at.

ok...

Here's the design of my database:

Parts Table - Key Fields: Part Number, Part Description Other fields: Part Name, # parts ( I decided this based on the excel data that has already been collected.. this is more of an informal database so many of the parts (ie screws) have the same arbitrary part Number but different part descriptions )

ok... a bit unfortunate if the description is lengthy, and impossible
if it's a memo!

Station Table- Key Fields: StationName, Other Fields: StationDescription

SubStationtable: Key Fields: Substation, StationName

PartsStationJunction Table- Key Fields: StationName, SubStation, Part Number, Part Description, Other Fields: # Part at Station

Relationships:
All One to Many relationships linking primary keys:
Station Table to SubStation (StationName field)
SubStation Table to Junction Table (StationName ,SubStation)
Parts Table to Junction Table (Part Number, Part Description)

Looks ok

So, with this setup, the user will have to add the part to the master list first (Parts Table), and then "assign" the parts to indivual stations with the amount there.

Is this a good way of setting up this database? I'm new to database design, just would like some suggestions.

Looks excellent to me. If the parts table or the junction table gets
really large, the lengthy key may cause performance problems; if so
you might consider adding an Autonumber "surrogate key" to Parts and a
long integer Foreign Key to Junction, and maintain a unique Index on
Part Number and Part Description. You may also want to avoid using
fieldnames containing blanks - PartNumber will transfer to SQL/Server,
Part Number will not!

One other related question:

The data itself works with the tables fine, but I'm having problems with the forms :

I setup a form with selecting Station, then Selecting SubStation, and it bringing up the corresponding part list. However, when I want to user to add a part, since there will be an incredible amount of parts to select from, I need some sort of list that is brought up to select from (so it can be searched through) instead of simply a combo box.
Is there a good way of doing this in access?

See http://www.mvps.org/access/forms/frm0028.htm for one of a few ways
to do this.