CODEWITHSUNDEEP
database-designdatabase-schema
Emanuil Rusev
Emanuil Rusev

Reputation: 35235

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, 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.

Upvotes: 2

Views: 172

Answers (4)

umlcat
umlcat

Reputation: 4143

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}

Upvotes: 0

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

enter image description here

Upvotes: 4

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

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.

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132570

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

Upvotes: 3

Related Questions