Hi Folks,

I am learning SQL using MySQL after a 10 year break from programming Looking back at Uni, I seemed to remember databases being a bit "smarter" than I seem to be finding them.

Here is an example of a car database with two tables, containing information about the car manufacturer and the car model:


I realise I could do a search like:

(Maybe the SQL isn't perfect, but it gets my question across!)

Which should output somthing like:


But what I am getting confused about is the relationships between the tables. I have manually searched and matched up the information I wanted using the select statements. I seem to remember in Uni doing one-to-many relationships, but I can see that there could be a way of the manufacturer_table (say Fiat) having a list of models *inside* it - to get a list of models that Fiat made, you would have to search the model_table and then refer back to the manufacturer_table to see if was a Fiat, rather than just being able to see a list of all the model_id's somewhere within the manufacturer_table.

I thought that databases were a bit smarter than this for some reason, and before I continue learning and writing my project, I just wanted to check that I wasn't missing something!

So I guess my questions is - is the database aware in anyway of the relationships between the tables, or do you have to manually compare keys in the select statements? In my example, would I be correct in searching everything model_table for where model_table.manufacturer_id is Fiat?

If this is the case, what difference does one-to-many relationships, or many-to-one or one-to-one relationships make, or am I confusing database design with implementation... or am I generally going crazy

Thanks in advance for all your replies!

Andy.

the latter

i could go into details but the gist of it is that you can join tables based on whatever columns you want (whether it makes sense to or not)

all the difference in the world

okay, there is no difference between one-to-many and one-to-one in the way they are implemented

and of course there is only one way of implementing a relationship -- using foreign keys

Thanks for the reply!

So is the concept of a foreign key only a concept?

For example the manufacturer_id key in the model_table in my example could be called anything at all, so long as the person building the select statement new that manufacturer_id pointed back to manufacturer_table.manufacturer_id.

Consider there a third table:

There would be nothing to stop a programmer building a select statement that incorrectly matching manufaturer_if from the model_table up to the tyre_id in the tyre_table giving output like:


This seems strange that so much information about the relationships and links between the tables of the data is mapped and kept outside of the database, and for all anyone knows "in the programmers head".

no, it's actually implemented into the structure of the tables

for example, other than quality reviews of his deliverables, no -- but he would never get it into production because the results of the query would be wrong

actually, most of it is kept in the database

do a search for INFORMATION_SCHEMA