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 ?

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
user3610792
  • 33
  • 5
  • 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: http://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. http://dba.stackexchange.com/questions/50708/do-natural-keys-provide-higher-or-lower-performance-in-sql-server-than-surrogate – Hannah Vernon May 14 '14 at 19:00

1 Answers1

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.

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
  • 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. – Hannah Vernon May 14 '14 at 18:29