Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006

    Question Relational DB design issues---NULL values

    I've read a few polls and there are mixed feelings about whether an address table should contain : address1; address2; address3; address4; as columns as they could have null values if the address is only 2 lines.

    Q1: Is this such a bad thing? What would the table/s look like if they were purely relational?

    Q2: Is there a way to avoid Null values in a relational design if values are only sometimes provided? I am storing "yes/no/not specified" and "if yes, describe" data. How would this be modelled? If no description is provided and yes is selected what, how and where do I store that information?


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    A1: No, it isn't. Fully relational addresses would be horribly overcomplicated.

    A2: Yes, by splitting the attributes off into their own tables, so that missing data requires no NULLS but instead is represented by missing rows | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Re A1, it is also the case that some DBMSs (and standard SQL) distinguish between an empty string '' and a NULL - so you could have address4 as a NOT NULL column and still set it to ''. Not in Oracle though.

  4. #4
    Join Date
    Sep 2005
    Why grab your data entry line1,line2,line3 & line4 and dump them into separate columns?
    Wouldn't it be simpler to dump it into a single column as (say)
    This is not breaking normal form as it is one item: an address.
    Date in 'Database in Depth' says that there is nothing wrong with doing things like this - his talk is about points - addresses are just as valid.
    We have a situation here where we are interested in the complete break-up of the address as we need to be able to sort by suburb, street name, street type, number and unit number.
    Saving the address as a delimited string makes sense to me. I don't want to have to handle multiple columns and multiple nulls.

  5. #5
    Join Date
    Nov 2004
    out on a limb
    if you address is for your own parochial needs then wrapping all lines of the address may make sense. however soem addresses are structured.

    for soem bizzaire reason the UK addresses are potentailly horrendously complex for non UK developers, the each line has a specific meaning (eg post town, county (think state for US) etc...

    wrapping these elements into one line doesn't help if you need to access your customer / supplier by these attributes. Although the postcode does contain geograpihical information its doens't neccesarily map to geopolicital areas. the 1st 2 digits of the post code identify the location of the major post office sorting station which could be local, it could be many many miles away in a different country.

    Irrespective of how information is presented, invariably I store an address as 5 lines plus a post code, depending on the application I may expressly identify lines 4 & 5 as county/state & country. line 3 as town, line 2 is optional (some addresses are name line 1, road line 2, some are name & number line 1.

    in short, yes you can compress addresses into one column, but you are potetnially throwing away valid inforamtion which may be needed later. if you know that you will never need such details then by all means store it as a single varchar block. however you can hit problems if you data is being represented on multiple platforms (eg a web interface or internal app.) there can be differences in how a new line is interpreted (some use linefeed, some CR + LF, etc.... if you store the lines as individual elements then you can provide whatever method of new line seperation you require.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2005
    We actually do need more information than just the address, suburb state and postcode are needed for sorting mail.
    And we do need the street number ordering information for a report which is run twice a year.
    Barcoding works best with a complete address (it probably has to do with the peculiarities of the barcoding software we use).
    We do not allow an address to be modified. The whole address has to be entered again. This is a small concern as most Australian addresses are 2 liners.
    Finally we can omit suburb state and postcode and instead provide a link into the postcode table which contains sort plan numbers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts