![Auto parts Auto parts](/uploads/1/2/5/1/125179293/393355045.jpg)
Hello Everyone,
This timeline shows the size of the U.S. Automotive parts aftermarket from 2013 through 2025, by segment.
I am working on access database which has been designed to create purchase orders, make payments of the orders, track delivery of the orders etc. Actually, we do import spare parts for machinery from all over the world hence the database has been designed specifically for import department.
As I am working on the front end, therefore, I haven’t full access to the database model to make the amendments in the database design. However, I have found some flaws in the database design model through my little knowledge of database.
I have only access to the order form screen to enter the purchase requisition & spare parts details into the order form. As far as I know, the data may be stored only in two tables Inquiry & Inquiry Details tables which are linked through the InquiryID. I would prefer to talk only following three entities which are related to my query.
- Suppliers (1) Machine Manufacturers (2) Traders
- Products
- Brands
We have two categories of the suppliers as mentioned above first one is Machine Manufacturers and second one is Traders or you can also call them brokers who purchase parts on our behalf & supply us on commission basis. The details of both categories of suppliers are stored in Suppliers table. Actually, we purchase the same products from both Machine Manufacturers & Traders.
Machine Manufacturers use many parts of different brands in their machines and they assign their own unique part # of each product in order to identify the product when they receive price request from us. So, when we ask price for the product from Machine Manufacturers we have to provide their part # so that they can easily identify the required product & send their offer without any difficulty.
On the other hand, when we ask price for the same product of any brand from Traders they contact to the Brand Manufacturer to obtain the price of the requested product then Traders offer their price after calculating their commission / profit. Similarly, we have to provide part # of the Brand Manufacturer in order to identify the product.
![Automotive parts database Automotive parts database](/uploads/1/2/5/1/125179293/880575007.jpg)
The first problem we are facing in the database is to store multiple part numbers of each product. I mean Machine Manufacturers & Brand Manufacturers part numbers. In my case, Traders do not have their own unique part numbers of the products because they are just a middle man between buyer & manufacturer.
Auto Parts Database Software
The solution I found for this problem is that there is one-to-many relationship between product and brand. Because, one product can be available in many brands. So, we can add multiple part numbers by linking ProductID in the parent and child tables. But, my approach might be wrong so I would like to know the experts advice.
The second problem I found in the database is that there is a part # column in the Inquiry Details table, however, it hasn’t been assigned as primary key which has cause the redundancy of the data because same product has been entered in many inquiries with different part numbers. I thought the part # column must be unique to identify the product.
Automotive Parts Database
Experts advice are welcome.
Advance Auto Parts
P.S. Above I have mentioned Machine Manufacturers and Brand Manufacturers, both are different.