Best way to show a specific user's members in a table

[MSUK1]

Hello, i am coding a backend portal, this portal will have staff members lets call them 'M1' and each staff member will have a client 'C1'

 

What is the best way to put this into a database?

 

do i have a table for Staff? then a seperate table for Members? or one table consisting of staff, with the members details written into the staff's row?

[Psycho]

You are mixing up your terminology in your post so I'm not really following you. In the first sentence you are referring to 'Staff Members' (one entity) and 'Clients'. Then in the last sentence you are referring to 'Staff' and 'Members' as separate entities.

 

But, generally speaking you want separate tables for different records. But, you may need additional tables based upon how records are related to one another.

 

If there is a one-to-one or one-to-many relationship then you can define the relationship in the records. For example, each staff may be related to multiple clients, but each client is related to one (and only one). staff. In that type of scenario you could have a field in the clients table that is a foreign key back to the staff record's primary ID. Conversely, if each staff is associated to only one client you could store the relationship in the staff table.

 

However, if you have a many-to-many relationship then you should use an additional table. For example, each client can be associated to one or more staff and each staff can be related to one or more clients. In this case you would have a separate table for staff and one for clients and then a third to define the relationships. That table would have a single record for each individual relationship. The records would contain the primary ID of the staff and the primary ID of the client.

 

That is just a very general pattern. There can be many nuances that would determine your specific table structures.