Problem modelling address generalization

Question

Hello,

I’m a bit concerned about how to model this rather simple scenario the best way in UML and ERM. When designed it has to be implemented in C# (Entity Framework 4) and SQL Server 2008.

The case is that I have a person, who has a postal address. Now this address can be two types, either a postal box, or a house identifier (house number, street, etc.). I believe that this is called generalization, and might be modelled the following way using UML:

http://i.imgur.com/Vzx4Z.png (sorry for the link, but I don't have enough reputation to post images yet)

PostalAddressPostalBox and PostalAddressHouseIdentifier will of course have more properties and relations to other entities/tables. Same goes with the Person class. Also other classes will have a reference to a postal address, so it's not only persons.

My question is if this is the correct way to model this problem, and how I can implement it in SQL (schema wise) and what my entities in C# should look like?

Thank you in advance

Answer 1

I just think you shouldn't reuse the PostalAddress for different entities. It will just be harder to change the address for a given entity.

For the relational database, there are some options, like class table inheritance:

Or maybe single table inheritance or concrete table inheritance. It's up to you to decide the tradeoffs.

Answer 2

In you database, you'd have a table with postalAddress. In that, you'd have fields postID, postAddressType, postalBoxCode, houseNo, streetName.

In you Person table, it'd contains a foreign key of postID.