CODEWITHSUNDEEP
database-design
Ehsan Korhani
Ehsan Korhani

Reputation: 75

What is the correct approach to maintain relationship between classes (tables) in the following example?

In the following typical example:

Country has one-to-many relationship with City.
Should Customer class have only a reference to City or to Country as well?

public class City
{
    public int CityId { get; set; }
    public int CountryId { get; set; }

    public virtual Country Country { get; set; }
    ...
}

public class Country
{
    public int CountryId { get; set; }

    public virtual ICollection<City> Cities { get; set; }
    ...
}

public class Customer
{
    public int CustomerId { get; set; }
    public int CityId { get; set; }
    public int CountryId { get; set; }

    public virtual City City { get; set; }
    public virtual Country Country { get; set; }
    ...
}

Normally even without defining the Customer-Country relationship we can access that via:
customer.city.country
and we can maintain the integrity as well.

However, I've mostly seen people add an explicit Customer-Country relationship because customer.city.country is weird and they want the simple customer.country.
Or in other cases, they create an Address class (value object) in between.

What is the correct approach?

Upvotes: 1

Views: 64

Answers (1)

Michael
Michael

Reputation: 445

In most cases, you will just want the customer to have a reference to a particular city. It will not be computationally heavy to access a customer's country by using their city, so that should not be a major concern.

The bigger concern would be your data integrity. What happens if a customer's city is Chicago, but their country is China? By having both references, you have to deal with more data validation.

In addition, having the single reference to the city follows the rules of database normalization. If a customer directly references the country, you will violate the 4th normal form (having attributes that can be derived from other attributes).

While we can debate the merits of having a normalized database, in this situation, the relationship between cities and countries is already defined, so there is no need to redefine that relationship by "shortcutting" a foreign key between customers and countries.

Upvotes: 1

Related Questions