Professional Web Applications Themes

creating a one-to-many relationship - MySQL

this is probably an uber-dumb question, but I am a hella noob to sql. I have a bunch of tables in a 1 to many relationship. My question is, how do I physically create a one-to-many relationship. Do you have to create a lookup column as in MS Access? If so, what would a sample command line look like? Thanks Justin...

  1. #1

    Default creating a one-to-many relationship

    this is probably an uber-dumb question, but I am a hella noob to sql.
    I have a bunch of tables in a 1 to many relationship. My question is,
    how do I physically create a one-to-many relationship. Do you have to
    create a lookup column as in MS Access? If so, what would a sample
    command line look like?

    Thanks

    Justin

    Justin Guest

  2. #2

    Default Re: creating a one-to-many relationship


    Justin wrote: 

    Try a Google search for 'foreign keys' and 'database normalization'.

    Daz Guest

  3. #3

    Default Re: creating a one-to-many relationship


    Justin wrote: 

    To create a one to many relationship you need to create 2 tables.

    One of the fields whose values can appear only once in the first table
    will map to a field in the second table, whose values can appear many
    times. Hey presto, you have a one to many relationship. You can now
    utilise this one to many relationship with for example JOINS or LEFT
    JOINS.

    So, if you have a table of users thus:

    UserID各ser_Name圯mail_address
    1刎red Bloggs圭om
    2匡im Kirk圭om

    and a table of articles by those users:
    UserID再rticle_Text
    1存ome text 1
    2存ome text 2
    1存ome text 1-1

    Then you have a one to many relationship and you can say list all the
    articles by a single user with a query like:
    SELECT users.User_Name, users.UserID, articles.Article_Text
    FROM users
    JOIN articles USING(UserID)
    Where User_Name = 'Jim Kirk'

    You can also formalise these "Foreign Keys" in CONSTRAINTS within the
    database structure
    (http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html)

    Captain Guest

Similar Threads

  1. relationship
    By faye in forum Brainstorming Area
    Replies: 0
    Last Post: June 10th, 06:38 AM
  2. many to one relationship
    By James in forum ASP Database
    Replies: 6
    Last Post: October 17th, 03:53 PM
  3. creating related records across a many-to-many relationship
    By Michael Pillsbury in forum FileMaker
    Replies: 2
    Last Post: July 19th, 10:59 PM
  4. Breaking a relationship
    By Kei in forum Microsoft Access
    Replies: 1
    Last Post: July 17th, 06:09 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139