I have 4 tables
ConsigneeAddress - holds address and ShipmentId
ShipperAddress - holds address and ShipmentId
AgentAddress - holds address and ShipmentId
Shipment has ShipmentId, Shipmentdate plus other columns which arent important to mention here plus with the addition of ConsigneeAddressId, ShipperAddressId, AgentAddressId.
These columns are related via fk's
There is the argument that I could hold Consignee, Shipper and Agent address in a single Address table but i'll.come to an issue with that later
Currently my issue is that if shipment has 10 million records it means consignee, shipper and agent must have 10 million records too
So when it comes to reporting and doing inner joins to get data within certain shipmentdate range, it becomes horribly slow...
What to do???
As mentioned puttine all addresses in one Address table wont solve the issue either as it.will.contain 3 records for every shipment.. which means 10 million x 3 in 1 table...
Would be great is someone could help.