Relating a single type to multiple types

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, uni开发者_高级运维versities, 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.

---

---

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);

---

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.

---

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}