Relating a single type to multiple types

Question

Pretend I'm Mark Zuckerberg and it's 2003.

I'm building a social network website (thefacebook.com) and I need an advice.

The emphasis of the site is on user profiles but it also supports profiles of businesses, universities, etc. We call these other profiles - page profiles.

Both types of profiles (user profiles and page profiles) support phone numbers.

How should I store these phone numbers in the database?

Does the following design make sense:

phones(phone_id, phone_number);
users_phones(user_id, phone_id, ...);
pages_phones(page_id, phone_id, ...);

Woud you suggest an alternative design?

PS: I believe I'm onto something big with this Facebook thing, so by participating in this question you might be taking part of building history.

score 4 · Accepted Answer · answered May 18 '11 at 16:11

4

enter image description here

answered May 18 '11 at 16:11

Damir Sudarevic

21,891
3
47
71

(+1) for having the time for the chart. I would add a "ProfileDefaultPhone" to "Profile" Can i haz cheeseburguer ? – umlcat May 18 '11 at 16:17
Is the "profileType" field necessary? The profiles of each type could be retrieve through JOINs without the need of the field in question. – Emanuil Rusev May 18 '11 at 17:30
@Emanuil, it is a discriminator. It states the type of the profile. Also used in a join to eliminate possible garbage entry from a wrong profile type. `.. FROM Profile as p LEFT JOIN UserProfile AS u ON (u.ProfileID = p.ProfileID and p.ProfileType = 'U')` – Damir Sudarevic May 18 '11 at 18:14
Not if you construct the join like: `.. FROM UserProfile LEFT JOIN Profile ..`. – Emanuil Rusev May 18 '11 at 18:59

score 3 · Answer 2 · answered May 18 '11 at 13:12

3

Maybe:

users(user_id, phone_number, ...);
pages(page_id, phone_number, ...);

i.e. do you really care about phones as an entity? Or is a phone number merely a useful attribute of a user and of a page?

For multiple phones per user and per page:

users(user_id, ...);
pages(page_id, ...);
user_phones(user_id, phone_number);
page_phones(page_id, phone_number);

answered May 18 '11 at 13:12

Tony Andrews

129,880
21
220
259

I missed a crucial point - each profile should be able to have multiple phone numbers. – Emanuil Rusev May 18 '11 at 13:14
I've messed up the question. The design you're proposing is exactly what the example in the question should have looked like. At least now I know it makes sense. – Emanuil Rusev May 18 '11 at 13:19
OK. The remaining difference is that I see no need for a **phones** table with asurrogate phone_id. – Tony Andrews May 18 '11 at 13:23
Wouldn't be more correct to name the tables using plural nouns like "users_phones" and "pages_phones", as we are referring to multiple possessions of multiple objects? – Emanuil Rusev May 18 '11 at 13:24
That's a local standards issue not a data modelling one! But FWIW, I have never seen both words pluralised like you suggest. – Tony Andrews May 18 '11 at 13:25
Yes, that would be a difference and something I'll think about. – Emanuil Rusev May 18 '11 at 13:26
+1. The phones table is pointless, and it'll lead to UI problems. Example: when an employee lists his company's main phone as a work phone number, does that end up changing the number of on his old company's page too? (Don't laugh, I've actually seen this happen on a database that stored a company_id for contacts instead of using a dumb varchar field; the result was *not* pretty.) – Denis de Bernardy May 19 '11 at 04:50

score 2 · Answer 3 · edited May 23 '17 at 10:34

2

Both types of profiles (user profiles and page profiles) support a phone numbers.

How should I store phone these phone numbers in the database?

In a table of profile phone numbers.

More information about this kind of pattern. And even more information.

edited May 23 '17 at 10:34

Community

1
1

answered May 18 '11 at 15:47

Mike Sherrill 'Cat Recall'

91,602
17
122
185

score 0 · Answer 4 · answered May 18 '11 at 16:17

There are 2 concepts that are mixing up.

(1): is that you are working with several similar but not exactly equal entities ("profiles"), even if you have a default entity or main profile.

Some properties or fields are the same for all entities. Some properties or fields are different for each kind of entity. This scenario or pattern is called "generalization", and usually is traslated into a main shared table with additional subtables:

http://en.wikipedia.org/wiki/Class_diagram#Generalization

profiletypes {profiletype_id, profiletype_name}

profile {profile_id, profiletype_id, profiletype_name}

profile_user{profile_id, profiletype_firstname, profiletype_lastname, profiletype_ssn, ...}

profile_company{profile_id, profiletype_companyname, ...}

profile_rockband{profile_id, profiletype_bandname, ...}

(2): You have a field that can be repeat several times, but still have one as default.

Usually; I add 1 or 2 "default" phone numbers to the main "profile" / "table", and create an additional table for phones.

profile {profile_id, profiletype_id, profiletype_name, profiletype_defaultphonenumber}

phones {phone_id, profile_id, phone_number}

Relating a single type to multiple types

4 Answers4