CODEWITHSUNDEEP
mysqldatabasedatabase-design
Sraj
Sraj

Reputation: 41

Suggestion on DB table design

I have two tables Table A and B as shown below.

Table A is a normal table with single primary key and other columns.

Table B has two foreign keys from the same table (Table A) and together forms a primary key.

Is it the right way to keep composite key or keep separate unique column with auto increment?

Which is the best?

Table A

id(PK)  name
------------
1       aa
2       ab
3       ac
4       ad

Table B

master_id(FK_id)    slave_id(FK_id)
----------------------------------
1                   2
2                   3
2                   4

Upvotes: 0

Views: 41

Answers (1)

Rick James
Rick James

Reputation: 142208

For a many-to-many mapping table it is better to eschew the auto_increment and have a composite PRIMARY KEY. More tips: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Upvotes: 1

Related Questions