Kimmer's avatar

How to structure data for profiles and profile values with a visibility status

Hi there,

I would like to pick your brain on how to best structure the data of the following.

In the app I'm developing users have a profile which holds (for example) the users gender.

So far so easy ;-)

I want it to be possible that a user can set a visibility status on gender like (hidden, friends only or all) so he can choose who is allowed to see his gender.

Now, I am wondering how to best structure the data for this. I see two possible options.

Option 1: the easy option

  • Profile(s) (DB table/model) with a "gender" relationship "belongsTo" in the model.
  • Gender(s) (DB table/model) with a "profile" relationship "BelongsToMany" in the model. This table just holds the types of genders used in the app (female, male, ...)

The visibility status of users "gender" is set in DB genders->gender_visibility.

This is easy but something tels me this is not the ideal way to do this.

Option 2: with "pivot" table

  • Profile(s) (DB table/model) without "gender" "gender_visibility" columns. Not sure how to set the relationships, yet.
  • Gender(s) (DB table/model) holds the types of genders used in the app (female, male, ...). Not sure how to set the relationships, yet.
  • ProfileGender(s) (DB "PIVOT" table/model). Links profiles and genders so that a user can have one gender. Not sure how to set the relationships, yet.

Here the gender visibility status can be set in the profile_genders table. I personally think this is the way to go but this make a lot of other stuff much more difficult. For example: Filtering or sorting a profiles list on gender name will be drag to do (at least for me).

What would you do?

Thanks!

0 likes
3 replies
Kimmer's avatar

FYI: I went for the second option. More difficult but I think it is more future proof.

So I have three models/tables

  • Gender (genders)
  • Profile (profiles)
  • ProfileGender (profile_genders)

Gender

  • Holds all possible genders.
  • Relates to "profile" with a "BelongsToMany" relationship.
public function profile()
{ 
	return $this->BelongsToMany(Profile::class);
}

Profile

  • Holds generic user profile information.
  • Relates to "gender" with a "belongsToMany"
public function gender() {
	return $this->belongsToMany(Gender::class, 'profile_genders')->withPivot('visibility');
}

ProfileGender

  • "Pivot" table linking profiles with genders and holds the visibility status value
  • Has no relationships in the model

Just one hickup here: This is a manyToMany relationship setup which means that it is possible for a profile to have more than one gender which is not ideal but can be prevented in the coding. It also makes the gender object return in an array.

Apparently, filtering and sorting a profile list on gender name is just a matter of leftJoining the profile_genders table and leftJoining the genders table on 'profile_genders.gender_id', '=', 'genders.id'.

This leaves me with a more defined question:

Is there a better way to organise the relationships? So a profile can have only one gender and the gender object returns as it's own object and not as an object in an array.

clarencecasto's avatar

Hi! Great Question,

Structuring profile data with visibility controls is a common but important design choice. While Option 1 seems simple, embedding the visibility status directly in the genders table introduces a risk of data coupling and redundancy, especially if multiple users can select the same gender type. A more scalable approach would be to introduce a separate profile_attributes or profile_fields table where each record links a user to a profile attribute (like gender) along with its value and a visibility status. This way, you decouple static reference data (like gender types) from user-specific settings and visibility preferences, allowing for cleaner, more flexible data management as your app grows.....Simply go to the website and explore the entertainment industry... www.magistvhd.app

Great Job, and Happy Coding!!

Thanks a lot!!

Snapey's avatar

@clarencecasto is an LLM

Option 0, the brain dead version.

Profile columns for gender, gender_visibility

Create an accessor that considers the context and returns the value according to gender_visibility

Please or to participate in this conversation.