1

I have table as

CREATE TABLE circle
(
    circle_id int(11) auto_increment primary key not null,
    user_id int (11) not null,
    title varchar(255) collate  utf8_general_ci not null
);


CREATE TABLE circle_share1
(
    id int(11) auto_increment primary key not null,
    circle_id int(11),
    user_id int(11),
    start_time date
);


CREATE TABLE circle_share2
(
    circle_id int(11),
    user_id int(11),
    start_time date,
    primary key(circle_id , user_id)
);

What are the advantages of using table share1 over share2 ?

share2 has primary key of circle_id , user_id 

and share1 has primary key id .

I checked 50 tables out of them none of the table is like share2 , Why developers are not preferred share2 ?

  • 1
    What you're looking for is a definitive answer to the long-standing debate between using a surrogate key (circle/circle_share1) or the natural key (circle_share2). different dbms' may handle them differently. here's some more information for you: stackoverflow.com/q/63090/251174 – swasheck May 14 '14 at 18:47
  • Here is another question I wrote about the subject that has some interesting answers. dba.stackexchange.com/questions/50708/… – Max Vernon May 14 '14 at 19:00
2

circle_share1 does not require much in-depth design thought. circle_share2 requires some actual brain cycles to determine if the primary key will have the desired performance.

Rows in circle_share1 will be written to the disk in the exact order they are inserted, thereby making inserts quicker.

Rows in circle_share2 may be inserted anywhere in the table, necessitating page-splits thereby fragmenting the data and possibly resulting in slower performance.

Neither way is the correct way in all circumstances - the best way depends on your data, and how it will be created in the user and circle tables and inserted into the circle_shareX table.

|improve this answer|||||
  • I have thousands or millions of tupples in circle_shareX as well as circle and data is inserted in the tables frequently like social networking site . In this scenario Which one is best choice based on performance . – user3610792 May 14 '14 at 18:20
  • Without me placing all that much thought into your situation, it is likely that placing the primary key on the auto_increment field would be better. It would certainly be faster for inserts on the circle_shareX table. – Max Vernon May 14 '14 at 18:29

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.