SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    normalizing question

    I have several tables that have address componant to them. Tables include user, building, client. (I am using MySQL).

    Each of those tables needs address componants - address1, address 2, city, state, and zip.

    My thought is in order to follow normalizing standards, I need to have an address table with the following fields; address_ID, address1, address 2, city, state, and zip. Then user, building, client would have a field address_ID.

    One concern I have is that it makes it more cumbersome to insert a new user/client/building. For example, i fI wanted to insert a new record into USER table, I'd insert some data into USER table and ADDRESS table. How would I then find the address_ID from ADDRESS table and add it to USER TABLE, all in action?

    Would you do something like this:
    Add record to ADDRESS table
    get address_ID
    then add suer data to USER table

    ?

    Thanks for best practice ideas here. Thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WebDevGuy
    Would you do something like this:
    Add record to ADDRESS table
    get address_ID
    then add user data to USER table
    that's exactly how you do it

    in mysql, use the last_insert_id() function
    in sql server, use the @@identity property
    in ...

    etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Deltona
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, The question to ask is do EACH of these entities (User, building, client) have multiple addresses? If each entity can only have one address, then there is nothing wrong with storing the address in the same table as the entity - the address in that case would also describe the entity so there would not be a need to break the address into another table. However, if it is possible for an entity to have more than 1 address (physical, shipping for example) then yes you would need to break the address into another table and follow r937's advice.


Bookmarks

Posting Permissions

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