I’m interested in learning how things work so that I can learn how to do more. Sites like Facebook and MySpace are especially interesting because there is so much too them.. So recently I’ve taken a Databases and Dynamic Webpages class. I’m thinking about coding my own Social Networking site using PHP and MySQL.
However, before I can start it, I need to think about how it would work. After a lot of thinking, here is my design for the underlying MySQL tables. From the tables, it should be clear the features I have put consideration into.
Table # 1: Login
CREATE TABLE `login` (
`email` char(255) NOT NULL,
`password` char(255) NOT NULL,
`userid` int(11) NOT NULL AUTO_INCREMENT,
`online` bool DEFAULT 0,
PRIMARY KEY (`email`),
UNIQUE KEY `userid` (`userid`)
)
Notes: Email is the primary key because I am trying to give the user the least to remember so it will be more simple for him or her to start using the site. In the login process, the user will login with the email and password and the browser will be given a session using the userid, which is the unique key that identifies users. When a user logs on, ‘online’ is set to true, when the user logs off or the session expires, it is set to 1.
Table # 2: User info
CREATE TABLE `userinfo` (
`userid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`nickname` char(128) DEFAULT NULL,
`fname` char(128) NOT NULL DEFAULT ”,
`mname` char(128) DEFAULT NULL,
`lname` char(128) NOT NULL DEFAULT ”,
`gender` tinyint(1) DEFAULT NULL,
`bdate` date NOT NULL,
`phonenum` int(10) DEFAULT NULL,
`schoolid` int(11) DEFAULT NULL,
`employerid` int(11) DEFAULT NULL,
`addressline1` char(255) DEFAULT NULL,
`addressline2` char(255) DEFAULT NULL,
`city` char(60) DEFAULT NULL,
`state` char(60) DEFAULT NULL,
`defaultpicid` int(11) DEFAULT NULL,
PRIMARY KEY (`userid`)
)
Notes: Based upon the userid, this table stores the info that would be listed on the persons profile. For items that might be shared with other users, like schools and employers, I made it an id number that can be looked up in another table. The default picture can be looked up in a picture table. Age can be calculated based on the bdate. Some of this info will be filled in, optionally, by the user after registration. If a value is not filled in (is NULL) it will simply not be displayed.
Table # 3: Relations
CREATE TABLE `relations` (
`relationidnum` int(11) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(11),
`userid2` int(11),
`relation` smallint(1),
PRIMARY KEY (`relationidnum`)
)
Notes: ‘Userid’ one user in a relation who is related to ‘Userid2’ in some way. The ‘relation’ key shows kind of relation it is. 1 = Friend, 2 = Super friend. 3 = ‘Userid’ follows ‘Userid2.’ 4 = ‘Userid2’ follows ‘Userid’
Table # 4: Requests
CREATE TABLE `requests` (
`requestid` int(11) unsigned AUTO_INCREMENT,
`userid` int(11),
`requesteruserid` int(11),
`requestkind` smallint(1),
PRIMARY KEY (`requestid`)
)
Notes: ‘Userid’ is the user who is being requested to be in a relation with ‘requesteruserid’ by ‘requesteruserid.’ `requestkind` is the kind of relation that ‘requesteruserid’ is requesting to be. 1 = Friend, 2 = Super friend. 3 = Follow ‘Userid’ 4 = Have ‘Userid’ follow ‘requesteruserid’
Table # 5: Statuses
CREATE TABLE `statuses` (
`statusid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(11),
`text` char(255),
`link` char(255),
`pictureid` int(11),
`time` timestamp,
PRIMARY KEY (`statusid`)
)
Notes: ‘userid’ is the user who posted the status. ‘timestamp’ is the time it was posted. The rest is the content of the status. The statuses in this table could be updated (UPDATE command) by the user provided that a proper interface was provided. By deleting a tuple out of this table, statuses can be erased.
Table # 6: Comments
CREATE TABLE `comments` (
`commentid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`statusid` int(11),
`userid` int(11),
`comment` char(11),
PRIMARY KEY (`commentid`)
)
Notes: This table stores the comments on statuses arranged by ‘statusid’ The ‘userid’ is the id of the person making the comment. The ‘commentid’ could be used in the future to implement a ‘like’ feature where users ‘like’ a comment.
Table # 7: Blocking
CREATE TABLE `blocking` (
`blockingid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(11),
`blockeduserid` int(11),
PRIMARY KEY (`blockingid`)
)
Notes: `userid` blocks `blockeduserid`
Table # 8: Two-Way Messages
CREATE TABLE `msgs` (
`msgid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`senderid` int(11),
`recieverid` int(11),
`body` char(255),
`read` bool DEFAULT 0,
PRIMARY KEY (`msgid`)
)
Notes: `senderid` sends ‘body’ to ‘recieverid’ It’s possible to do an ‘inbox’ and ‘outbox’ using this table. Delete messages by removing its tuple. Both users can do this.
Table # 9: Group Messages
CREATE TABLE `msgs` (
`msgid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`senderid` int(11),
`groupid` int(11),
`body` char(255),
PRIMARY KEY (`msgid`)
)
Notes: `senderid` sends ‘body’ to the group ‘groupid’ It’s possible to do an ‘inbox’ and ‘outbox’ using this table. Delete messages by removing its tuple. Only the sender should be able to delete group messages. Not sure if messages to a ‘group’ should be posted on a page for that ‘group’ or instead sent to the ‘inbox’ of the user, hence the lack of read. unread feature for group messages.
Table # 10: Schools
CREATE TABLE `schools` (
`schoolid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` char(255) ,
PRIMARY KEY (`schoolid`)
)
Table # 11: Employers
CREATE TABLE `employers` (
`workid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` char(255),
PRIMARY KEY (`workid`)
)
Table # 12: Pictures
CREATE TABLE `pictures` (
`picid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`location` char(255),
PRIMARY KEY (`picid`)
)
HAMPDEN-SYDNEY, VA (05/16/2012)(readMedia)– On May 13, at commencement ceremonies marking the end of the 237th academic year at Hampden-Sydney College, Jared Christian, Class of 2012 (right), received the Algernon Sydney Sullivan Medallion. The award was presented by Dr. Christopher B. Howard, President of Hampden-Sydney College. The Algernon Sydney Sullivan Medallion is given annually by the New York Southern Society in honor of its first president, Algernon Sydney Sullivan. This award is given to a member of the graduating class distinguished for excellence of character and generous service to his fellows.
