Null Foreign Key or not?

[TonyT]

Hi all,

Is there a definitive view on whether to allow null's in foreign keys, or
whether it is better to have a bogus entry in the parent table to substitute?

My database has UK address' for customers with Parent tables for Road, Town,
Area/Region & County, then child fields in the Customer Table. More often
then not Area has no value, but when it does have a value it is often the
same as Town.
Currently I have required=False for each child FK, which makes a few queries
troublesome, but then so does coding around null entries in the bound
comboboxes for entering the data.

Which way is best?

And is having Town and Area FK's pointing to 1 Parent Table (Town) a bad
idea or not?

(Most of my customers work within a 60 mile radius of their base, so they
are always fairly limited entries in County & Area.)

opinions appreciated,

TonyT..

 

[Douglas J. Steele]

I wouldn't use a bogus entry. What you're describing is exactly what Null is
for. You just need to ensure that you're using a LEFT JOIN or RIGHT JOIN.

 

[J. Goddard]

Hi Tony -

Obviously I don't know your requirements, but can I ask why you would
have separate tables for each part of an address? After all, any given
address can have only one of each part (road, town, etc.). Would it not
be a lot simpler to just include the address as part of the customer
table, and then identify customers with a Customer_ID? That is how
customer databases would usually be set up (IMHO). Seems to me that
using that data organization would make life a lot simpler.

Combo boxes could still be used in data entry - their data source can be
the same table as the one they are populating, and it's easy enough to
keep the entries in their lists unique.

Just a thought

John

 

[TonyT]

Hi John,

The version in current use by my customers has just as you suggest, but
almost all of them have *alot* of mispelled versions of the same road or town
name despite using comboboxes as the selection, it seemed easier to code
around putting it right, also if the business is in Leeds, then Leeds will
appear in 90%+ of all the Customers records, and with that percentage a
separate field seemed sensible.

TonyT..

 

[Damian S]

Hi TonyT,

If you have access to a list of all streets in the UK you might consider
using a lookup table for this. If you don't have access to that list,
manually creating the entries is going to be a real pain in the butt.

I understand where you are coming from though - the less a user has to type
the better your data integrity/quality will generally be. Have you
considered the use of some Address Translation software? Here (in Australia)
there are several companies who sell such software that you can then
incorporate into your design - these types of software prevent users from
selecting an address that doesn't physically exist.

Hope this helps...

Damian.

 

[David F Cox]

The post code and the building number uniquely identify premises in the UK.

 

[John Nurick]

Not so. Where I live, for example, I share a postcode with other
addresses in this part of the street, and a building number with the
other 30-odd flats in this complex. So to identify my premises you need
flat number, building number (or building name) and postcode.

Conversely, many buildings have more than one postcode. Where I work,
there are three: one each for the two big firms that occupy most of the
building, and one for any others.

 

[David F Cox]

Sorry, you are quite right. I have checked up on the definition of
"premises" and find it has more than one definition, and part of a building
is one that I was not aware of. Still learning.

 

[TonyT]

Hi Jamie,

Thanks for feedback, guess I'm on track with the GovTalk structure with 2
out of their select at least 1 from 3 approach, not sure the Royal Mail agree
with that, in fact I know they don't!
I see the reasoning behind the ISO approach, but it would never be accepted
by my customers or their customers for that matter. It still amuses me how
upset people can get if you don't get their address exactly as they gave it
to you (general public here, not corporate), and bring to mind a chap I knew
who took the Royal Mail to court to be able to name his house 34 1/2 rather
than 34a!!

TonyT..

 

[TonyT]

Hi TonyT,

If you have access to a list of all streets in the UK you might consider
using a lookup table for this. If you don't have access to that list,
manually creating the entries is going to be a real pain in the butt.

I don't at the moment, and it is

I understand where you are coming from though - the less a user has to type
the better your data integrity/quality will generally be. Have you
considered the use of some Address Translation software? Here (in Australia)
there are several companies who sell such software that you can then
incorporate into your design - these types of software prevent users from
selecting an address that doesn't physically exist.

I know there are lists available to purchase, but I provide software to a
small number of businesses in a very niche market, most of whom have to buy a
pc first, then Access, then a barcode scanner, printer.................need I
say more.

TonyT..

 

[Tim Ferguson]

See this article

Design Challenge: Global Address
by Steve Hoberman
http://www.dmreview.com/portals/portalarticle.cfm?articleId=1062041&top
icId=230291

URL doesn't work here: it demands a registration. What does the article
say?

I agree the approach you allude to in your later reply i.e. the
address you want it the one Royal Mail need to be able to deliver mail
to the individual/organisation. So why have a table of administrative
areas?

Trouble is that "an address" doesn't mean very much in database design
terms. It could be a piece of dumb text that has to go on an envelope; a
way of grouping people or places geographically; a method of identifying
a person (e.g. common in health care); a business product (real estates,
landlords, etc); an index into some kind of mapping or routing system;
membership of a family; and many more. I would claim that each of these
represent different needs on the business model, and therefore will need
different modelling in the schema. For some, a simple Memo or Text field
is more than enough: others will require much more sophisticated
handling.

Just my tuppenceworth!
All the best


Tim F

 

[TonyT]

Modelling postal addresses in SQL is an interesting topic; there's no
easy, universal answer. Is it an attribute or an entity? etc.

To my way of thinking it's an entity, hence the separate tables.

See this article

Design Challenge: Global Address
by Steve Hoberman
http://www.dmreview.com/portals/portalarticle.cfm?articleId=1062041&topicId=230291

I ccba to register tbh, but if it mirrors their thinking of their
registration form I don't like it one bit!! Street Address, I live in an
Avenue not a street, A State combo for US only, which doesn't change if you
select another country (nice to be able to choose United Kingdom or Great
Britain for a change, but as usual no England!). City and then Province
doesn't work for UK either, but then hey, looks like they want to cater to

I don't agree their solution (it doesn't fit my postal address!)

I agree the approach you allude to in your later reply i.e. the address
you want it the one Royal Mail need to be able to deliver mail to the
individual/organisation. So why have a table of administrative areas?

Because some Town's will be populated with postal villages or hamlets so
small that they are virtually unkown 10 miles away, in which case
Region/Area becomes important for delivery purposes (business' own not so
much Royal Mail).

I think on balance I will opt for double linking to Town table, even less
work to correct mis-spelt entries.

feedback much appreciated,

TonyT..