UPDATE column when a user renames data from another table

Question

Hey guys, how the heck do I go about doing this.

I have an address book I'm making and I'm trying to figure out how to tackle my groups. I let the users rename their groups at will. But then this complicates my life ;0

I have 2 tables. groups and contacts the groups has the group names per user. the contacts has a group column that says what group that contact entry belongs to.

How can I rename a group from the groups table and have the new name reflect in the contacts table so everything matches up?

Answer 1

I would suggest changing your model.
Use IDs as primary keys and an intersection-table to assign your contacts to groups:

Groups
id
name

Contacts
id
name

Group_Contacts
group_id    -> Groups.id
contact_id  -> Contacts.id

Now you can change the group-name whenever you want, without updating the contacts.

---

EDIT: If you only want to get the contacts of a certain group, use a SELECT like this one:

Select c.name
From groups g
Join group_contacts gc On ( gc.group_id = g.id )
Join contacts c On ( c.id = gc.contact_id )
Where g.name = 'Your group name'

Answer 2

You also could use a before update trigger which first renames all entries in the contacts table to match the new name and then let the update go on