7
\$\begingroup\$

Below is the database design that I created for a basic image sharing social website.

  • When a user signup his username, email, password save in users
  • When the user setup his profile his information will be saved in profile against username
  • When the user create a post it will be saved in posts against username
  • When a comment is made on a post it will be stored in comments against postid.
-- phpMyAdmin SQL Dump
-- version 4.9.0.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Aug 31, 2019 at 08:46 AM
-- Server version: 10.4.6-MariaDB
-- PHP Version: 7.3.8

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `pakart`
--

-- --------------------------------------------------------

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `commentid` int(10) UNSIGNED NOT NULL,
  `commenttext` text NOT NULL,
  `commenttime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `postid` int(10) UNSIGNED NOT NULL,
  `username` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `posts`
--

CREATE TABLE `posts` (
  `postid` int(10) UNSIGNED NOT NULL,
  `username` varchar(25) NOT NULL,
  `posttitle` varchar(50) DEFAULT NULL,
  `posttag` varchar(25) DEFAULT NULL,
  `imageurl` varchar(255) NOT NULL,
  `posttime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `likecount` int(10) UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `profile`
--

CREATE TABLE `profile` (
  `profileid` int(10) UNSIGNED NOT NULL,
  `username` varchar(25) NOT NULL,
  `firstname` varchar(25) NOT NULL,
  `lastname` varchar(25) NOT NULL,
  `gender` char(1) NOT NULL,
  `dateofbith` date NOT NULL,
  `pictureurl` varchar(255) NOT NULL,
  `bio` text NOT NULL,
  `joindate` date NOT NULL,
  `rating` int(10) UNSIGNED DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `username` varchar(25) NOT NULL,
  `usermail` varchar(50) NOT NULL,
  `userpass` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `comments`
--
ALTER TABLE `comments`
  ADD PRIMARY KEY (`commentid`),
  ADD KEY `postid` (`postid`),
  ADD KEY `username` (`username`);

--
-- Indexes for table `posts`
--
ALTER TABLE `posts`
  ADD PRIMARY KEY (`postid`),
  ADD UNIQUE KEY `imageurl` (`imageurl`),
  ADD KEY `username` (`username`);

--
-- Indexes for table `profile`
--
ALTER TABLE `profile`
  ADD PRIMARY KEY (`profileid`),
  ADD UNIQUE KEY `pictureurl` (`pictureurl`),
  ADD KEY `username` (`username`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`username`),
  ADD UNIQUE KEY `usermail` (`usermail`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `comments`
--
ALTER TABLE `comments`
  MODIFY `commentid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `posts`
--
ALTER TABLE `posts`
  MODIFY `postid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `profile`
--
ALTER TABLE `profile`
  MODIFY `profileid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `comments`
--
ALTER TABLE `comments`
  ADD CONSTRAINT `Comments_ibfk_1` FOREIGN KEY (`postid`) REFERENCES `posts` (`postid`),
  ADD CONSTRAINT `Comments_ibfk_2` FOREIGN KEY (`username`) REFERENCES `users` (`username`);

--
-- Constraints for table `posts`
--
ALTER TABLE `posts`
  ADD CONSTRAINT `Posts_ibfk_1` FOREIGN KEY (`username`) REFERENCES `users` (`username`);

--
-- Constraints for table `profile`
--
ALTER TABLE `profile`
  ADD CONSTRAINT `Profile_ibfk_1` FOREIGN KEY (`username`) REFERENCES `users` (`username`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I need recommendations related datatypes, data redundancy and normalization if any. Database Design

\$\endgroup\$
  • 5
    \$\begingroup\$ Since this is Code Review, please repost this question in code form (CREATE TABLE statements). \$\endgroup\$ – 200_success Aug 31 at 5:14
  • 5
    \$\begingroup\$ @200_success i have added Database Export. \$\endgroup\$ – Malik Bilal Aug 31 at 7:00
4
\$\begingroup\$

By default you will have a numeric userId as well. Using the numeric userId rather than the username will probably perform better and reduce the size of the other tables. The userId will be a foreign key in 3 of the 4 tables and you need to consider the restraints that apply in each of the tables.

Naming a picture in the user's profile pictureurl could be confusing, I originally thought there were 2 instances of the post image URL. Any URL fields should probably be at least 1024 characters.

Decide what the most important fields in the profile are and put them in the table. You can use a JOIN to get all of the fields in the profile together, but by default I doubt the the user will use all the fields in the profile. Limit the size of the BIO, they shouldn't need any more than 1024 characters for the bio.

Limit the size of the comment text.

You may want to have a separate table of tags and include the tagId instead of the tag text. Post titles and post tags may require a larger size then is allowed for.

In the profile what is the rating and how is it calculated? You may want to have a floating point value rather than an integer value.

\$\endgroup\$
  • 2
    \$\begingroup\$ That first paragraph is the most important one. Picking the correct PK allows you to model against future changes. If UserName is a PK, how would you handle when a user changes their UserName? \$\endgroup\$ – dfhwze Aug 31 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.