If I have tblBookInfo (bookId, title… etc.) and I want it to have categories column, what is the best way to do it?

If I have tblBookInfo (bookId, title... etc.) and I want it to have categories column, what is the best way to do it?

categories table will have FK related to bookId like this

bookId title
1 test title
2 test title 2


bookId name
1 Science fiction
1 Mystery
1 Horror
2 Science fiction
2 Mystery


In this case, category name will be repeated many times and it's ugly

Store an array of int in btlBookInfo like this

bookId title categories
1 test title [1,2,3]
2 test title 2 [1,2]


I heard storing array in a column is not a best practice as asked here

Leave the categories table as it is like this

id name
1 Science finction
2 Mystery
3 Horror


And add a FK to tblBookInfo like this

bookId title categoryId
1 test title 1
2 test title 2 2


Finally, add another table that links these to tables like this

bookId categoryId
1 1
1 2
1 3
2 1
2 2


Now I don't have to repeat the category name but I don't think if it is normal

1 Answer
1

It can be fairly straightforward to determine how to architect the database by a thorough understanding of the problem domain, examining the entities you've discovered, and being sure you understand the relationships between those entities.

Here you have two entities: Book and Category. It appears that two rules you've already determined from your problem domain are:

The above can be simplified into "There exists a many-to-many relationship between Books and Categories."

In classic SQL database engines it is not possible to implement a many-to-many relationship directly between two tables. It must be implemented by a 1-to-many or 0-to-many relationship between each of the two original tables with and a new table that is used to cross-reference the rows of the original two tables. Such tables are variously called "cross-reference table", "relationship table", "join table" or "intersection table".

In your case, it appears that you need a table to cross-reference Books to Categories, and vice versa.

This might be diagrammed (somewhat poorly as it's hard to diagram in Stack Overflow) as:

Book <----- Book_Category -----> Category


or

Book -1-----M- Book_Category -M-----1- Category


So you n eed to introduce that Book_Category table (with whatever name you choose), which contains a foreign key to the Book table and a foreign key to the Category table.

You might do it like this:

tblBookInfo

CREATE TABLE tblBookInfo(
BookId int not null,
Name varchar(50) not null,
CONSTRAINT PK_tblBookInfo PRIMARY KEY CLUSTERED (BookId)
);


tblCategory

CREATE TABLE tblCategory(
CategoryId int not null,
Name varchar(50) not null,
CONSTRAINT PK_tblCategory PRIMARY KEY CLUSTERED (CategoryId)
);


tblBookInfo_Category

CREATE TABLE tblBookInfo_Category(
BookId int not null FOREIGN KEY REFERENCES tblBook(BookId),
CategoryId int not null FOREIGN KEY REFERENCES tblCategory(CategoryId)
);


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.