CODEWITHSUNDEEP
mysqldatabase-designkeyword
Legend
Legend

Reputation: 116820

Any suggestions for a db schema for storing related keywords?

I have to store a set of related keywords inside a database. As of now, I am thinking of using the following:

To store the keywords themselves:

CREATE TABLE keywords(
   id int(11) AUTO_INCREMENT PRIMARY KEY,
   word VARCHAR(255)
);

To store the relations (stores the ids of the related keywords):

CREATE TABLE relatedkeywords(
   id int(11) AUTO_INCREMENT PRIMARY KEY,
   keyword1 int(11),
   keyword2 int(11),
   FOREIGN KEY (keyword1) REFERENCES keywords(id),
   FOREIGN KEY (keyword2) REFERENCES keywords(id)
);

Is this the convention or is there a better way of doing this? The only problem I am seeing is that I need to check both the column in order to be able to get the related keywords sometimes... I might be missing something here.

Upvotes: 2

Views: 1402

Answers (3)

blispr
blispr

Reputation: 912

Is there a solution with just one table - 

create table keywords (
   keywrd varchar (40) not null primary key,
   related_keys_csv varchar(400)  
)

Upvotes: 0

Alex Martelli
Alex Martelli

Reputation: 881635

Simplify the second table to:

CREATE TABLE relatedkeywords(
   keyword1 int(11),
   keyword2 int(11),
   FOREIGN KEY (keyword1) REFERENCES keywords(id),
   FOREIGN KEY (keyword2) REFERENCES keywords(id),
   PRIMARY KEY (keyword1, keyword2)
)

as this is one of the cases where an "artificial primary key" just makes little sense and offers no practical usefulness.

Upvotes: 2

Dmitry
Dmitry

Reputation: 3780

If "relatedness" is a property of a pair of keywords, this schema is OK (don't forget to add UNIQUE(keyword1, keyword2))

If "relatedness" can spread a set of keywords and a set of related keywords may have additional propertirs, you may want to add a new table "Related_Set" and a M:N relationship "Keyword_Set" between keywords and sets.

If a set doesn't have any additional properties, you may just live with "Keyword_Set" table

Upvotes: 3

Related Questions