Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This for private social network, so I wonder,

Which approach is more accurate and valid at this time. So I estimate the tables to have million rows,

First:

CREATE TABLE `system_auth_users` (

  `username` char(15) NOT NULL PRIMARY KEY,
  `password` varchar(32) NOT NULL,
  INDEX `username` (`username`)

)type=MyISAM;


CREATE TABLE `system_auth_info` (

  `username` char(15) NOT NULL PRIMARY KEY,
  `fname`    varchar(15) NOT NULL,
  `lname`    varchar(15) NOT NULL,
  `gender`    char(1) NOT NULL,
  `location`  varchar(3) DEFAULT NULL, 
  `descr`     varchar(15) DEFAULT NULL,
   INDEX `username` (`username`)

)type=MyISAM DEFAULT CHARSET=UTF8;


CREATE TABLE `system_auth_pass_recovery` (

  `username` char(15) NOT NULL PRIMARY KEY,
  `token`   varchar(35) NOT NULL,
  `time` int(10) NOT NULL   
   INDEX `username` (`username`)

)type=MyISAM DEFAULT CHARSET=UTF8;

The problem is that this approach is really good for maintenance/for queries, but it wastes free space by using duplicate row username

2 :

Second approach is to create single table for all common tasks (authorization, password recovery, profile)

CREATE TABLE `system_auth_info` (

  `username` char(15) NOT NULL PRIMARY KEY, /* for password recovery, for authorization, for 'personal info' */
  `password` varchar(32) NOT NULL,
  `token`   varchar(35) NOT NULL,
  `fname`    varchar(15) NOT NULL,
  `lname`    varchar(15) NOT NULL,
  `gender`    char(1) NOT NULL,
  `location`  varchar(3) DEFAULT NULL, 
  `descr`     varchar(15) DEFAULT NULL,
   INDEX `username` (`username`)

)type=MyISAM DEFAULT CHARSET=UTF8;

the benefit of this approach is that this one doesnt waste memory, but makes a little bit harder to maintain whole system

So, the question is, Which is approach is recommended for >= 1 000 000 rows ( including perfomance ) ?

Thanks.

share|improve this question
    
Have you looked at dba.stackexchange.com/questions/4622/… –  JNK Jun 11 '12 at 19:07
    
@JNK I've looked at similar as this ones, they tells to use first approach, but this one wastes memory. number of users > 1 000 000 => x * number of users waste : ( –  djay Jun 11 '12 at 19:18
4  
It's called normalization and a million rows isn't that much. You're talking about an exta 28 megs. –  JNK Jun 11 '12 at 19:21
    
What you duplicate is the Primary Key of one table, as Foreign Key in the 2 other tables. (By the way you should use InnoDB so actual Foreign Keys are defined and integrity is enforced). That's (as @JNK mentioned) called normalization. The only thing that can/should be duplicated is the PKs. –  ypercube Jun 11 '12 at 22:33
    
You don't seem to care much though about duplication of indexes. Why all your tables have 2 identical indexes? The PRIMARY index is enough, the second username index (in every table) is redundant. –  ypercube Jun 11 '12 at 22:35
show 3 more comments

1 Answer

up vote 0 down vote accepted

So after spending days and night, I found the answer, The first approach is far better for common purpose. Twitter and Facebook use that one

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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