How to Relate Coloumns with each other?

[phparion]

Hi

I have two tables that contain the following structure.

Code:
Table A
stdroll, stdname, stdfee (stdroll = primary key)

Table B
exmid, stdname, papers, marks

i want to relate the cols 'stdname' of Table A with the stdname of Table B. such that if i edit the stdname of Table A then All the related entries of stdname are changed automatically in Table B. for example if i have the data like

Code:
Table A
1, David, 200

Table B
0087, David, PHP, 90
0098, David, ASP, 89--
if I change 'David' in Table A to "David Bone" then Table B must change David name to David Bone Automatically in all entries related to it

Please help me that how can I relate the entries in two tables with each other? should i also add Primary key of Table A to Table B to do this job? like this

Code:
Table B
stdroll, exmid, stdname, papers, marks

thanks in advance

[FalseDawn]

You could use cascading updates, but that's messy.

Basically, your tables are not sufficiently normalized.

You have a duplicated "stdname" column, which is a recipe for creating referential problems.

I suggest you normalize the "stdname" column - i.e create another table ("tbl_name" for example) with 2 columns name_id, name

And use the name_id value in Table A and Table B

Then, you will just need to change the name in one place (tbl_name)

You might also want to do a bit of reading on the first 3 normal forms of database design.

[tomda]

Agree with what has been said above.

Note that in the table B, you should refer to the person by the primary_key of the table A and not his name

So that

Code:
Table A
1, David, 200

Table B
0087, David, PHP, 90
0098, David, ASP, 89

looks like

Code:
Table A
1, David, 200

Table B
0087, 1, PHP, 90
0098, 1, ASP, 89

This should resolve your problem regarding the update of person name in both tables (changing person name in table A is enough).

[phparion]

Thanks for your help.

I am working over it and will get back with results.

thanks again