A Small Library DB! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

A Small Library DB!

Author

Topic

ConfusedOfLife
Starting Member

35 Posts

Posted - 2002-10-10 : 21:17:29

Let's ask a kind of normalization question.
I'm trying to make a sample ( not quite sample ) libray in here with three main entries :

1- A Book's Name
2- A Book's Author(s)
3- A Book's Category

So, I think that it's better that I use a many-2-many relationship in the following way:
I make a table ( my juncture table ) with 3 coloumns, Name, Author and Category. If book 1 called "ABCD" has 3 authors, A, B and C, it surely has just one category, for example "Computer", ok? So, If I wana make my junction table, I will write

Name Author Category
--------- --------------- -------------------
ABCD A Computer
ABCD B Computer
ABCD C Computer

Now if someone searches for all books whose topic is "Computer", one of the results is gonna be our sample book, but it shows 3 rows for our book, reperesnting all the 3 authors. Isn't it anyway that we can avoid this?

Another strategy is that we have a 2 coloumns table, Name and Category, it means that the name of each book is just gonna show up once in the Name's coloumn, then we link this Name's table to our Authors table that holds the name of authors in it's foreign key called Authors for example ( that's not surely a unique coloumn ).

Which one is better? ( maybe none of them! )

<edit> moved to MS Access forum </edit>

Edited by - robvolk on 10/12/2002 11:26:18

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-10 : 21:23:54

Have you looked at the pubs database on your SQL Server? It's a model for book publishing and sales tracking. It keeps titles and authors in separate tables, and uses a titleauthors junction table to store multiple authors. It stores the book category in the titles table as a single column, like you have in your 2nd example.

If you need to show the books authors, but presented on one row, take a look at some of these articles:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

There are a few that show how to consolidate multiple rows into one.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-10 : 21:29:33

I would have an Author's Table

AuthorID Primary Key Numeric(Depending on your needs)
Author
...extra info unique to author

Then a Category table
CategoryID
...extra info unique to categories

Then a Books Table
BookID Primary Key
Book
CategoryID
...extra info unique to book

Then a Book_Author Table

BookID,AuthorID As Primary Key

ConfusedOfLife
Starting Member

35 Posts

Posted - 2002-10-12 : 07:43:32

Thank you guys.
I saw pubs when excecuting "exec sp_help" to learn "exec" for the first time! ( I'm still a baby ), but I never went through that.

And you dear Valter, it's actually what I came up with myself! But to be honest, I dunno how to implement that! I'm not that strong in SQL ( just learning the ropes! ) and I'm trying to write it with Access, everything is fine in Access, I mean the junction having just two coloumns, one the bookIDs and another the AuthorID. But the thing is that if someone wants to register a new book, he has to enter the name of the book in the Books table and gets the BookID ( that's an autonumber field ), then entering the Authors' names in the Author's Table will give him their ID too ( it's an autonumber field again ), and then he has to go to the Junctions table and enters the book's ID for the number of different authors and then he has to enter each author's ID in front of the books IDs that he's just entered to perform a normal registration!
You see that it's a tedious job! For registering each book, you have to go through 3 tables and I know that if I wanted to do it with SQL, I could have written some piece of code that when he enters the registration information, 3 tables get uploaded automatically, but unfortunately I dunno how to do it in access and I wanted to DENORMALIZE the tables somehow that registration gets easier, you know what I mean?

Any help would be appericiated...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-12 : 09:40:37

quote:
but unfortunately I dunno how to do it in access and I wanted to DENORMALIZE the tables somehow that registration gets easier, you know what I mean?

That's precisely what you SHOULD NOT do. Denormalizing the tables in this instance will still leave you with your original problem and no way to solve it.

quote:
But the thing is that if someone wants to register a new book, he has to enter the name of the book in the Books table and gets the BookID ( that's an autonumber field ), then entering the Authors' names in the Author's Table will give him their ID too ( it's an autonumber field again ), and then he has to go to the Junctions table and enters the book's ID for the number of different authors and then he has to enter each author's ID in front of the books IDs that he's just entered to perform a normal registration!

Bingo! You've got the procedure figured out for using 3 tables, so why don't you just do it?

CREATE PROCEDURE AddBookAuthor @bookTitle varchar(100), @author varchar(30) AS
DECLARE @bookid int, @authorid int
INSERT INTO Authors (Name) VALUES (@author)
SET @authorid=@@IDENTITY
INSERT INTO Titles (Title) VALUES (@bookTitle)
SET @bookid=@@IDENTITY
INSERT INTO TitleAuthor (AuthorID, BookID) VALUES (@authorid, @bookid)

That wasn't too hard.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-12 : 11:23:59

quote:
I'm trying to write it with Access

quote:
but unfortunately I dunno how to do it in access

Public Function AddBookAuthor(ByVal strBookTitle As String, ByVal strAuthor As String)
On Error Goto Err_Handler

Dim objConnection As ADODB.Connection
Dim objCommand As ADODB.Command
Dim objRs As ADODB.Recordset
Dim intAffectedRecords As Integer
Dim lngAuthorID As Long
Dim lngBookID As Long

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
Set objRs = CreateObject("ADODB.Recordset")

objConnection.Open (strConnect) ''Put your connect string here
objCommand.ActiveConnection = objConnection
objConnection.BeginTrans

objCommand.CommandText = "INSERT INTO Authors (Name) VALUES (" & ' Replace(strAuthor, "'", "''") & "')"

objCommand.Execute intAffectedRecords, , adExecuteNoRecords

objCommand.CommandText = "SELECT MAX(authorid) As AuthorID FROM Authors"

Set objRs = objCommand.Execute

lngAuthorID = objRs("AuthorID")

objCommand.CommandText = "INSERT INTO Titles (Title) VALUES (" & ' Replace(strBookTitle, "'", "''") & "')"

objCommand.Execute intAffectedRecords, , adExecuteNoRecords

objCommand.CommandText = "SELECT MAX(bookID) As BookID FROM Titles"

Set objRs = objCommand.Execute

lngBookID = objRs("BookID")

objCommand.CommandText = "INSERT INTO TitleAuthor (AuthorID,BookID) VALUES (" & lngAuthorID & ", " & lngBookID & ")"

objCommand.Execute intAffectedRecords, , adExecuteNoRecords

objConnection.CommitTrans
objConnection.Close

Set objConnection = Nothing
Set objCommand = Nothing
Set objRs = Nothing

Exit Function
ErrorHandler:
objConnection.RollbackTrans
objConnection.Close
Set objConnection = Nothing
Set objCommand = Nothing
Set objRs = Nothing
End Function

Edited by - ValterBorges on 10/12/2002 11:27:13

ConfusedOfLife
Starting Member

35 Posts

Posted - 2002-10-14 : 15:18:23

Thanx to you all!
I know that it should be easy, but when you don't know the syntax, then nothing is easy! anyway, thanx for the code Valter, I'm gonna work on it till I figure it out and I hope I can help you too one day ( when I learn! ).

Cheers

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-14 : 16:37:36

quote:
it surely has just one category, for example "Computer"

Well, I guess that depends on how broadly you define your categories, but I wouldn't be too surprised to find that you need multiple categories per book. To do that, you'd be better off with two junction tables: One is TitleAuthor (like pubs) and the other is TitleCategory. And if you ever needed to know all authors in a category you could join some tables to find that out.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-14 : 17:57:35

Anytime Confused,
You already help me.
By reading about problems that others are having I'm constantly learning and being challenged.

Edited by - ValterBorges on 10/14/2002 17:59:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources