how relationships

powerofphp

hello,
i wana know how relationships between tables is maintain in database

onyx

Well firstly to understand relationships you need to understand normalisation:

http://www.devshed.com/Server_Side/MySQL/Normal/Normal1

and general database theory:

http://www.frick-cpa.com/ss7/Theory_TOC.asp

Here is some stuff specifically about relationships:

http://orchard.wccnet.org/~chasselb/inp275/ClassNotes/mysql/jrinn/relationships.htm

If you still want more information then you would probably find looking around computer science departments to find their notes on database theory useful.

powerofphp

sorry my question was incomplete i wana know how relationship in mysql can be created between tables

onyx

You can not do so in the creation of tables in mysql. You have to define the tables with the relationships in mind and remember these when you are performing queries.

Hence if I had a Person table with the following columns:

PersonID
Name
Address

And another JoinEP table:

PersonID
EmployeeID

And another Employee table:

EmployeeID
Wage

Now say if you wanted to query the tables to discover the names of people with wages over 20,000.

Select name from Person, JoinEP, Employee where Person.PersonID = JoinEP.PersonID and JoinEP.EmployeeID = Employee.Employee.ID and wage > 20000;

Because of the fact that you can not state relationships within MySql this means that you can not ensure the database integrety and hence care must be taken to ensure that this is the case within the code.

goldbug

Originally posted by onyx
You can not do so in the creation of tables in mysql. You have to define the tables with the relationships in mind and remember these when you are performing queries.

Hence if I had a Person table with the following columns:

PersonID
Name
Address

And another JoinEP table:

PersonID
EmployeeID

And another Employee table:

EmployeeID
Wage

Now say if you wanted to query the tables to discover the names of people with wages over 20,000.

Select name from Person, JoinEP, Employee where Person.PersonID = JoinEP.PersonID and JoinEP.EmployeeID = Employee.Employee.ID and wage > 20000;

Because of the fact that you can not state relationships within MySql this means that you can not ensure the database integrety and hence care must be taken to ensure that this is the case within the code.

Not entirely true. MySQL supports InnoDB tables, which supports relationships.

http://www.mysql.com/documentation/mysql/bychapter/manual_Introduction.html#ANSI_diff_Foreign_Keys

EDIT: removed mention of BDB tables, not sure they do FK's (but they do perform transactions)

onyx

Interesting. I can not say I have ever heard of those. I will have to have a look into that.

Thanks.