Re: Question on Addresses and Not Null constraints

["Chris Travers" <..hidden..>]

On Thu, May 22, 2008 at 5:23 AM, Roderick A. Anderson <..hidden..> wrote:

>
> Since I'm just getting started with LedgerSMB (after years away from
> SQL-Ledger) I'm not sure what the installation base is nor what is it
> going to be.
>
> If there is a need to send /reliably/ via. a postal service (or even
> delivery services) to non North American addresses all the correct
> pieces needed to do this should be there.

Agreed.  However, I think that doing this properly will require more
db normalization and complete redesign of contact management
interfaces.  Furthermore, since address requirements tend to be very
tricky I would rather spend more time gathering requirements and less
time recoding things because new requirements are discovered.

> Depending on how quickly this
> needs to be in place (v1.3, v1.4) either a low-hanging fruit approach
> (less NOT NULL constraints), with a ladder waiting, or just build it
> with all the right parts which may include less NOT NULL attributes.

Longer-run, I would like to see cities and states/provinces normalized
out of the location table.  This would allow for better UI design,
constraint enforcement, and the like.  However I am not sure that this
makes sense to change for 1.3 since:

1)  We are getting closer to a beta
2)  This would require substantial re-engineering
3)  I am not convinced that we have enough time and energy to provide
the kind of review that would be necessary for this sort of change
until we start on 1.4.  The last thing I want to do is spend a lot of
time re-engineering a solution that turns out to prevent a substantial
number of users from upgrading.

>
> I got dragged off to other projects and I haven't had a chance to look
> at the model so this may be totally off target.

No, I think it is completely on-target :-)

>   At least two tables, the main address table would have the common
> stuff plus an address type identifier.

What we actually do currently is have an address table, a mapping
table which connects it to an entity along with a type.

I am thinking that we may want to break out at least the state from
the address table.  This would allow one to manage state data in a way
which would be fundamentally more strict.  Then we can talk about
breaking the city out later and see if this makes sense.

My general feeling is that a single free-form field for street address
may make more sense than line_one, line_two, and line_three since
these vary considerably from place to place.  But we can look at this
later too.

>  Other table(s) with address type
> specific attributes. These might be pretty sparse if all the /other/
> address fields are included in one table but there would only be one
> tuple/row if it is needed for the address.
>
> Of course I may be making this too complicated as my address experience
> is with bulk mailing, high quality, direct mailed magazines, in the
> range of 1/4 to 1/2 million every two months.

Long-run I think we want to have good address handling.  IIRC,
Peoplesoft uses some insane number of tables for storing addresses.
While I am not sure it makes sense (yet) to go that route, it is worth
considering that address modelling is difficult because of regional
variations in format and semantics of the addresses.  (Managua
represents an extreme example:
http://www.worldpress.org/Americas/592.cfm)

Best Wishes,
Chris Travers