0

If we need to have 2 tables, one storing the author info(author_info), & another storing all the books info(book_info).

author_info has authorId as it's primary key, whereas the book_info table has bookId has it's primary key.

Now to answer a question like fetch all books written by an author in an OLTP system (latency is key), which of the following 2 will be better:

1) RDBMS: Having authorId as a foreign key in the book_info table, and joining the two tables based on the authorId.

2) Wide Column Store: Modifying the second table to be like a wide-column DB, wherein we author_id as the primary key, and simply query the second table based in author_id.

0

I would go with option 3, and have a many to many mapping table to associate Books and Authors. What if you have a book with more than one author? I've included an example for you below.

Your second approach could work for you. But the answer as to what's best is how will you be querying for the information? If you are going to query for data inside the Wide Column Store, that's going to be slow through SQL.

CREATE TABLE dbo.Author
    (
    AuthorID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AuthorName NVARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.Book
    (
    BookID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , BookName NVARCHAR(100) NOT NULL
    , ISBN NVARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.AuthorBook
    (
    AuthorBookID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , AuthorID INT NOT NULL REFERENCES dbo.Author (AuthorID)
    , BookID INT NOT NULL REFERENCES dbo.Book (BookID)
    )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.