I have 2 tables, articles and entities table. An article can have many entities and an entity can belong to more than one category.
What would be the sql to create such an association in mysql such that I can insert an entity to the entities table via the articles table and be able to query for the article from the entities table.
The entity only has 2 fields, entity_id and entity while the articles table has 3 fields: article_id, url and category
Is this what I require to do
CREATE TABLE articles(
article_id INT NOT NULL AUTO_INCREMENT,
url VARCHAR(255),
category VARCHAR(255),
PRIMARY KEY(article_id)
)ENGINE=INNODB;
CREATE TABLE entities(
entity_id INT NOT NULL AUTO_INCREMENT,
entity VARCHAR(50),
PRIMARY KEY(entity_id)
)
CREATE TABLE relationships(
relationship_id INT NOT NULL AUTO_INCREMENT,
article_id INT,
entity_id INT,
PRIMARY KEY(relationship_id),
FOREIGN KEY(article_id) references articles(article_id),
FOREIGN KEY(entity_id) references entities(entity_id)
)ENGINE=INNODB;