Generalised approach to storing address details

I want to set up a generic way of entering names and address details. Address details could be a multi-line address, a telephone number, an email address, or whatever communication medium may arise in the future.

I am thinking along the following lines. The concept needs to be fleshed out, but I would appreciate some feedback before I go too far down this path. It is quite possible that it is a well-known 'bad thing to do' (it feels a bit like EAV).

1. create a table for user-defined 'address components', such as street, suburb, town, postbox, postcode, zipcode, phone number, email address, etc. The table would simpy have a code and a description.
2. create a table for user-defined 'address definitions', eg postal address, delivery address, email address, fax number, etc. There would be a header table with code and description, and a details table, containing one or more rows per definition, with columns for a component code, a sequence indicator, and a 'required/optional' indicator.
3. create a table to store the actual addresses. Primary key would be organization id, address definition code, address component code,with one additional column to store the actual value.

One problem I can see is that it would not be possible to make a foreign key constraint between, say, a zipcode and a table of zipcodes, though this could be enforced at the application level.

Any comments will be appreciated.

BTW, here is a link to a discussion of EAV and its pitfalls. I found it interesting. Any pointers to other online literature on this and similar topics would also be appreciated.

http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

Thanks

Frank Millman Received on Thu Dec 07 2006 - 06:53:28 CET