Person-to-Address Relationship Cardinality — One-to-One or Many-to-One (In this context)?

Question

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

Answer 1

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.

Answer 2

Got a good explanation from a user on a channel from freenode where I usually stay at.

The person I argued with seems allergic to repeating values like, even if there's only 1 out of thousand of records he's willing to do his tables N-N.

The user I talked to says, he seems to be a "classic DB people". Then he asked me this:

"for your exact use case: do you need to know that N people share the same address, or is it enough to just know Person X lives at addresses Y and Z"

and I answered that I need the second one only. He then followed with this reply:

"he's a DB guy... he'd abstract every single entitity in his DB.... you're an app developer, you only model what you need"

"and that's why 1-N is more than enough"

Never thought of this right away, but I think I know where did the person I argued with end up with that answer to me.

He has an API that lists out up to smallest division. Well, going back to N-N or N-1 that'll be possible.

If you're going to let the user type their address, it'll be hard and 1-N is really more than enough.

Answer 3

In theory, it is better you design a separate address table (without Person_fk in it), and then you can create an associate table, let's call it PersonAddress (Person_fk, Address_fk). This way, it does not matter where you have N:N relationship between Person and Address (i.e. a person may have multiple addresses or an address may host many persons).