Better table setup?

[dyingatmidnight]

So I have this table

checkout(cardNumber,libraryID, checkoutDate, checkinDate, dueDate)

that holds the checkin and checkout information for books in a library database. The trouble is I only now realized that with this set up a cardholder can't checkout the same book at a later date. I've been wracking my brain for a better setup but I can't seem to think of one. I thought about breaking it down into a table for checkouts and checkins separately, which would probably work but I'd like to be able to hold historical data, like what books have been taken out when so that cardholders can see what books they've borrowed in the past as well. I thought about making the checkoutDate a key as well but it can't really act as a key can it?

[urtrivedi]

Also add on more key that is autonumber say transaction ID.
To avoid duplication make combination of three columns as unique (cardnumber,libraryid,chekcoutdate)

checkout(trans_id,cardNumber,libraryID, checkoutDate, checkinDate, dueDate)

[dyingatmidnight]

oh hey cool. I wasn't sure if that was something I could legitimately do, like normalization wise or something. Thanks.

[smantscheff]

Well, normalization-wise you can't. The transaction ID is an easy way to refer to the checkout table in relations, but it does not solve your original problem. The three column unique index (cardnumber,libraryid,chekcoutdate) which urtrivedi proposed is a step further, but still the user cannot checkout a book on the same day twice, even if he checked it in in between. Therefore you'd better make checkoutdate a time field resolving to minutes or seconds.

[urtrivedi]

I do not think practically some one would issue then return and issue again on same day. I think even library rules also may not allow such practice. This is an exception and I think exceptions are not included in systems.

[smantscheff]

Good design tries to facilitate processes and activities and to allow for as much freedom and variety as possible without compromising ease of use. It does not harm anyone to record day/hour/minute values instead of only days, except maybe the developer who may have to introduce a business rule which says: One book may be only retrieved once per day and user. If such a rule exists it will most probably exists because of "technical" reasons, i.e. bad design.
And well designed systems of course allow for exceptions. The other are the ones which drive us crazy at check-in terminals, ATMs, ticket vending machines and bureaucracy officials telling us of "technical reasons" why something is not possible.

[urtrivedi]

Good design tries to facilitate processes and activities and to allow for as much freedom and variety as possible without compromising ease of use. It does not harm anyone to record day/hour/minute values instead of only days, except maybe the developer who may have to introduce a business rule which says: One book may be only retrieved once per day and user. If such a rule exists it will most probably exists because of "technical" reasons, i.e. bad design.
And well designed systems of course allow for exceptions. The other are the ones which drive us crazy at check-in terminals, ATMs, ticket vending machines and bureaucracy officials telling us of "technical reasons" why something is not possible.

I agree

[dyingatmidnight]

Its merely a school project so its going to be rather on the simply side. I think allowing a user to check out a book on the same day would be something I could overlook in this case. I do, however; have to show normalization, if I were to use the above suggestion can it be normalized to BCNF or 3NF as all my tables need to be normalized to one of those forms. Normalization continues to elude me, mostly because I find determining all the functional dependencies sort of hard.