On 2020-05-12 21:55:56 +0100, Peter Devoy wrote:
> >Is is possible to have two entries which have the same
> >address_identifier_general, street and postcode, but different
> >descriptions?
>
> Unfortunately, yes. The data comes from gov't systems to
> regulate the development/alteration of arbitrary pieces of property and
> those pieces do not always have a postal address. E.g. a farmer may
> one year apply to erect a wind turbine in "field north of Foo Cottage"
> and the next year apply to demolish "barnhouse west of Foo Cottage".
I see. So postcode, street, address_identifier_general, description
(from least to most specific) together identify an object.
Going back to your original question I think that in this case it is
actually useful to distinguish between NULL (unknown) and '' (empty),
and if unknown values are forbidden, enforce that with a non null
constraint.
Consider the following examples:
postcode | street | address_identifier_general | description
1234 | main street | 12 | ''
1234 | main street | 12 | NULL
1234 | main street | NULL | bike shed
2345 | '' | 12 | ''
The first one refers to the whole property at main street 12. The
second one maybe only to a part of it but we don't know which one.
In the third example tghe address_identifier_general is unknown. Some
bike shed on main street, There might be more than one, so PostgreSQL is
correct not to enforce the unique constraint.
In the last one there is no street name - it's not unknown, we know that
there is none because this is a small village which doesn't have street
names, just house numbers.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"