How to store and synchronize a big list of strings

Question

I have a large database table in SQLExpress on Windows, with a particular field of interest 'code'.

I have an Apache web server with MySQL on Linux. The web application on the Linux box needs access to the list of all codes. The only thing it will use the list for is checking for the existence of a given code.

Having the Linux server call out to the Windows server is impractical as the Windows server is behind a NAT'ed office internet connection, and it may not always be accessible. I have set it so the Windows server will push the list of codes to the web server by means of a simple HTTP POST request. However, at this point I have not implemented the storage of the codes on the Linux box.

Should I store them in a MySQL table with a single field 'code'? Then I get fast indexed lookups O(1), however I think synchronization will be an issue - given an updated list of codes, pushed from the Windows box, how would I optimally synchronize the list with the database? TRUNCATE, followed by INSERT?

Should I instead store them in a flat file? Then I have O(n) look up time rather than O(1). Additionally an extra constant-time overhead too, as I will be processing the file in Ruby. However, synchronization is easy - simply replace the file.

UPDATE

Another approach: Abuse the filesystem: Given a code, say "ABCDEF", generate a path like "A/AB/ABC/ABCDEF", and 'touch' a file there. I think I like this approach the most. Is that is a bad idea?

Answer 1

First, if your web app has persistence, just add the codes to the current persistence store. (simpler)

But, it realy depends on the size of your code list. Since you are able to HTTP POST the codes, it does not seem to be gigabytes large when saved to a flat file.

Thus, if your flat file goes into some MBs (or some thousands of codes), go for MySQL. Anything less than say 1MB in my mind would be an overkill to use a database server.

So, if you file is small: make it flat.