Re: Enforcing uniqueness on [real estate/postal] addresses

On 2020-05-12 10:49:22 +1000, Tim Cross wrote:
> Peter Devoy <peter(at)3xe(dot)co(dot)uk> writes:
> > I need to store addresses for properties (as in real estate) so in my
> > naivety I created a unique constraint like this:
> >
> > ALTER TABLE properties
> > ADD CONSTRAINT is_unique_address
> > UNIQUE (
> > description, --e.g. Land north of Foo Cottage
> > address_identifier_general,
> > street,
> > postcode
> > );
> >
> > Of course, if any of the fields are NULL (which they often are) I end
> > up with duplicates.
[...]
>
> Personally, I don't like the idea of using empty strings just to avoid
> having nulls. This is probably a personal preference, but for me null
> and '' are quite different. A null indicates an unknown - we don't know
> what the value is. An empty string i.e. '' means there is no value (i.e.
> we know it has no value). The difference is quite subtle and may not
> seem relevant. It may not be or it may be or it may become relevant in
> the future. General rule of thumb for me is that my model should reflect
> the known information and should always avoid any data transformation or
> mapping which reduces the known information.
>
> I would step back a bit and think about why/what constraint you really
> need and what needs to be unique. The first field which jumps out for me
> is description. Is this really a unique value?

As the coonstraint stands, it isn't. Only description,
address_identifier_general, street and postcode together are unique.

I'd therefore ask the question in the other direction:

Is is possible to have two entries which have the same
address_identifier_general, street and postcode, but different
descriptions? What does that mean? To different properties which happen
to be at the same place or two descriptions for the same property?

(What is an address_identifier_general, btw?)

I agree with the rest of posting.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"