Need help finding a way to query against 1,050 ids

Hey there! I need to know what the best way to query a database of 1,050 unique IDs. This is for a hand-held magnetic card reader to validate registered members. I have read on these forums that putting a database onto an arduino is a no-go however I have herd that it may be possible to read a file on an SD card and query against that. An example of the user data would look like S374374678.

Is this possible? What would need to be done? can anyone provide links to source code perhaps?

Yes, it's possible. Before discussing how to do that, though, consider what happens when a new member registers. How does the SD card get updated?

Where will the card reader be used? Will there be a computer nearby that the reader could communicate with? Could you, perhaps using a zigbee on the Arduino, communicate with one attached to a PC, where a real database lives, and ask it "Is Sxxxxxxxxxx a registered user in good standing?".

Searching though so many codes will be slow and you need to do this quickly in order for the door to open quickly. One way round this is to pre sort the codes into bins. A bin is a small section of the valid codes. You assign a code a bin based on a hashing algorithm. Then when a new code is to be searched you apply the same algorithm to that so you know what bin to search. This can make the search up to N times faster where N is the number of bins you have.

Well having a pc nearby would not be an option. This data would not be used to open a door. Its to see if a student number on a school ID is that of a resident or not. If the code is in the database then they are allowed in the building, if not then they need to be registered as a guest. As far as I know the code is S 040 XXX XXX where X is the number that changes. That may help sort data a bit, I dont know.

Instead of looking up in a table, you can do this:

If the digits are all numerical, each would take 4 bits. that means 4+4+4+4+4+4 = 24 bits per ID, or to simplify, an array of [255][255][255]. (adjust the numbers as necessary if you need to include alphanumeric ids)

If you use a file a file on an 16MB or larger SD card, which shouldn’t be hard to find, you can calculate the location in the file to read the data from rather than walking and comparing data to the whole file. There is a lot of wasted space for the swipe cards you don’t own, but lookup is a math calculation (offset) away and relatively instant.

For example, for card S 040 156 789 you can find if it is a guest or resident swipe by checking the byte at [15][67][89] in the single data file. (or offset 15255255+67*255+89)

You can simplify the space a lot too (supposing the cards are sequential) by creating files for each major number , such as 15.txt with the contents of all the 15-xx-xx based cards. This way, instead of having 1x16mb file, you have 255 files 65K (essentially an array of [255][255]). When the card is swiped, you open the 15.dat file and seek to position [67][89] (or 67*255 + 89) to read the 1 byte of data to determine what to do.

I use a byte in the above examples, and essentially you have 8 bit-flags you could set in that one byte of data on the file. You could divide all the sizes by 8 if you use 1 bit instead of a whole byte, but you have to first calculate the byte offset and pull the bit out of that byte.

… Anyway, I wanted to present an alternative to trying to search through data …

Use a FAT-SD library that supports subdirectories.

For IDs of the form S-040-AAB-BCC, format an SD card:


This ensures there’s no more than 100 elements in any directory; subdirectories can get really slow if you have many hundreds of files in them. Keeping filenames within 8.3 lengths is also helpful for lightweight FAT libraries. You could even open the file and append/log a realtime clock timestamp for entry, exit.

If you want to easily distinguish residents from guests, then make a \guest directory with the same structure, and only create files in that directory when guests register.

A filesystem can be a powerful database, especially when the total number of elements is in the low thousands.