Page 1 of 1

User type entities

Posted: Thu Jan 21, 2010 3:51 am
by John Cartwright
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?

Re: User type entities

Posted: Thu Jan 21, 2010 4:20 am
by Weirdan
John Cartwright wrote:How would you guys approach this?
The same way as you did. This is a problem that does not map naturally into relational structure. Some DBMSes could make it a little less painful by providing native support for table inheritance (like Postgres), but generally it doesn't get much easier.

Re: User type entities

Posted: Thu Jan 21, 2010 4:51 am
by Eran
This is the correct approach, table-based inheritance. Question is, why would you need a UNION query if accounts contains all the users? the other tables should only be containing differing fields, which you can't put in a UNION anyway (UNION must have matching fields in number and type).

Re: User type entities

Posted: Thu Jan 21, 2010 5:02 am
by onion2k
John Cartwright wrote:How would you guys approach this?
To be honest, I'd just have one table for accounts, and lots of columns for all the different fields. They'd be redundant for people who the field doesn't apply to, but as you'll often be using the data in a query you'll save loads of query time (and effort) having a slightly denormalised structure.