mysql relational?

[jorasmi]

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

[dragonhawk]

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...

[voostind]

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

[HarryF]

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

"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...

[MattR]

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.

[galt]

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.

[MattR]

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

[galt]

I was just suggesting that a product might not meet all the CODD criteria, yet still be reasonably considered a relational database.

[seanf]

Moved to a more appropriate forum - the PHP forum is for PHP questions!

Sean

[MattR]

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!

[dragonhawk]

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...

[jorasmi]

ok so it's not really relational, although it has some features of a relational database.

[Maelstrom]

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)

[MattR]

MySQL has a 'merge' table which is *almost* like the VIEW we all know and love. Of course, it's *almost* useful too.