SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2002
    Location
    Scotland, UK
    Posts
    530
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is and how do I make a relationship in MySQL?

    Hi everyone,

    Im pretty new to database dev but know how to use them.

    Question Im asking today is what exactly is a relationship and how do I make them?

    Are they created manually by your program or does MySQL magically do it for you ?

    Thanks for your help.

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A relation is a connection between two tables. In MySQL, you have to create those yourselves. Take a look at the following examples:

    Code:
    Table Jokes
    ID | Joketext | AID
    
    Table Auhtors
    ID | Name | Email
    As you can see in the above two tables, they are connected to each other with the AID-ID statement. The AID, which is short for AuthorID, holds the value of the ID of the author of the joke.

    Let's say I am a joke-auhtor and my ID is 4. I have written a joke which appears in the database Jokes. The value for AID in that record would be 4, which links the record up with my record in the table Auhtors.

    Does that make any sense?

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2002
    Location
    Scotland, UK
    Posts
    530
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup, I follow that but how do you then link the both together. Would you have to manually find the correct value with something like this :

    SELECT * FROM Authors WHERE ID = Jokes.AID

    ?

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you would need a joined query for that. With MySQL, you need to use one of these methods.

  5. #5
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    MySQL doesn't handle Relationships. It is not a Relational database. Any and all relationships you wish to make must be maintained in your code manually.
    Wayne Luke
    ------------


  6. #6
    SitePoint Evangelist
    Join Date
    Jan 2002
    Location
    Scotland, UK
    Posts
    530
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by W. Luke
    MySQL doesn't handle Relationships. It is not a Relational database. Any and all relationships you wish to make must be maintained in your code manually.
    Seriously?

    from Building a Database-Driven Web Site Using PHP and MySQL
    (http://www.mysql.com/articles/ddws/)

    "We'll be using two new tools for this: the PHP scripting language and the MySQL relational database."
    Last edited by Rikki; Jul 31, 2002 at 08:17.

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2001
    Location
    Canada
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL is a RDBMS.
    Last edited by kix; Jul 31, 2002 at 08:22.

  8. #8
    SitePoint Evangelist
    Join Date
    Jan 2002
    Location
    Scotland, UK
    Posts
    530
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now I feel more confused LOL

  9. #9
    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)
    MySQL doesn't handle Relationships. It is not a Relational database.
    wayne, on the troll-o-meter that's gotta be a 7

    mysql actually "handles" relationships quite well, if you consider what it's doing whenever you do a JOIN

    what you probably meant was that mysql does not support declarative relational integrity, i.e. you can designate foreign keys, but mysql does not enforce them

    if that's your criterion for being a relational database, then all the big boys -- oracle, sql/server, db2, your favourite rdbms du jour -- fail too, because none of them can do ON UPDATE action, all they've implemented so far is ON DELETE CASCADE or RESTRICT

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

  10. #10
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    In order to be a relational database the has to be Referential Integrity, Cascading Updates and Cascading Deletes. In order to do this, you need to have Foreign Key support. MySQL has no support for foreign keys and therefore cannot handle relational data.

    Granted it is a good easy way to learn SQL but until it actually handles relationships automatically without the need for additional code such as JOINS and outside programming languages, I stick by my observation that it is not a Relational Database.

    We used to do ON UPDATE stuff in Oracle 8i and in SQL Server 7 all the time. Never really programmed in Sybase but it also has a lot of features of Referential Integrity. Heck, you can even do this stuff in Microsoft Access so it isn't that ground breaking.
    Last edited by Hierophant; Jul 31, 2002 at 14:19.
    Wayne Luke
    ------------


  11. #11
    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)
    MySQL has no support for foreign keys and therefore cannot handle relational data.
    again, it can "handle" the relational data just fine

    the way you're going, a three-legged dog is not a dog because all dogs have four legs

    let's just agree to disagree on what "defines" a relational database

    like i said, if "relational integrity" is the criterion, sql/server, oracle and db2 aren't relational either

    without the need for additional code such as JOINS
    whoa, i can't believe you said that

    your database tables never require joins, i take it?

    heh

    hey, don't take any of this personally, okay?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937

    whoa, i can't believe you said that

    your database tables never require joins, i take it?
    I never said that... I have used plenty of Joins just with the proper relations they aren't needed in every query...

    We can agree to disagree since I personally don't hold a very high opinion of MySQL.
    Last edited by Hierophant; Jul 31, 2002 at 15:54.
    Wayne Luke
    ------------


  13. #13
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What junk.. if mysql is a rdbms, than a stack of index cards is too. "See I write this id here, so I know this card goes with that stack over there. As long as I keep things clear in my head it works fine!"

    r937 is right - there isn't a fully relational database in existence, and if there were it most likely wouldn't be accessed via sql. But on the relational scale, mysql is very low...

    But who uses mysql who is concerned about these issues? 9 times out of 10 mysql is driving a forum or a news site (the script kiddies of the database world) and would not benefit much from conforming to a better relational model.

  14. #14
    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)
    But on the relational scale, mysql is very low...
    no problem agreeing with that assessment

    lack of foreign key support isn't the half of it

    how about no subqueries? no column aliases in the WHERE clause? letting users GROUP BY anything they damned well please?

    what i like about mysql is that its popularity and ubiquity introduces so many new people to relational databases

    they will stumble on a lot of things, but on the whole, they will come up to speed a lot easier than if they had to dive into db2 or something...

    and mysql is a whole lot better than processing flat files with perl

    say, did you know there are odbc modules that let you access flat files with sql queries?


  15. #15
    SitePoint Evangelist
    Join Date
    Jan 2002
    Location
    Scotland, UK
    Posts
    530
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So in essence MySQL is a toy database only really good for simple web sites that need a small database feed for news or links or whatever.

    Im a bit disappointed as everything Ive read online (including a lot of Sitepoint stuff) seems to sing the praises of MySQL

  16. #16
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you need anything more? If it does the job and it's what you need, use it. Like W.Luke said, it is a good easy way to learn sql.

    If you want a more advanced dbms, try oracle or postgres.

  17. #17
    SitePoint Evangelist
    Join Date
    Jan 2002
    Location
    Scotland, UK
    Posts
    530
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have been looking at Postgresql and InnoDB for a few days now since learning of the shortcomings of MySQL

  18. #18
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Rikki
    Im a bit disappointed as everything Ive read online (including a lot of Sitepoint stuff) seems to sing the praises of MySQL
    That's because most SitePointer authors and members use PHP, not ASP (AFAIK). I'm not expressing an opinion about MySQL, rather the bias that members express towards their chosen scripting language and its correspondingly most popular DB.

    It's like asking a Man U supporter what the best football team is....! lol
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  19. #19
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think many use asp with postgres - I suspect they favor php, java, perl.


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
  •