MySql table type, how do I draw column data from one table to another automatically?

Question

I have a 2 tables called 'members' and 'users' that both have 2 columns: name & gender. I want that everytime I add a new 'name' to members table, the value in 'gender' column in that row would be updated from 'users' table:

**users:**
Emilia - F
John - M
David - M
**members:**
Synthia - F

'INSERT INTO members VALUES('David')...or whatever'
now **members:**
Synthia - F
David - M

Answer 1

You could use an insert trigger. However you should not have a copy of the gender in two different tables - it breaks normalization, requires more storage space and risks that the two copies will get out of sync.

Instead you should use a foreign key and join the tables when you need information from both. You can use the username as the foreign key, or the autoincrement id, if you have one.

Answer 2

I think you're talking about a computed column, but I'm not sure if MySQL supports them (google a bit, I could be wrong). Why not create a view over the two tables instead? You could try something like...

CREATE VIEW MemberDetail
AS
  SELECT mem.Name,
    usr.Gender
  FROM Members mem
  INNER JOIN Users usr ON mem.Name = usr.Name;

Answer 3

how about INSERT ... SELECT syntax?

INSERT into members (name, gender) 
SELECT u.name, u.gender FROM users u WHERE u.name='Cynthia';