I've got a many key phrases and you will find a listing of words connected with every keyword

Say key phrases are SCIENCE , Computer systems, STRUCTURES

You will find a listing of words under each keyword like

SCIENCE - botany , zoology, physics .....

Computer systems - databases , os's, .....

STRUCTURES - stacks, queues, arrays, ....

I have to store this during my database, exactly what is a good design with this? Storing these questions single table appears stupid because they are not associated with one another, but creating different table appears just like a overhead too! Here I'm confused.

In my opinion this will do.

Category (ID, NAME)
  1  Science
  2  Computers
  3  Structures

SubCategory(ID, CATID, NAME)
  1  1  botany
  2  1  zoology
  3  2  databases

CATID in subcategory is really a foreignkey referencing the ID in category table.

You are considering "relations" in the wrong manner. While so botany isn't associated with databases (except in certain very, unusual corner cases), you are searching in the data, not the factor.

The way you model this is dependent how you are viewing these key phrases. Is a strict single-parent relationship, in which a child keyword can never have children and child key phrases are essentially not the same as parent key phrases (quite simply, the mother and father serve only like a classification mechanism and therefore are not, themselves, key phrases)? Or would you "nest" these key phrases randomly deeply and employ them as key phrases at any level (quite simply, "botany" may have children, and "SCIENCE" is equally as much a keyword as "botany" is)?

Whether it's the very first, you would model it something similar to this:

CategoryID (PK)

KeywordID (PK)
CategoryID (FK)

Whether it's the 2nd, you'd model it something similar to this:

KeywordID (PK)
ParentKeywordID (FK)

Where ParentKeywordID is really a nullable foreign key to Keyword. You've produced a table that references itself, and structures such as this define a tree structure with nodes that may be nested at any level.

Side note

Many will explain that storing null values within the database is an awful idea, and that i would accept these a lot of people. Should you wish to visit a completely stabilized storage format (stabilized to 5NF, anyway), you'd need to do it such as this:

KeywordID (PK)

KeywordID (PK, FK)
ParentKeywordID (FK)

Your top-level key phrases simply will not have rows in KeywordParent.

This kind of design is usually regarded as as "better" from the database design perspective, although it will slightly complicate your queries (only within their construction they will not perform any worse, and could really perform better without nullable posts).


1 null Science 
2 null Computers
3 null Structures
4  1   botany
5  1   zoology
6  2   databases

Typical Tree structure.