work with more tables in MySQL

Anon

Hi,

I have a lot of mess in mind regarding how to work with more than one table in MySQL.

I think that the problem is that I am thinking like if I was working with MS-Access.

I have made a "Driver" table and a "computer" table.

So in the "Computers" table I am storing computers models (so that I allow users only to select available models) and in the "Driver" table, I store the computer id (in the "Computer" table I have id and model fields) and some other infos like date, file name etc .

I am asking my self if it has sense to take an id for the "computer" table and to put it into the "driver" table.

Yes, because I want to show the computer name when I make the query and not the id of it...

I was working with id because, like in Access, if a model changes, I don't need to update all records on the "driver" table...

but with MySQL ? Which is the right way ?

I am a lot confused...

If I'm right with the way I take, which could be the SQL query to get out all the files for a model (checking "driver" table) and show the model number reading it from "computer" table ?

hope someone could tell me the right route...

Many thank to anybody who wants to explain this issue to me.

Fabio

Anon

If you are going to have multiple drivers available for a single computer then this is fine. But are you going to also have multiple computers that use the same driver?

Basically what I'm asking is if you have a 1 to many relationship or a many to many relationship.

For a 1 to many relationship it's very easy:

Computers:
computer_id
other_fields

Drivers:
driver_id
computer_id
other_fields

Those are the 2 tables that you will need and you should fill the computer_id field in the driver table with the computer that you are linking the driver to.

If you have a many to many relationship (1 computer linked to many drivers, and 1 driver linked to may computers) then you need a 3rd table and don't need the computer_id in the drivers table:

computers_x_drivers:
computer_x_driver_id
computer_id
driver_id

The queries that you want to do will be determined by the type of relationships that you have. Answer that first and then you can start working on the queries.

fabiuz

many thanks for your answer !

Yes, I have 1 to M only relations.

I am working on...

Questions:

1- Do I have to set the relations between tables in someway or not ? If yes, how ?

2- Is it best to give the same name to the "key" fields ?

3- So, could you write me a couple of queries as sample ?

Actually I am running 2 different SQL commands to get first drivers and than computers model :-(

Thanks again !

Anon

1 - no
2 - yes

Anon

1 - There is no way to set the relations between tables in MySQL. It currently doesn't support Foreign Keys. One of the reasons I like it.

2 - I personally follow these rules:
2.a - All table names should be plural
2.b - All primary key fields should have the singular version of the table name followed with _id. So the table Computers would have the primary key computer_id. This way I never have to think about it, and when I'm dealing with multiple tables in a query I don't have to worry about what "id" field I'm dealing with.

i.e
Select d.driver_id, d.computer_id
from computers c, drivers d
where d.computer_id = c.computer_id
and c.name = 'Intel';

Vs.

Select d.id, d.computer_id
from computers c, drivers d
where d.computer_id = c.id
and c.name = 'Intel';

In the second example it could get very easy to confuse the "id" fields and tables especially in a very long query.

3 - It depends on what you want to get from the database. The above query (both of them) gets you all of the driver_id's and computer_id's from the drivers table where the computer_id is the one for the computer with the name 'Intel'. Without full table structures and a specific dataset to retrieve any queries I write will be meaningless.

fabiuz

Really thanks for the useful informations.
I will take care with next MySQL DB.

Now I have understand a bit better how things work.

I still have two doubts:

1) Is there a logic for using INNER JOIN ?
Suppose I have these tables and each of
the has the primary key called using
your method: "drivers", "computers", "kindofdrivers" and "operatingsystems".

If the "drivers" table is the one on the M side, is this queries ok to get info from all of them as it was a unique table?

SELECT drivers.id_driver, computers.id_computer, kindofdriver.id_kindofdriver FROM kindofdrivers INNER JOIN (operatingsystems INNER JOIN (computers INNER JOIN drivers ON computers.computer_id = drivers.computer_id) ON operatingsystems.operatingsystem_id = drivers.operatingsystem_id) ON kindofdrivers.kindofdriver_id = kindofdrivers.kindofdriver_id ;

This is the SQL string I get if I do this thing with MS-Access. Also MySQL should support this syntax. What I have not so clear in mind is the logic of inner join.
Can I start from any table or the order before have a logic ?

The last question, and thanks again to have read me till now, is about the relation between drivers table and operatingsystems one.
Each driver could have more then one OS and each OS could have more than one Driver.
It is a M to M relation.

I know how to solve it in Access, but with MySQL how I can do ?

I mean, which kind of form could I build to allow a user to select more then one OS ?
A list box ?

Many many thanks for your help

Fabio

Anon

Well first of if you join on tables MySQL assumes that it's an inner join unless you tell it otherwise so this will work better:

SELECT drivers.id_driver, computers.id_computer, kindofdrivers.id_kindofdriver
FROM kindofdriversm operatingsystems, computers, drivers
WHERE computers.computer_id = drivers.computer_id
AND operatingsystems.operatingsystem_id = drivers.operatingsystem_id
AND kindofdrivers.kindofdriver_id = drivers.kindofdriver_id;

I just copied your select, so you should double check it. You are using id_computer and computer_id and those would be 2 different fields and you might not get what you are looking for.

I explained that a bit in my first post. You will need another table that you will used to link a driver_id to an operatingsystem_id, then you will need to get your driver_id's or operatingsystem_id's from that table before you start selecting all of your other information.