Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a database newbie, so please bear with me.

Say I'm creating a library database. I have a table containing information about books. Do you put the book availability as a row in this table or would you create another table containing only the book (using a foreign key) and the availability?

Any pointers would be gratefully received.

share|improve this question
    
I would place it on the same table !! <br> –  Up_One Feb 27 at 19:27
1  
Most library systems I've seen support the concept of "copies"; the "books" table contains one row for each title, while the "copies" table contains one row for each copy of that title. –  Colin 't Hart Feb 27 at 19:27
    
@Up_One Why? I wouldn't -- see my comment above. –  Colin 't Hart Feb 27 at 19:28
    
And what about "formats" (hardcover, softcover, eBook formats), "printings", and "editions" of what is essentially the same book? –  Colin 't Hart Feb 27 at 19:29
    
This is irrelevant to the question. Let's say every book is unique. The question I am asking addresses the issue of a non constant field, that isn't a piece of information about the book itself, like the title is. –  brian.p.h Feb 27 at 19:34

3 Answers 3

It's depend on what you want achieve. What your database will do? Do you want track who lend book and when? You can create table with transaction and based on return date set availability. Another question is how you want to implement this logic. It could be controlled by your front-end or you can create view, stored procedures or implement triggers. For simplicity you can add column IsAvailable as bit (1,0). I don't now what database you are using.

share|improve this answer

The question you have asked is very generic and you should Expect changes in the answer as you clarify more details about what workflow around the accessing this information. but my first take based on what so far you have given is as follows:

Information about the Book is rarely changing or less frequently changing information depending on what attributes you have included here. On the Other hand "Availability" is probably very frequently changing attribute. Lets say you Define "Availability" with as simple indicator Yes/No as possible values.

putting this column on the same Table will have PLUS point is your read query will involve only one table but down side is as the "Availability" changes the update is going to be the against this large table. different type of read query can be requested against that table and down the road you might find Availability is not that important for 60% of the query user is doing on this table; and hence that will affect decision what column are part of Indexes. this table might need "FULL-TEXT", "FILTERED Index", "Fuzzy Search" etc....

Putting this column on a new table has a plus point that 90% of time update query against this table will involve "BookID" and "Availability" column in them so you can optimize the indexes on this table as you like w/o worrying about the performance of Read queries on the other table where mostly constant information of Book is stored.

now as you can see above is applicable IF the possible values are "Yes/No" or "0/1" etc.... as your definition and Implementation of "Availability" changes, you have to re-evaluate your decision.

share|improve this answer

Thinking of a data model for a library ! having the AVAILABLE on the same table with NAME,ID,,,etc makes scene to me as it's an ATIRIBUT/STATE of that book.
Now a question for Collin :
Why would you not have the AVAILABLE attribute on the same Entitiy ? since you have rated me down !
How you can handle such an attribute in the same Entity:
- making the AVAILABLE having the values 0(unavailable) and 1(available), this column can have a constraint related to the transaction table(it's state would be dictated by it's transaction final state- book was returned or not).
But there are many ways in which you can layout your logical model. As i had said in my first line - "I would" - i didn't said "you should"

What i am trying to say is better described in the image bellow - where the status is the same as availability
enter image description here

share|improve this answer
    
What is the inventory_qty? The number of copies of a book? If yes, how does your model deal with some copies being available and others not? –  ypercube Mar 29 at 22:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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