I have four tables:
- Province (province_id, name)
- City (city_id, name, province_fk)
- Another Smaller subset under city say… City_sub (sub_id, name, city_fk)
- Address table (address_1, address_2, sub_fk, person_fk)
Had an argument that it should be N-1, i.e. many-to-one, (actually he said N-N [many-to-many] but I limited the address for a user to be 1 only so let's change it) because there might be 2 persons living on the same house
But how would you query it to ensure that there will be no repeated data in the Address table? Address lines itself is already vulnerable. There are some people who type "street", "st.", and misspelled "stret".
Well what do you guys say? I think the saying "base your model on the real world" has limitations.
I am currently siding with my opinion which is 1-1, i.e. one-to-one, (1-N [one-to-many] if I returned to allowing persons to have multiple addresses).
The person I argued with tells me that this would have "data quality issues".
Best Answer
The easiest and more realistic real world scenario to maintain and query is going to be either 1-1 or 1-N, choose whichever is appropriate for your business logic. Several of the products I've worked on have used either of these methods and they are far easier to maintain than trying to get N-1 to work.
Maintaining N-1 for addresses would very challenging because you will inevitably run into misspelling and different grammar scenarios (as you mentioned), but additionally you will get convoluted logic to maintain the single record reference on a table that may not necessarily be static.
Something else we've learned is to avoid separate State, County, and City reference tables as they can create some convoluted logic for querying addresses. Try turning those tables into a single hierarchical Jurisdiction table and store the 'smallest' appropriate reference (usually the town) for the address record. The hierarchy maintains the other references and allows easy expansion when necessary.
If you're using MS SQL they have a HierarchyID type for this specific use case, but you can also create your own with a simple path style NVARCHAR field combined with a type field. Some text indexing and trailing wild card queries will cover all your expansion and filtering needs. Even if using MS SQL I would recommend using the NVARCHAR option alongside the HierarchyID as it generally performs better than the HierarchyID functions in most cases.