I've been faced several times when a project I'm working on would involve several types of users, all requiring different kind of details stored about them. To keep things simple, I'll use a single modeling website I'm working on. The types of users include: studios, males, females
Now what I've done is created 5 tables,
accounts -- stores username, password, created datetime
account_details -- commonly details such as name, phone number, email address, mailing address
account_profile_studios -- furtur profile details exclusive to this category, such as company name
account_profile_males -- etc
account_profile_females -- etc
This seems to work OK -- however this results in some duplication of field names in the profile pages simply because females and males have common attributes, but it is not the case with studios. For example, height is exclusive to male and female and not studio, so it cannot go in the account_details table. Therefore, I would be forced to have height in both males and females.
I know some might say that is acceptable, but when you have 10+ fields that are common between to fields it gets ugly. That just smells bad to me.
Any thoughts on how I can rearrange this database structure to prevent any duplication?