Relationships using multi-field compound keys setup & Possible Redundant Indexes setup

explrll

New Member
Joined
Apr 6, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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


Parts Tbl IDVendor Table IDVendor PNOur Part NumberPart DescriptionCurrent Supplier List PriceCurrent Supplier Contract PriceCore ExchangaeableCurrent Supplier Exchange PriceCurrent Supplier Outright PriceCurrent Core Exchange ValueMost Recent Date Updated In DatabaseFile Source Location ID
1​
20​
34249099209940Recent
$30.00​
$0.00​
No​
$0.00​
$0.00​
$44,826.00​
9/22/2022​
25​
2​
763​
0934U09902939DFLJKASD
$0.00​
$40.00​
Yes​
$0.00​
$0.00​
$0.00​
9/5/2022​
2​
3​
1452​
34240990293DFLJKAS
$0.00​
$40.00​
Yes​
$0.00​
$0.00​
$0.00​
9/5/2022​
24​
4​
1452​
30-15420000261872Levo Cart Power Supply
$200.00​
$300.00​
No​
$0.00​
$0.00​
$0.00​
9/8/2022​
22​
PartsPriceHistoryTBL


Pricing History Tbl IDVendor Table IDVendor PNFile Source Location IDOur Part NumberPart DescriptionSupplier List PriceSupplier Contract PriceCore ExchangaeableSupplier Exchange PriceSupplier Outright PriceCore Exchange ValueDate Updated In Database
1​
20​
3424
1​
9099209940sdlfj
$0.00​
$20.00​
No​
$0.00​
$0.00​
$0.00​
9/5/2022​
2​
763​
0934U
2​
09902939DFLJKASD
$0.00​
$40.00​
Yes​
$0.00​
$0.00​
$0.00​
9/5/2022​
4​
1452​
30-1542
22​
0000261872Levo Cart Power Supply
$200.00​
$300.00​
No​
$0.00​
$0.00​
$0.00​
9/8/2022​
3​
1452​
3424
24​
0990293DFLJKAS
$0.00​
$40.00​
Yes​
$0.00​
$0.00​
$0.00​
9/5/2022​
6​
20​
3424
25​
9099209940Recent
$0.00​
$30.00​
No​
$0.00​
$0.00​
$0.00​
9/22/2022​
FileLocationTBL


FileLocationTBLIDVendorTBLIDFileLocationMonth/seasonRecievedYearRecievedVendorSysID
1​
20​
testing1Feb
2019​
f759251edb7ab74059a27e09af96199c
2​
763​
testing2c3
2020​
262de06cdbea23c048ad776baf96190a
22​
1452​
C:\Users\TESTING3.xlsxQ2
2019​
f9bc6d19db7bf604f5d770d9af961973
24​
1452​
C:\Users\TESTING4.xlsxJan
1991​
f9bc6d19db7bf604f5d770d9af961973
25​
20​
TESTING5Jan
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?
 

Attachments

  • Relationships.PNG
    Relationships.PNG
    30 KB · Views: 13
  • PartsTblIndexes.PNG
    PartsTblIndexes.PNG
    13.2 KB · Views: 14

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I'd suggest you start with removing the compound fields and create compound indexes instead. Then rename your fields to make it easier for those who don't know the business or process to help with issues. That could even be someone who takes over this for you down the road. I would also leave out tbl from field names as it's redundant. Fields can only be in tables thus there's no need to identify that it is contained in any table. That will shorten names.

Suggestions:
- VendorTbl.VendorIDpk joins to OtherTbl.VendorIDfk (primary key to foreign key). Using composite index will allow you to join on only one field - the primary one. Joining bulkupdateID to partsID is just not intuitive. Neither is relating file location to vendor. If it's hard to understand, it will be hard for anyone else to figure out what's going on. Even you might be stumped 12 months later when you need to change something.
- use autonumber pk field to make the joins to foreign key fields
- You will not be able to perform certain operations because you've joined two fields for which neither is a pk (sysid to vendorsysid)
- watch your spelling as it can cause issues when you spell one way in fields/tables and another way in code ( look for recieved)
- ditch the special characters in names ( / ) etc. If you must, only use underscore.
- vendor table should be joined on parts

A study of db normalization might be helpful to you before doing too much more. Here is all the advice I like to give as it covers a whole lot of fundamentals.
Normalization Parts I, II, III, IV, and V
and/or

Entity-Relationship Diagramming: Part I, II, III and IV

How do I Create an Application in Microsoft Access?

Important for success:
Naming conventions - General: Commonly used naming conventions

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - Multivalued Fields
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top