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
subcategory is really a foreignkey referencing the
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:
Category ---------- CategoryID (PK) Name Keyword --------- KeywordID (PK) CategoryID (FK) Name
Whether it's the 2nd, you'd model it something similar to this:
Keyword --------- KeywordID (PK) ParentKeywordID (FK) Name
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.
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:
Keyword ----------- KeywordID (PK) Name KeywordParent ------------- KeywordID (PK, FK) ParentKeywordID (FK)
Your top-level key phrases simply will not have rows in
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).
KeyWords (ID, PARENT, NAME) 1 null Science 2 null Computers 3 null Structures 4 1 botany 5 1 zoology 6 2 databases
Typical Tree structure.