Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Location
    Houston, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    (Re)designing database (XP)

    I am relatively new to database development, so here is a table design question.

    I am working with a non-profit's existing database which contains the following tables: Contacts, Contributions, Events. Included within the Contacts table are the following fields: HomeAddress, HomeCity, HomeState, HomeZip, BusinessAddress, BusinessCity, BusinessState, BusinessZip. There are also checkbox fields to indicate which address to use as the mailing address: MailHome, MailBusiness. Amongst other things, I had difficulty creating a query that would give me just a list of the mailing addresses.

    Wouldn't the database work better if the address information was moved into its own table? I'm thinking it would be a one-to-many relationship between Contacts and Addresses, with a description field (Home, Business, Other) and with a checkbox field indicating the mailing address. Am I on the right track in design theory, or do I need to go back to basics? I'm just getting started identifying the problems with this database.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: (Re)designing database (XP)

    If you foresee that you may have to introduce other addresses than Home and Business in the future, a separate address table might be a good idea. Otherwise, I'd probably stick with the present setup. It may not be fully normalized, but it works well in most situations.

    You can use a union query to get all the mailing addresses (the first field names are imaginary):

    SELECT ContactID, LastName, FirstName, ..., HomeAddress As MailAddress, HomeCity As MailAddress, HomeState As MailState, HomeZip as MailZip
    FROM Contacts
    WHERE MailHome = True
    UNION
    SELECT ContactID, LastName, FirstName, ..., BusinessAddress, BusinessCity, BusinessState, BusinessZip
    FROM Contacts
    WHERE MailBusiness = True

    Union queries can only be created in SQL view (from the View menu); you may want to create the first part (mails to the home address) in design view first, then switch to SQL view to finish the union query. Both parts of the union query should contain the same number of fields, in the same order. The field names or their aliases from the first query will be used by the union query; that's why I assigned aliases "As MailAddress" etc.

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Location
    Houston, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: (Re)designing database (XP)

    Would I also assign the same aliases to the Business fields?

    SQL is definitely the thing to learn!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: (Re)designing database (XP)

    No, you don't need to assign aliases to the Business fields. A union query automatically used the field names (or their aliases) in the first SELECT statement for ALL records. Field names/aliases in the second and subsequent SELECT statements are ignored.

  5. #5
    New Lounger
    Join Date
    Mar 2004
    Location
    Houston, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: (Re)designing database (XP)

    I understand. Thanks so much!

Posting Permissions

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