To model a many:many relationship between two tables a and b, you need a bridging table where each row represents one instance of an association between a row in a and a row in b, as in this example:
drop table if exists users,actions,useractions; create table users(userid int primary key, username char(32)); insert into users values(1, 'James'),(2, 'Alex'),(3, 'Justin'); create table actions(actionid int primary key, action char(32)); insert into actions values(1, 'Login'),(2, 'Logout'),(3, 'Delete'),(4, 'Promote'); create table useractions(uaid int primary key, userid int, actionid int); insert into useractions values(1,1,1),(2,1,2),(3,3,4); select u.username, a.action from useractions ua join users u using (userid) join actions a using (actionid); +----------+---------+ | username | action | +----------+---------+ | James | Login | | James | Logout | | Justin | Promote | +----------+---------+Most every non-trivial database will have at least one instance of this pattern. We have a collection of articles and users' scores of them. How to report statistics on these scores? We need three tables--one for articles, one for users, and a bridge table where each row represents one score on one article by one user: DROP TABLE IF EXISTS art_articles; CREATE TABLE art_articles ( ID INT AUTO_INCREMENT PRIMARY KEY, title CHAR(30), txt TEXT, UNIQUE KEY (title) ); INSERT INTO art_articles VALUES (1,'abc',''),(2,'def',''),(3,'ghi',''),(4,'jkl',''); DROP TABLE IF EXISTS art_users; CREATE TABLE art_users( ID INT AUTO_INCREMENT PRIMARY KEY, name CHAR(20) ); INSERT INTO art_users VALUES (1,'A'),(2,'B'); DROP TABLE IF EXISTS art_scores; CREATE TABLE art_scores ( id INT AUTO_INCREMENT PRIMARY KEY, articleID INT NOT NULL, -- references article.articleID userID INT NOT NULL, -- references user.userID score DECIMAL(6,2) ); INSERT INTO art_scores VALUES (1,1,1,80),(2,1,2,90),(3,2,2,60); -- find average score for article titled 'abc' SELECT a.title, AVG( s.score ) AS Art1Avg FROM art_articles a JOIN art_scores s ON a.id=s.articleID WHERE a.title='abc' GROUP BY a.title; -- find average score submitted by user 1 SELECT u.name, AVG( s.score ) AS User1Avg FROM art_users u JOIN art_scores s ON u.id=s.userID WHERE u.id = 1 GROUP BY u.name; |