SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    web daemon jorasmi's Avatar
    Join Date
    Nov 2001
    Location
    Philippines
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question mysql relational?

    hi i've read the description of mysql on their site and it says it is relational. i've been using it for a while already but i don't know how to make relationships, can you help me with this.

    thanks

  2. #2
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Isn't relational just meaning you are setting primary keys and foreign keys in your database?

    And the foreign keys needing to be primary keys in another table.

    For me, I don't bother with the relationships since I'm using PHP to add the values to the relevant tables anyway.

    I find setting the relations only helps if you are going to be editting the database manually. Also find it a headache when they say that I'm not allowed to add a row because the foreign key doesn't exist as a primary key in another table.

    Anyway, that's what I think...

  3. #3
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    According to the books, MySQL is not a relational DBMS. Yes you can define foreign keys, but that's about it.

    In a relational DBMS, the database should take care of checking whether a DELETE is allowed (the record isn't referred to anywhere else), UPDATES should be cascaded so that all records referring the one you're updating are updated as well, and so on. (There are many more things a relational DBMS must have that MySQL doesn't).

    About what dragonhawk says: "I don't bother with the relationships since I'm using PHP to add the values to the relevant tables anyway". All I can say to that is: do some research, and find out why this is not a very smart thing to say. I don't mean this in a bad way, but I do think that when you say something like that, you don't fully understand what a relational DBMS is intended to do.

    Vincent

  4. #4
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep it comes down to what you mean by "relational".

    Yes MySQL supports all the SQL you'll need to built one to many / many to many relationships yourself but until recently, you couldn't really do anything to to define foreign keys (like there's no GUI for drawing lines between tables as you have in Access).

    Type of SQL I'm talking about would be like

    Code:
    "SELECT books.title
    FROM books, category
    WHERE books.book_id = category.book_id
    AND category.name = 'Sci-Fi'"
    Here we get all the books listed under Sci-Fi - perhaps category has a one to many relationship with books.

    So it's generally up the application developer, to make sure data integrity is maintained (I think that was what dragonhawk was getting at).

    You may be interested in this...
    Last edited by creole; May 9, 2002 at 10:50.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A 'relation' is analagous to a table in a RDBMS. So you 'could' say that MySQL is relational in that it supports relations. However Codd, the author of the Relational Database standard has rules as to what a 'fully relational' system is:
    http://www.soi.city.ac.uk/~tony/dbms/codds_laws.html

    So no, MySQL is not a RDBMS in the strictest sense.

  6. #6
    SitePoint Evangelist galt's Avatar
    Join Date
    Apr 2002
    Posts
    461
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    YAO...

    Relational these days usually means the ABILITY to relate tables to each other in a query, and pull data without regard to physical location. I always thought of MySQL as relational, but since it does not support VIEWS, I would have to say NO. But it does support SQL, automatically manages indexes,and relieves much of the data management headaches. It does not support referential integrity, does not support rollback or rollforward, record locking is very weak, does not support query integrity, and only supports transactional integrity under some conditions.

    As far as the CODD requirements, those are a theoretical set of requirements, and a few years ago it was considered impossible to meet all of them.

    If you have never used a relational database, MySQL is a good place to start with minimal overhead (compared to Oracle and SQLServer). It has its place and it's weaknesses. It is very fast. I like it. BUt I would not use it to run an airline.

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by galt
    As far as the CODD requirements, those are a theoretical set of requirements, and a few years ago it was considered impossible to meet all of them.
    I'm not sure what you mean by that? Of course it's theory, most computer science is theory.. But he's the father of relational database systems, and certainly to establish a set of 'ground rules' is not out of his scope

  8. #8
    SitePoint Evangelist galt's Avatar
    Join Date
    Apr 2002
    Posts
    461
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was just suggesting that a product might not meet all the CODD criteria, yet still be reasonably considered a relational database.

  9. #9
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum - the PHP forum is for PHP questions!

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  10. #10
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by galt
    I was just suggesting that a product might not meet all the CODD criteria, yet still be reasonably considered a relational database.
    Reasonably, yes. By definition? No. But I certainly agree -- I'll continue to call Oracle, MS SQL, Sybase, etc. RDBMS' because they materially meet the criterion specified by Codd. MySQL? Not a chance!

  11. #11
    SitePoint Guru dragonhawk's Avatar
    Join Date
    Apr 2002
    Location
    Melbourne
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by voostind
    About what dragonhawk says: "I don't bother with the relationships since I'm using PHP to add the values to the relevant tables anyway". All I can say to that is: do some research, and find out why this is not a very smart thing to say. I don't mean this in a bad way, but I do think that when you say something like that, you don't fully understand what a relational DBMS is intended to do.
    Maybe you could enlighten me? What is a relational DBMS intended to do? I just want to know if I should be doing something I'm not when I'm writing code...

  12. #12
    web daemon jorasmi's Avatar
    Join Date
    Nov 2001
    Location
    Philippines
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok so it's not really relational, although it has some features of a relational database.

  13. #13
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by dragonhawk


    Maybe you could enlighten me? What is a relational DBMS intended to do? I just want to know if I should be doing something I'm not when I'm writing code...
    It protects your data. It also cuts down on the amount of work php (or any server sided language) would have to do. Personally I couldn't live without relations. You may as well go back and use dbase or flat files if you aren't using relations.

    And no mysql is not in the strictest sense relational but it is a great place to learn the concept of relating the databases together. Joins can sometimes be the toughest part when first learning sql. Ver 4.x goes further into the true relational but again leaves pieces of the puzzle out for speed purposes (according to them).

    For example if you have a true relational db you could fetch information from multiple databases joining every table together to form one table. When a change is made you can put that information back with one query because the db knows the relationships within the tables. Mysql didn't support both ends of this relationship. In version 4 they do.

    This is only an example and a very small one. If I can make mysql do half the work of what php can do and only use php to template and create my site than I do. Mysql or any sql can process faster than the server sides. ...(at least in my experience)
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  14. #14
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL has a 'merge' table which is *almost* like the VIEW we all know and love. Of course, it's *almost* useful too.


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
  •