I have a names and addresses table:
Name Address AddressType ID CustID
Bob Smith, 123 Main St, Corporate, 1, 1 Bob Smith, 123 Main St, Home, 2, 1 null, P.O. Box 123, Warehouse, 3, 1
and I have a customer table
ID TaxID BusinessType etc 1, 94-123456798, Consultant
The customer may have many addresses.
I need to query on specific address types and do CRUD operations. For example, what is the corporate address? Retrieve and edit the DBA and Corporate addresses. Where is the inventory kept?
So my first inclination is to modify the customer table to add CorpAddressID, InventoryLocationID, etc. There might be a slew of old and new addresses but certain addresses have to be designated clearly.
So then I thought I could just reference the address type -- Customer join Address where AddressType = Corporate -- I'm worried about doing this and I have no idea why. Like having to hard code where AddressType = Corporate and relying on the Corporate designation being stored in the database.
I checked this out and it was similar but not close enough to help:
So, I ask, is there a compelling reason to not reference the name and address field IDs directly from fields such as CorpAddressID, DBAAddressID, etc?
Any thoughts or feedback would be greatly appreciated.