tables or too many finds

[crazybake]

I need some theory help. Not sure if it is bad structure or finds. sorry for the long post

I have a solution for checking out equipment to students - with tables; Students, Equipment, Transactions, Transaction Lineitems.

This is where I may have gone wrong.... There are three types of transactions (I think): reserve, checkout, checkin. My solution only creates one transaction and one lineitem with a status field; reserve, open, complete. As the student moves through the process, the status only changes... is this correct? Or should there be seperate transactions for each? Should the status flag be placed on the equipment, not the transaction lineitem?....so confused.

I ask this because of the way the solution works currently.

1.) User scans or enters item number. A script performs a find request using "set Field x to item number" and "set field y to status open"

2.) If the results are "0", it runs a second script that performs a find using "set Field x to item number" and "set field y to status reserved" and "set field" for today's date to see if the item is reserved for that day

3.) If that results in "0", then it will allow a student to checkout the equipment....

The seconday issue is that when the student is "allowed" to checkout the equipment scripts are run to:

1.) make sure that their account isn't blocked (through a go to related record and see if block is set to "1")

2.) make sure that the equipment can be checked out to that individual (i.e. they are a first year student and the equipment can only be checked by a second year) It does this through a go to related record for equipment to see if "1st year" is checked.

This seems like way too many finds and is not elegant at all. I may be making this way too complicated.... because, the last issue is reserving.

Students are allowed to reserve items. A scirpt will run to see if the item is reserved for a particular day through a find of an item number and date. If the result is "0", it also makes sure that there is no block on the account, and that the equipment is allowed to be reserved by them.

Any help with relationships and on theory would be greatly appreciated.

[imoree]

I would agree that this is a lot of search(s) or finds.

But the ERD is Students checking out equipment

Students, Equipment, Transactions, Transaction Lineitems.

So 1 student can rent many pieces of equipment

That means there can be many transactions

but really it is 1 to many COrrect?

StudentID - Primary Key --- Equipment StudentID - Foreign key

The reserve, checkout, checkin. are just 1 field in the Equipment Table

You can use this as a value list. ( Lets say Options)

Based on that you have what you need.

secondly:

you need to see if the student is "ALLOWED" first, before continuing to the First Script.

If they are not allowed , then you don't even need to scan the item, you know.

-i

[crazybake]

That does make sense... I guess I was thinking it was easier to base transactions on the staus of an asset. On a side note... do you think it is important to have a seperate transaction for checking out and checking in? Or is simply having an open/closed/reserved flag on a single record? If this is the case, I would assume I would key this in assets as a flag.... am I thinking correctly?

[imoree]

That does make sense... I guess I was thinking it was easier to base transactions on the staus of an asset. On a side note... do you think it is important to have a seperate transaction for checking out and checking in? Or is simply having an open/closed/reserved flag on a single record? If this is the case, I would assume I would key this in assets as a flag.... am I thinking correctly?

Think Boolean and based on true / false you can make your table open/closed/reserved flag on a single record !

Yes this would be in assets, you an call this FLAG as well.

However think

case (Flag = open; 1; Flag = closed ; 2; 3 )

hope that helps, but what do i know.

i am still learning mesefl! :)