On the Clients layout, add a portal based on the groups_clients table and in that portal place the groupname field from the groups table.
thankx DaSaint, yes this is a good solution. The only problem I foresee is that once I delete a group, the group info will be lost in the clients table too and I wish it to be static information. I've read a lot of negative comments about copy/paste, is there another way though?
Why would you delete a group that's in use by your clients?
You can delete records in your groups_clients table without a problem. Maybe at some point a client doesnt' belong to a certain group anymore.
But deleting a group from the groups table sounds like a bad idea.
What might work is looking up the group info in the groups_clients table but then you are duplicating a lot of data and you are not really respecting the rules of relational database design.
So I would say: Don't delete the groups.
- groups (fields: group id, groupname, client id, category, date)
The field in red makes no sense in terms of your database design. It would appear to be a field that you can remove from this table.