SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Sep 2003
    Location
    Northern California
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL : adding fields to table : good normalization practice

    I have a table with the usual company information : name, address1, address2, city, state, zip, phone. Sometimes the physical location is different from the mailing address (e.g. po box). It seems to me that billing address info belongs in the same table.

    I want to let the company add "tags" - like keywords - to describe their products or services. So, if a user searched tags for "widgets" - companies associated with that tag appear. To keep tables normalized, would it be practice to have the tags in a field in the company table.
    Interactive Web Media - Trinity River
    Embrace the mobile lifestyle - SmartFreelancer.com

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Create a separate table, called tags, with the fields tag and companyID as the joint primary field. you idea would probably not be normalised.

    if you put all the tags in one field, searching for a given tag is trickier; if you had several tag columns, you'd have to search each column for each tag, so searching becomes difficult. With a separate table, you only search the one column for the tag(s) you want.

  3. #3
    SitePoint Guru
    Join Date
    Sep 2003
    Location
    Northern California
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't know you could use two columns together for a primary id. Whenever I had a many-to-many mapping table, I always had an auto-increment primary id.
    Interactive Web Media - Trinity River
    Embrace the mobile lifestyle - SmartFreelancer.com

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,830
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    You can have as m any fields in the key as you like.

    If your primary key is field1 and field2 in that order then you effectively have two indexes one with both fields and one with just field1 while if you used an autoincrement you effectively don't have either of those indexes and things will probably run a lot slower.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mgm_03 View Post
    I didn't know you could use two columns together for a primary id. Whenever I had a many-to-many mapping table, I always had an auto-increment primary id.
    quite useless in a many-to-many table

    you'd want a unique constraint on the pair of FK columns in any case, to prevent the same company from getting the same tag more than once

    and of course, one or two indexes on the FK columns as well, to optimize queries

    you may note that any query referencing a many-to-many table never references its auto_increment value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •