Parts Interchange Database Design Help

[bubba61]

Hello, I am a novice with Access. I want some help in designing a database. I work for a new car dealership parts department and have wanted to create a database to store part number interchanges between the OEM dealer and aftermarket. At our dealership, we are constantly shopping parts for our customers in our service department either because of price or availability. In our small town, we have 4 parts stores and it can take up to 15 to 20 minutes to call each parts store to locate a part. Usually each store has particular brands, which involves an application lookup each time. My thought is it would be much quicker if we could already have the interchangeable part numbers available. So I thought it would be beneficial to develop a database to accomplish this. I am thinking that I need at least 5 tables. A table (Manufacturer) to store manufacturer information, which would include fields “Man_ID,” Man_Name,” “Man_Address,” “Man_City,” “Man_State” and “Man_WebAdd.” A table (Brand) to store parts brands, which would include fields “Brand_ID” and “Brand”. A table (Product_Desc) to store part descriptions, for example Oil Filter, Air Filter, Spark Plug etc. It would consist of fields “Product_Desc_ID” and “Product_Desc.” A table (Product_Part) to store part information such as part number, warranty info, and notes. The fields would be “Part_ID,” “Part_No,” “Warranty,” and “Notes.” A table (Store) to store Parts Store information, which would consist of fields “Store_ID,” ” Store _Name,” “Store _Address,” “Store _City,” “Store _State” and “Store_Phone.” Do I need other tables to tie some of these tables together? I also was wondering if I need to consider using a “Master Parts ID” to tie all interchangeable part numbers together? If so, should I also consider using a systematic auto number field, such as AF0001(for Air Filters), OF0001(for Oil Filters), SP0001(for Spark Plugs) etc.? If so, how would I do that? Any help and advice before designing this would be greatly appreciated.
Thanks,
James

[rpeare]

you'll likely want to have another table 'Part_Types' with PT_ID, PT_Desc (this would hold your oil filter, spark plug, air filter, etc) then you don't have to generate your own ID and you'll get a consistent description instead of 'oil filters' vs 'oil filter' which would be treated as two different things if you typed them in.

If I understand you correctly the stores you're trying to correlate parts with are not affiliated with your business, you're just trying to facilitate looking up THEIR part numbers based on YOUR part numbers.

if that's the case you'll probably want the following as well:

a table with a PK and the other local business information (name, address, phone, contact, etc)
a table with a PK YOUR item number, the local business FK and the local business Item number.

If you set it up this way you're only populating when you have a 'match'

For instance let's say 3 of the 4 businesses carry a specific air filter, you'd have TWO records for the air filter (you are already maintaining one in your database for YOUR shop).

[bubba61]

"you'll likely want to have another table 'Part_Types' with PT_ID, PT_Desc (this would hold your oil filter, spark plug, air filter, etc) then you don't have to generate your own ID and you'll get a consistent description instead of 'oil filters' vs 'oil filter' which would be treated as two different things if you typed them in." Good Idea! thanks

"If I understand you correctly the stores you're trying to correlate parts with are not affiliated with your business, you're just trying to facilitate looking up THEIR part numbers based on YOUR part numbers." That's right.

"if that's the case you'll probably want the following as well:

a table with a PK and the other local business information (name, address, phone, contact, etc)
a table with a PK YOUR item number, the local business FK and the local business Item number."

Not sure what you are talking about? Are you talking about an additional "Store" table?

Thank you for all of your help.
James

[rpeare]

you are trying to reference YOUR part number with other business' part numbers.

So think about it in purely visual terms

My Part Number | Other Local Business | Other Local Business Part Number
10               Tom's Auto             AA42
10               Dick's Auto             BBXXX

You want a table that will be able to reference the information the same way so you can look up your part number, then look at the business you're trying to contact then look at their part number.

To do this you need a table with the other local business information with a PK (primary key, autonumber works fine) so that when you're doing data entry you can choose from a list of businesses and, just like your part descriptions (oil filter, air filter, etc) you do not want any inconsistencies in the spelling so you limit it to a list of items from a table that can't be altered without data additional maintenance data entry.

The second table will store an individual cross reference ID (again a PK autonumber field would be fine), a reference to the other local store (the FK is the foreign key to the other local business table) and THEIR part number.

so let's say you have the tables

tblPartType
PartTypeID  PartTypeDesc
1           Air Filter
2           Oil Filter

tblManufacturer
MfgID  MfgName
1      XXX
2      YYY

tblParts
PartID  PartTypeID  MfgID  PartNumber ---> other part related fields
1       1           1      XXX Airfilter Model A
2       1           1      YYY Airfilter Model A
3       2           2      NNN Oil Filter Model J
4       2           1      MMM Oil Filter Model K

tblPartners
PartnerID  PartnerName  ----> other partner related fields (phone, address, etc)
1          Tom's Auto
2          Dick's Auto
3          Harry's Auto

when you're doing data entry or lookups you'd have a combo box that displays only your trading partner name, but the bound column would be the partnerID

Then in you'd have a cross reference table

tblCrossRef
CrossRefID  PartnerID  PartID  PartnerPartNum
1           1          1       MMM-42634
2           1          2       MMM-42635

So your cross reference field has a PK (CrossRefID) The partner that carries it is the FK to your partners table (in this case Tom's Auto), the partID is XXX Airfilter Model A, but you're storing the part ID which then lead you back to the manufacturer and the part type (air filter)

[bubba61]

Thanks rpeare! I'll look it over.
James