Storing Street Addresses and Postal Codes

I'm trying to decide how to store some address information. This is what I have in mind (it's in Django model form, but you should be able to read it)

class City(models.Model):
    name = models.CharField(max_length=100)
    province = models.ForeignKey(Province)
    data_source = models.PositiveIntegerField(default=DataSources.USER)
    latitude = models.DecimalField(max_digits=18, decimal_places=12, null=True, blank=True)
    longitude = models.DecimalField(max_digits=18, decimal_places=12, null=True, blank=True)
    feature_class = models.CharField(max_length=1, null=True, blank=True)
    feature_code = models.CharField(max_length=10, null=True, blank=True)
    population = models.BigIntegerField(null=True, blank=True)
    elevation = models.PositiveIntegerField(null=True, blank=True)
    time_zone = models.CharField(max_length=40, null=True, blank=True)
    mod_date = models.DateTimeField(auto_now=True, auto_now_add=True)

class Province(models.Model):
    code = models.CharField(max_length=2, primary_key=True)
    name = models.CharField(max_length=100)
    country = models.ForeignKey(Country)

class Country(models.Model):
    code = models.CharField(max_length=2, primary_key=True)
    name = models.CharField(max_length=100)

class Address(models.Model):
    name = models.CharField(max_length=100)
    street = models.CharField(max_length=200)
    postal_code = models.ForeignKey(PostalCode)

class PostalCode(models.Model):
    code = models.CharField(max_length=10)
    city = models.ForeignKey(City)
    data_source = models.PositiveIntegerField(default=DataSources.USER)
    latitude = models.DecimalField(max_digits=18, decimal_places=12, nul开发者_如何转开发l=True, blank=True)
    longitude = models.DecimalField(max_digits=18, decimal_places=12, null=True, blank=True)

I've already got a database full of cities, provinces, countries, and postal codes. For the most part, users will only be entering addresses.

This way, if a user enters a postal code, I can populate the city, province and country fields automatically for him. But what if my data is out of date, or is incorrect? Then the user just has to update those fields with the correct information, and then I can update my database accordingly.

But what happens then if he enters someone else's postal code, and then updates the city to something incorrect? He's just broke someone else's address.

So maybe I should move the city field to the Address class instead? But then I can no longer look up the city from the postal code.

So then maybe I should just have cities in both models? But now I've got duplication. Is this still the best option?

Not really. You are mixing up two different functional aspects.

1. Normalise your data so that there is no duplication. otherwise your database is crippled.

   • Sure, you have to allow for cities that cross state boundaries, towns that have two pst codes, etc.

2. Security. Why on Earth would you allow any person to update Reference (the tables that constitute static data that make up an Address) tables ? That should be reserved for Admin users. And periodically, you need to update the Reference tables from your local council or whatever.

IMHO you can keep the design.

If your city is incorrect, your users will update it. If you think the users could be wrong too, just wait for X persons to update your data with the same information the first user gave, then update your database accordingly.

I'm keeping the design and just putting the unique constraint on (postal_code,city), and (city,province) instead. That way the same entries can appear twice if they spill over to another region.