Re-evaluating my table design 1

[KornGeek]

I am working on a library application using Access 2000. I have a table of items that have been checked out (tblOutItems). The fields are ItemID, BorrowerID, DueDate, and Qty (the names in bold are combined to form the primary key). The ID fields are text, the DueDate field is (you guessed it) a date field, and Qty is a number.

The same ItemID can be checked out to multiple users, and one user can have multiple items. Also, one user can have the same ItemID due on multiple days. (Imagine if you checked out a book on Monday, and then another copy on Tuesday. It would have the same BorrowerID and ItemID, but a different DueDate.)

The problem comes in because I have been asked to allow for Items to be checked out indefinitely. In a previous incarnation of this program, I did this by allowing a null due date under a radically different table design. With the due date now part of the key, I can no longer do this.

At this point, I am considering all options, including (but by no means limited to), requiring a due date for all Items (which will cause some problems), using some sort of token value (but I don't know what I could use), or redesigning this table structure.

Please offer whatever suggestions or comments you can. Time is getting short, so I need to decide on a course of action quickly.

Thanks for you help and patience.

 

[RiverGuy]

If an Item is checkout out indefinately, it would, in effect, become the property of the borrower? I would just make a Yes/No field. Indefinate would be yes.

To view your books that are due, only select those with a No.

 

[randysmid]

Hi,
I also had a problem similar to this, and I ended up setting a date far into the future (e.g., 12/31/2030). RiverGuy's idea is also very good, but you will need to modify the structure of the table to do this. Of course, in some organizations a person may need to go through the "master" DBA. HTH,
Randy Smith
California Teachers Association

 

[KornGeek]

Indefinite checkouts do essentially become the borrower's property. The concept is that the company still owns the item, but the borrower will have posession of it for an extended period of time (such as while they are still employed). Eventually, it will be returned, but there is no fixed pre-determined date that it is due back.

I am considering your suggestion. This (as well as any rework of the table structure) would unfortunately cause me to rework several of my forms, reports, queries, and code. I would still need to store some sort of date in the field (because it is part of the key), all of my groupings (to show items that are overdue, due today, etc.) would need to exclude these, and I would have to screen out the due date on my forms and reports.

Another possibility that I'm current examining is to convert the field to text. I could then use a dummy token (possibly "&quot to display the indefinite due date. This would still require changes to my queries and code, but I think it might reduce the changes to my form and report layouts.

Thank you for providing another possiblity to consider. I'll let you know which path I end up taking with this.

 

[randysmid]

Hi,
Changing a date field to text is not recommended. It then becomes very difficult to convert those text boxes back into valid dates in all of your forms, queries and reports. With using 12/31/2030, it is very easy to create a report showing all items that have been lent indefinitely (query - criteria field for due date = 12/31/2030). Make sense? It is real easy to implement, especially if time is an issue. HTH,
Randy Smith
California Teachers Association

 

[RiverGuy]

Here's another thing. If someone checkout out 3 identical items, would they be due on the same day, no matter what? What I mean is, would it always be 1 record with a quantity of 3?

If so, maybe you could switch your key. Use DateCheckedOut, BorrowerID, and ItemID. You could allow nulls for the DueDate that way.

 

[KornGeek]

Randy,
My first idea was to simply use a due date far enough in the future that it wouldn't matter. Unfortunately, that idea got shot down. I think the answer would have been different if the decision makers actually had to code this, but alas, that's my burden. I might be able to pull this off if I completely mask the value from the users.

RiverGuy,
If someone checks out 3 identical items, they may or may not have the same due date. Two of them could be due back on 3/12/03, and the third could be due back on 3/18/03 (just picking arbitrary dates for this example). Or each could have it's own due date. Or one (or more) may be checked out indefinitely, so it would have no due date.

I appreciate your insight into this matter. I think I'll back up my database (just in case), and try using Randy's idea. With any of the ideas that we've tossed around, I would have to do some date checking and mask the dates. Because his idea doesn't involve changing the database structure, it's attractive to try it first. I'll let you guys know how this worked out.

Thank you for your time.

 

[KornGeek]

I got this working using Randy's idea. I mostly only needed to update some queries and a little bit of code. I used a lot of Nz and IIF function calls, but I got it working in only a couple of hours and without redesigning my tables. I used a token date of 12/31/9999 (the highest date Access allows). My customers might conceivably be using this 30 years from now (although I doubt it, I don't want to force them to limit it). If they're still using it 7000 years from now, at least I won't have to take the tech support calls.

The great thing about using a date far in the future as the token value is that all of my functions that compute what things are overdue, or how many days late an item is all still work. The best thing about using a token value is that I didn't need to do (yet another) redesign of my base tables.

Thank you both for your time and effort on this issue.