Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    128

    Several address columns or just 1?

    Howdy all. I have a developer proposing a DB schema that has 4 different tables containing address columns (Vendors, Service Provider, Locations, Payment Address). Each of these has Address1 and Address2. It seems to me that it would be better to have 1 address table, with a flag for which address type it was. That way I could keep all address' together, and eliminate Address2 at the same time. Would you agree?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Generally Id agree, a single table could be a good idea... however how do you propose to ensure RI.

    bearing in mind that one entity may have mor ethan one role (ie they could eb a customer and a supplier)

    some organsiations may have many addreszsess (eg a multi branch company)

    granted you could "just" do it as an address type, and contact type and associate in SQL. perfectly practical, just against the theory of RDBMS imposing constraints ie your address owner is the prime key of the custoemr / supplier / whatever and you enforce RI using a query / trigger make sure the relevant RI rules are enforced

    in any event I'd want at least 5 columns for the address & postcode
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    "Address2" is normally used to store additional address information such as apartment number or floor. In such cases, it does not violate the principles of normalization.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Posts
    128
    Quote Originally Posted by healdem
    Generally Id agree, a single table could be a good idea... however how do you propose to ensure RI.

    bearing in mind that one entity may have mor ethan one role (ie they could eb a customer and a supplier)
    I could have a table in between ("bridge table", "many to many table", whatever it's called) that would then allow an address to have many types. But then I really don't know what I'd be getting out of the deal.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It struck me that the OP was talking about a second address in its entirety rather than an attribute of an address entity (i.e. two addresses per record, one per column, rather than two records with one address each).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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