New table needed ?

Jian0203 · 01-01-2013, 02:16 PM

Hi everyone, thank you for spending time to read my question. I appreciate it a lots.

I am implementing a web-based graphical password system. The user will click on the enrolled categories to login. Now, i wish to add in another login method which user will need to click on the enrolled categories of picture with certain numbers of time.

During registration, the user will enroll 5 categories of image as password and number of times of click on respective category of image.

e.g. For category A, click count is 5, category B, click count is 3 and so on. Therefore, during authentication, user will click on the category A with 5 times and category B with 3 times so on.

If i want to add in the features, do i need to create another table ? If yes, how do i link the tables. If no, I just add in new column in my main table ?

My existing tables are listed below:

Quote:

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 26, 2012 at 06:01 AM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
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 utf8 */;

--
-- Database: `graphicpass2`
--

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

--
-- Table structure for table `checkpasstable`
--

CREATE TABLE IF NOT EXISTS `checkpasstable` (
`pass_name` varchar(1000) NOT NULL,
`pass_table_name` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `checkpasstable`
--

INSERT INTO `checkpasstable` (`pass_name`, `pass_table_name`) VALUES
('bird', 'pic_a'),
('car', 'pic_b'),
('computer', 'pic_c'),
('food', 'pic_d'),
('game', 'pic_e'),
('house', 'pic_f'),
('shoe', 'pic_g'),
('sofa', 'pic_h'),
('phone', 'pic_i'),
('woman', 'pic_j');

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

--
-- Table structure for table `custdetail`
--

CREATE TABLE IF NOT EXISTS `custdetail` (
`customer_id` varchar(1000) NOT NULL,
`customer_pass1` varchar(1000) NOT NULL,
`customer_pass2` varchar(1000) NOT NULL,
`customer_pass3` varchar(1000) NOT NULL,
`customer_pass4` varchar(1000) NOT NULL,
`customer_pass5` varchar(1000) NOT NULL,
`cust_email` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `custdetail`
--

INSERT INTO `custdetail` (`customer_id`, `customer_pass1`, `customer_pass2`, `customer_pass3`, `customer_pass4`, `customer_pass5`, `cust_email`) VALUES
('tan', 'bird', 'car', 'computer', 'food', 'game', 'tan'),
('shlau', 'bird', 'car', 'computer', 'food', 'game', 'lau.siong.hoe@mmu.edu.my'),
('mmu', 'bird', 'car', 'computer', 'food', 'game', 'mmu@gmail.com'),
('fist', 'bird', 'car', 'computer', 'food', 'game', 'fist@mmu.edu.my');

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

--
-- Table structure for table `picture`
--

CREATE TABLE IF NOT EXISTS `picture` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`pic_id`),
UNIQUE KEY `pic_id` (`pic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `picture`
--

INSERT INTO `picture` (`pic_id`, `pic_link`) VALUES
(1, '1.jpg'),
(2, '2.jpg'),
(3, '3.jpg'),
(4, '4.jpg'),
(5, '5.jpg'),
(6, '6.jpg'),
(7, '7.jpg'),
(8, '8.jpg'),
(9, '9.jpg'),
(10, '10.jpg'),
(11, '11.jpg'),
(12, '12.jpg'),
(13, '13.jpg'),
(14, '14.jpg');

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

--
-- Table structure for table `pic_a`
--

CREATE TABLE IF NOT EXISTS `pic_a` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_a`
--

INSERT INTO `pic_a` (`pic_id`, `pic_link`) VALUES
(1, 'a1.jpg'),
(2, 'a2.jpg'),
(3, 'a3.jpg'),
(4, 'a4.jpg'),
(5, 'a5.jpg'),
(6, 'a6.jpg'),
(7, 'a7.jpg'),
(8, 'a8.jpg'),
(9, 'a9.jpg'),
(10, 'a10.jpg');

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

--
-- Table structure for table `pic_b`
--

CREATE TABLE IF NOT EXISTS `pic_b` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_b`
--

INSERT INTO `pic_b` (`pic_id`, `pic_link`) VALUES
(1, 'b1.jpg'),
(2, 'b2.jpg'),
(3, 'b3.jpg'),
(4, 'b4.jpg'),
(5, 'b5.jpg'),
(6, 'b6.jpg'),
(7, 'b7.jpg'),
(8, 'b8.jpg'),
(9, 'b9.jpg'),
(10, 'b10.jpg');

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

--
-- Table structure for table `pic_c`
--

CREATE TABLE IF NOT EXISTS `pic_c` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_c`
--

INSERT INTO `pic_c` (`pic_id`, `pic_link`) VALUES
(1, 'c1.jpg'),
(2, 'c2.jpg'),
(3, 'c3.jpg'),
(4, 'c4.jpg'),
(5, 'c5.jpg'),
(6, 'c6.jpg'),
(7, 'c7.jpg'),
(8, 'c8.jpg'),
(9, 'c9.jpg'),
(10, 'c10.jpg');

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

--
-- Table structure for table `pic_d`
--

CREATE TABLE IF NOT EXISTS `pic_d` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_d`
--

INSERT INTO `pic_d` (`pic_id`, `pic_link`) VALUES
(1, 'd1.jpg'),
(2, 'd2.jpg'),
(3, 'd3.jpg'),
(4, 'd4.jpg'),
(5, 'd5.jpg'),
(6, 'd6.jpg'),
(7, 'd7.jpg'),
(8, 'd8.jpg'),
(9, 'd9.jpg'),
(10, 'd10.jpg');

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

--
-- Table structure for table `pic_e`
--

CREATE TABLE IF NOT EXISTS `pic_e` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_e`
--

INSERT INTO `pic_e` (`pic_id`, `pic_link`) VALUES
(1, 'e1.jpg'),
(2, 'e2.jpg'),
(3, 'e3.jpg'),
(4, 'e4.jpg'),
(5, 'e5.jpg'),
(6, 'e6.jpg'),
(7, 'e7.jpg'),
(8, 'e8.jpg'),
(9, 'e9.jpg'),
(10, 'e10.jpg');

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

--
-- Table structure for table `pic_f`
--

CREATE TABLE IF NOT EXISTS `pic_f` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_f`
--

INSERT INTO `pic_f` (`pic_id`, `pic_link`) VALUES
(1, 'f1.jpg'),
(2, 'f2.jpg'),
(3, 'f3.jpg'),
(4, 'f4.jpg'),
(5, 'f5.jpg'),
(6, 'f6.jpg'),
(7, 'f7.jpg'),
(8, 'f8.jpg'),
(9, 'f9.jpg'),
(10, 'f10.jpg');

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

--
-- Table structure for table `pic_g`
--

CREATE TABLE IF NOT EXISTS `pic_g` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_g`
--

INSERT INTO `pic_g` (`pic_id`, `pic_link`) VALUES
(1, 'g1.jpg'),
(2, 'g2.jpg'),
(3, 'g3.jpg'),
(4, 'g4.jpg'),
(5, 'g5.jpg'),
(6, 'g6.jpg'),
(7, 'g7.jpg'),
(8, 'g8.jpg'),
(9, 'g9.jpg'),
(10, 'g10.jpg');

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

--
-- Table structure for table `pic_h`
--

CREATE TABLE IF NOT EXISTS `pic_h` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_h`
--

INSERT INTO `pic_h` (`pic_id`, `pic_link`) VALUES
(1, 'h1.jpg'),
(2, 'h2.jpg'),
(3, 'h3.jpg'),
(4, 'h4.jpg'),
(5, 'h5.jpg'),
(6, 'h6.jpg'),
(7, 'h7.jpg'),
(8, 'h8.jpg'),
(9, 'h9.jpg'),
(10, 'h10.jpg');

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

--
-- Table structure for table `pic_i`
--

CREATE TABLE IF NOT EXISTS `pic_i` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_i`
--

INSERT INTO `pic_i` (`pic_id`, `pic_link`) VALUES
(1, 'i1.jpg'),
(2, 'i2.jpg'),
(3, 'i3.jpg'),
(4, 'i4.jpg'),
(5, 'i5.jpg'),
(6, 'i6.jpg'),
(7, 'i7.jpg'),
(8, 'i8.jpg'),
(9, 'i9.jpg'),
(10, 'i10.jpg');

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

--
-- Table structure for table `pic_j`
--

CREATE TABLE IF NOT EXISTS `pic_j` (
`pic_id` int(255) NOT NULL,
`pic_link` varchar(1000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `pic_j`
--

INSERT INTO `pic_j` (`pic_id`, `pic_link`) VALUES
(1, 'j1.jpg'),
(2, 'j2.jpg'),
(3, 'j3.jpg'),
(4, 'j4.jpg'),
(5, 'j5.jpg'),
(6, 'j6.jpg'),
(7, 'j7.jpg'),
(8, 'j8.jpg'),
(9, 'j9.jpg'),
(10, 'j10.jpg');

/*!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 */;

Old Pedant · 01-02-2013, 02:40 AM

What a *MESS*!

There is NO REASON AT ALL for you to have SO MANY TABLES!

*ALL ELEVEN OF THOSE TABLES* (from 'picture' through 'pic_j') should be in *ONE* TABLE!!!

PERIOD.

Code:

CREATE TABLE pictures (
    pic_letter CHAR(1),
    pic_id INT,
    pic_link VARCHAR(100),
    PRIMARY KEY (pic_letter, pic_id)
);
INSERT INTO pictures VALUES(' ',1,'1.jpg'),(' ',2,'2.jpg'), ...
INSERT INTO pictures VALUES('A',1,'a1.jpg'),('A',2,'A2.jpg'), ...
...
INSERT INTO pictures VALUES('J',1,'j1.jpg'),('J',2,'j2.jpg'), ...

Old Pedant · 01-02-2013, 02:43 AM

And the answer to your question is "NO."

Just add counts for each key-image to the table.

WHY do you use VARCHAR(1000)??? You really expect people to use 1000 characters?

Jian0203 · 01-02-2013, 07:01 AM

Quote:

Originally Posted by Old Pedant

And the answer to your question is "NO."

Just add counts for each key-image to the table.

WHY do you use VARCHAR(1000)??? You really expect people to use 1000 characters?

Thank you so much for the suggestion.
Do you mind to give an example ? I don't understand how can i record the click counts for each category of picture in the same table.

Old Pedant · 01-02-2013, 07:04 PM

???

Maybe I didn't understand what you were trying to do. I assumed you just needed:

Code:

CREATE TABLE IF NOT EXISTS custdetail (
    customer_id varchar(100), /* bad idea...should be INT and refer to customer tbl */
    customer_pass1 varchar(100),
    customer_count1 int,
    customer_pass2 varchar(100),
    customer_count2 int,
    customer_pass3 varchar(100),
    customer_count3 int,
    customer_pass4 varchar(100),
    customer_count4 int,
    customer_pass5 varchar(100),
    customer_count5 int
) ENGINE=InnoDB DEFAULT CHARSET=latin1;