User type entities
Posted: Thu Jan 21, 2010 3:51 am
I've been having this issue for a few years now, and have not been happy with the solution I've rolled out previously a couple dating sites.
Consider this, you have users, which are defined by their gender. So logic sais we need to describe each gender as their own entity. There are actual more "gender" types than this if you want to include couples, etc.. Anyways..
accounts
profile_male
profile_female
profile_transgender
Where accounts is the primary table containing the account crendentials, and each profile_* table uses accounts primary key as each respective profiles foreign key to connect them.
Obviously the profile values are going to greatly differ on the type of information collected, as it's highly gender specific, which is the basis for me seperating them as their own entity. Now, these seems like an "ok" solution.. however, in practice it creates very sloppy queries. And more so, to select all users it involves 3 different queries (or 1 big union).
Is my only solution to roll out a profile fields table which has 1 row per value kind of thing, and another table with rows as profile field names?
How would you guys approach this?
Consider this, you have users, which are defined by their gender. So logic sais we need to describe each gender as their own entity. There are actual more "gender" types than this if you want to include couples, etc.. Anyways..
accounts
profile_male
profile_female
profile_transgender
Where accounts is the primary table containing the account crendentials, and each profile_* table uses accounts primary key as each respective profiles foreign key to connect them.
Obviously the profile values are going to greatly differ on the type of information collected, as it's highly gender specific, which is the basis for me seperating them as their own entity. Now, these seems like an "ok" solution.. however, in practice it creates very sloppy queries. And more so, to select all users it involves 3 different queries (or 1 big union).
Is my only solution to roll out a profile fields table which has 1 row per value kind of thing, and another table with rows as profile field names?
How would you guys approach this?