dynamic database columns?

Question

Suppose that I've a website where I'm offering the user to link his social network accounts like facebook, twitter, ... with his account on my website. Now I'm supporting facebook and twitter in the website, but later I'll add more. So currently I've a table in the database for each supported social network, for example Facebook table which will have the facebook user id and the security hash and whatever I need in the app. this table has a foreign key user id to the User table for users in the website. Now suppose that I want to know for each user which social networks are linked with my website, so I decided to add a column to the user table of varchar. And I'll have a sequence in it, for above example, if the user is linking both facebook and twitter account, the value will be '11', if he is linking facebook only, the value would be '10' and so on. Is this a good way to design such thing?

Also when I want to support a new social network say Google+, all I've to do is to add a table GooglePlus table in the database, and then write a script which will add a char to the existing column, initially all users will have whatever they had before + '0'. So the first user will have '110' and the second user will have '100'. And so on.

Is this the best approach I can follow in this situation? Is this a maintainable design?

An example to clarify the question:

The user table:

User_Id    LinkedSocialNetworks
   1          '100'
   2          '101'

each char in the linked social networks represents a flag either this user is linking (1) his account with a specified social network or not (0). I know as the developer of the app is that the first char is for facebook, second is for twitter, Google+, ....

From inspecting the user table, I know that user 1 is linking his account with Facebook, user 2 is linking with Facebook and google + but not with twitter.

After getting this information, for user 1, I'll get his information from Facebook table only, for user 2, I'll get his information from Facebook table and Google+.

I'm using MySql by the way

Answer 1

No, it's not a good design idea. However, there's nothing wrong with your seperate tables for each social network. Instead, to link each account to different people, try a design like this:

UserID UserName Preferences...
3      janeDoe123 ...
4      johnDoe456 ...

FaceBookUsers
UserID FBUserID FBUserName  ....
3      234322   janey
4      3453434  johnperson

MySpaceUsers
UserID MySpaceUserID MySpaceUname MySpaceThing2  ....
3      23423234 jj324 ...
4      323      jps2432dawg ...

You have a general table for users that you use inside your application and that table has a uniqueID that ties together the other social user identities on other accounts

The idea here is that you're not adding columns every time a new social network pops up, nor do you have to recalculate some formula and update all the rows that the formula affects (- that's a maintenance nightmare). Instead, you just query each table for your user's unique identifier to see if they exist on that social network. (for example, since your John Doe has a userID of 4, you check if they have a FB account by queryinng for a row that has a userID of 4)

I don't know enough about this to tell you if having a separate table for each social network is a good idea or not. I'm not sure if it's possible to consolidate all the details for every network down to a single table, but my gut feeling is that it would be a mess. I don't really know here, so I'm not sure if your one-table-per-social-network is a good idea or not, but I'd think that it is the correct way to go.

[b]An Alternative[/b] I don't see why you're doing all that work for a bit string that specifies relationships like 1010110. You can use MySQL's TINYINT(1) for a minimal memory footprint and do something like this:

UserID UserName Preferences... HasMySpace HasGooglePlus HasFB
3      janeDoe123 ...             0            1         0
4      johnDoe456 ...             0            1         0

I think that column default values (null or 0 ) would come in handy here. This way you add a new column to your main table every time a new network pops up, but it achieves the same effect as you currently have with a little less work.

Answer 2

I'd rather have something like

CREATE TABLE users (id INT NOT NULL auto_increment PRIMARY KEY, ... );
CREATE TABLE social_networks (id INT NOT NULL auto_increment PRIMARY KEY, 
 name varchar(50), ...);
CREATE TABLE users_social_networks (user_id INT NOT NULL, 
 social_network_id INT NOT NULL, social_id VARCHAR(100) NOT NULL, 
 social_name VARCHAR(100), 
  PRIMARY KEY (user_id, social_network_id), 
  UNIQUE(social_network_id, social_id), 
   FOREIGN KEY (user_id) REFERENCES users(id), 
  FOREIGN KEY (social_network_id) REFERENCES social_networks(id));