MySQL Structure for a social network

Question

I'm experimenting by making a social network from scratch in PHP/MySQL, but I'm having trouble thinking of the optimal MySQL structure for it, currently I have:

This is a table which stores all user info:

fname varchar (300),
sname varchar (300),
pass varchar (400),
email varchar (300),
gender varchar (300),
dob varchar (200),
uid varchar (300),
PRIMARY KEY (id)

This is created when a user signs up, their own personal table:

id int(20) NOT NULL auto_increment,
            uid varchar (300),
            photo_url varchar (400),
            pfid varchar (300),
            phototime datetime,
            video_url varchar (400),
            vfid varchar (300),
            videotime datetime,
            status longtext,
            sid varchar (300),
            statustime datetime,
            blog longtext,
            bid varchar (300),
            blogtime datetime,
            about_bio longtext,
            about_current_job longtext,
            about_secondary_school longtext,
            about_primary_school longtext,
            about_college longtext,
            about_university longtext,
            about_workemail longtext,
            about_homeemail longtext,
            about_phonenumber longtext,
            about_relationshipstatus longtext,
            about_relationshipwith longtext,
            PRIMARY KEY (id)
            )";

The sessions table to track whether someone is logged in or not:

id int(20) NOT NULL auto_increment,
sid varchar(300),
uid varchar(300),
PRIMARY KEY (id)

Haven't gotten onto relationships yet but I was thinking:

id int(20) NOT NULL auto_increment,
requestby varchar(200),
requestto varchar(200),
status varchar(200)

(Before anyone asks, this is purely just for the learning experience, nothing more)

Answer 1

Well, you definitely shouldn't have one table per user. I think a database structure more like this would work really well:

CREATE TABLE users (
    userID INT NOT NULL AUTO_INCREMENT,
    firstName VARCHAR(30),
    lastName VARCHAR(30),
    password CHAR(32), -- should be encrypted, CHAR is better if the field is always the same length
    email VARCHAR(64) NOT NULL, -- not null if this is what you will use as a "username"
    PRIMARY KEY (userID)
);

CREATE TABLE personalInfo (
    userID INT NOT NULL,
    gender ENUM ('MALE', 'FEMALE'),
    dateOfBirth DATE,
    phoneNumber VARCHAR(15),
    personalEmail VARCHAR(64), -- may or may not be the same as the email field in the "users" table
    workEmail VARCHAR(64),
    bio TEXT,
    FOREIGN KEY (userID) REFERENCES users (userID)
);

/* this table is not specific to any single user. It is just a list of jobs that have been created */
CREATE TABLE jobs (
    jobID INT NOT NULL AUTO_INCREMENT,
    company VARCHAR(100),
    title VARCHAR(100),
    description TEXT,
    PRIMARY KEY (jobID)
);

/* the workInfo table will hold one entry per user per job. So if a user has held five jobs,
   there will be five rows with that userID in this table, each with a different jobID, which
   refers to an entry in the "jobs" table above. */
CREATE TABLE workInfo (
    userID INT NOT NULL,
    jobID INT NOT NULL,
    startDate DATE,
    endDate DATE, -- can set this to null if it's the user's current job
    FOREIGN KEY (userID) REFERENCES users (userID),
    FOREIGN KEY (jobID) REFERENCES jobs (jobID)
);

CREATE TABLE schools (
    schoolID INT NOT NULL AUTO_INCREMENT,
    schoolName VARCHAR(100),
    -- any other information you want to provide about the school (city, address, phone, etc)
    PRIMARY KEY (schoolID)
);

CREATE TABLE schoolPrograms (
    programID INT NOT NULL AUTO_INCREMENT,
    programName VARCHAR(100),
    -- any other information you want to provide about the program (department, teachers, etc)
    PRIMARY KEY (programID)
);

CREATE TABLE educationInfo (
    userID INT NOT NULL,
    schoolID INT,
    programID INT,
    startDate DATE,
    endDate DATE,
    FOREIGN KEY (userID) REFERENCES users (userID),
    FOREIGN KEY (schoolID) REFERENCES schools (schoolID),
    FOREIGN KEY (programID) REFERENCES schoolPrograms (programID)
);

CREATE TABLE relationships (
    userID INT NOT NULL,
    userID2 INT, -- allowed to be null if the user is single or does not specify who they are in a relationship with
    status ENUM ('SINGLE', 'IN A RELATIONSHIP', 'MARRIED', 'IT''S COMPLICATED' /* etc */),
    FOREIGN KEY (userID) REFERENCES users (userID)
);

/* each photo is created here. This way, when a user wants to share a photo,
   we don't have to duplicate each column. We just create another row in
   the "userPhotos" table below that) REFERENCES the same photoID. */
CREATE TABLE photos (
    photoID INT NOT NULL AUTO_INCREMENT,
    url VARCHAR(200),
    caption VARCHAR(200),
    dateOfUpload TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (photoID)
);

CREATE TABLE userPhotos (
    userID INT NOT NULL,
    photoID INT NOT NULL,
    FOREIGN KEY (userID) REFERENCES users (userID),
    FOREIGN KEY (photoID) REFERENCES photos (photoID)
);

/* vidoes, handled exactly the same as photos */
CREATE TABLE videos (
    videoID INT NOT NULL AUTO_INCREMENT,
    url VARCHAR(200),
    caption VARCHAR(200),
    dateOfUpload TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (videoID)
);

CREATE TABLE userVideos (
    userID INT NOT NULL,
    videoID INT NOT NULL,
    FOREIGN KEY (userID) REFERENCES users (userID),
    FOREIGN KEY (videoID) REFERENCES videos (videoID)
);

CREATE TABLE status (
    userID INT NOT NULL,
    status TEXT,
    FOREIGN KEY (userID) REFERENCES users (userID)
);

Answer 2

Don't use large varchars for all those fields. Friendship status can be just an int if you keep a lookup table (or a list in your code) that explains each value.

If the user table has an auto incrementing ID, you could use that ID for foreign key relationships. Even if you don't want UID to be an integer, you could still make it a GUID or something else that is much, much smaller than a varchar.

These tables only specify a profile and maybe a relationship, but there is so much more. Even something as simple as Twitter has a table of tweets, lists, accounts to put in a list, users that follow a list, direct messages (although those could theoretically be in the same table as Tweets), linked apps, blocked users and much, much more.

So I think first of all, you should think about what your social network should be, what it should look like, what features should it have. Then, strip that down to only the most essential features. Then, strip it down a little more, you're still thinking too big. ;) When you got clear what your minimum desirement are, it will probably be much clearer to you what table you would need.

Don't forget to add constraints and indexes!

Note that in practice, Twitter, Facebook and the other large networks don't use MySQL at all, but to practice, MySQL is fine.