Hello,
So I have not built, managed, or even worked in a database for at least 3 years.
I am building a Microsoft DB to do the following. Regularly, our company receives current/updated parts pricing & parts details lists from hundreds of different suppliers. Every time we receive these lists we have to update our systems parts information accordingly & save the file on the share drive for our reference.
Monthly we run a Price Variance report comparing our quoted pricing with what we paid. In our PV report, we have to create a unique vlookup for Every SUPPLIER. Pulling the most recent contracted prices from Every Supplier's Most Recent current/updated parts pricing & parts details lists. This is a very tedious process requiring us to locate each file.
In the future, our process will change. Every time we receive a Supplier's current/updated parts list, we want to import this file information & contents into our DB so we can query current prices, file history, part pricing history, and compare pricing on common Part Numbers between suppliers/vendors.
The DB has a suppliers/vendors table, FileLocation table that will track each files location, date received, & supplier/vendor, Parts Table storing all unique part numbers for each supplier/vendor with the current pricing, PartsPriceHistory table to track all pricing history, and Parts detail table.
I am having trouble remembering how to properly set up my table keys, relationships, & indexes.
Specifically any recommendations on the relationship between the PartsTbl & PartsPriceHistoryTbl?
The parts table should only keep a unique part number for each supplier/vendor as the compound key
The PartsPriceHistoryTbl should store the price history based on the unique compound key containing the part number for each supplier/vendorID & FileLocationId as the compound key.
PartsTBL
PartsPriceHistoryTBL
FileLocationTBL
PartsTbl Indexes - Primary key/compound contains VendorPN & VendorTblID, additionally we will regularly query FileLocation, VendorPN, & VendorTblID information for queries so I have an index for each Field. Since the Primary key/compound index contains the VendorPN & VendorTblID, is it redundant to have individual field indexes for these fields?
So I have not built, managed, or even worked in a database for at least 3 years.
I am building a Microsoft DB to do the following. Regularly, our company receives current/updated parts pricing & parts details lists from hundreds of different suppliers. Every time we receive these lists we have to update our systems parts information accordingly & save the file on the share drive for our reference.
Monthly we run a Price Variance report comparing our quoted pricing with what we paid. In our PV report, we have to create a unique vlookup for Every SUPPLIER. Pulling the most recent contracted prices from Every Supplier's Most Recent current/updated parts pricing & parts details lists. This is a very tedious process requiring us to locate each file.
In the future, our process will change. Every time we receive a Supplier's current/updated parts list, we want to import this file information & contents into our DB so we can query current prices, file history, part pricing history, and compare pricing on common Part Numbers between suppliers/vendors.
The DB has a suppliers/vendors table, FileLocation table that will track each files location, date received, & supplier/vendor, Parts Table storing all unique part numbers for each supplier/vendor with the current pricing, PartsPriceHistory table to track all pricing history, and Parts detail table.
I am having trouble remembering how to properly set up my table keys, relationships, & indexes.
Specifically any recommendations on the relationship between the PartsTbl & PartsPriceHistoryTbl?
The parts table should only keep a unique part number for each supplier/vendor as the compound key
The PartsPriceHistoryTbl should store the price history based on the unique compound key containing the part number for each supplier/vendorID & FileLocationId as the compound key.
PartsTBL
| Parts Tbl ID | Vendor Table ID | Vendor PN | Our Part Number | Part Description | Current Supplier List Price | Current Supplier Contract Price | Core Exchangaeable | Current Supplier Exchange Price | Current Supplier Outright Price | Current Core Exchange Value | Most Recent Date Updated In Database | File Source Location ID |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 20 | 3424 | 9099209940 | Recent | $30.00 | $0.00 | No | $0.00 | $0.00 | $44,826.00 | 9/22/2022 | 25 |
2 | 763 | 0934U | 09902939 | DFLJKASD | $0.00 | $40.00 | Yes | $0.00 | $0.00 | $0.00 | 9/5/2022 | 2 |
3 | 1452 | 3424 | 0990293 | DFLJKAS | $0.00 | $40.00 | Yes | $0.00 | $0.00 | $0.00 | 9/5/2022 | 24 |
4 | 1452 | 30-1542 | 0000261872 | Levo Cart Power Supply | $200.00 | $300.00 | No | $0.00 | $0.00 | $0.00 | 9/8/2022 | 22 |
| Pricing History Tbl ID | Vendor Table ID | Vendor PN | File Source Location ID | Our Part Number | Part Description | Supplier List Price | Supplier Contract Price | Core Exchangaeable | Supplier Exchange Price | Supplier Outright Price | Core Exchange Value | Date Updated In Database |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 20 | 3424 | 1 | 9099209940 | sdlfj | $0.00 | $20.00 | No | $0.00 | $0.00 | $0.00 | 9/5/2022 |
2 | 763 | 0934U | 2 | 09902939 | DFLJKASD | $0.00 | $40.00 | Yes | $0.00 | $0.00 | $0.00 | 9/5/2022 |
4 | 1452 | 30-1542 | 22 | 0000261872 | Levo Cart Power Supply | $200.00 | $300.00 | No | $0.00 | $0.00 | $0.00 | 9/8/2022 |
3 | 1452 | 3424 | 24 | 0990293 | DFLJKAS | $0.00 | $40.00 | Yes | $0.00 | $0.00 | $0.00 | 9/5/2022 |
6 | 20 | 3424 | 25 | 9099209940 | Recent | $0.00 | $30.00 | No | $0.00 | $0.00 | $0.00 | 9/22/2022 |
| FileLocationTBLID | VendorTBLID | FileLocation | Month/seasonRecieved | YearRecieved | VendorSysID |
|---|---|---|---|---|---|
1 | 20 | testing1 | Feb | 2019 | f759251edb7ab74059a27e09af96199c |
2 | 763 | testing2 | c3 | 2020 | 262de06cdbea23c048ad776baf96190a |
22 | 1452 | C:\Users\TESTING3.xlsx | Q2 | 2019 | f9bc6d19db7bf604f5d770d9af961973 |
24 | 1452 | C:\Users\TESTING4.xlsx | Jan | 1991 | f9bc6d19db7bf604f5d770d9af961973 |
25 | 20 | TESTING5 | Jan | 2000 | f759251edb7ab74059a27e09af96199c |
PartsTbl Indexes - Primary key/compound contains VendorPN & VendorTblID, additionally we will regularly query FileLocation, VendorPN, & VendorTblID information for queries so I have an index for each Field. Since the Primary key/compound index contains the VendorPN & VendorTblID, is it redundant to have individual field indexes for these fields?
