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".