Storing a looong lookup table

Question

Background

The product i am working on has a very long lookup-table. the table contains static data and cannot be auto generated. there are about 500 rows and 10 columns. columns have mostly integers and strings. to complicate the matters, there are actually two such tables. every row in table-1 maps to zero-or-more rows in table-2.

we use an SQLite database with two tables. the product installer places the SQLite file in the installation directory. the application is written in dot-net and we use ADO to load the data once on startup.

now,

1. the lookup table grows. in each release a month, we add about 10 new entries
2. existing entries are adjusted. every release we fine tune existing entries.

The problem

a team of (10) developers work on the lookup table. Code goes in the SVN, but the little devil the SQLite does not. this prevents multiple developers to work on it. we do take regular backups of the file, but proper versioning is not possible. we never know who did the breaking change. the worse thing is we dont know if there is any change at all. diff'ing databases is tedious if not impossible.

the tables are expected to grow quite large in years to come and we would need developers to work in parallel on it. the data is business critical. we need to be able to audit changes made to it.

Question

What would be a solution for the problems outlines above? one idea was to transform the whole thing to XML and treat it like just another source file. that way SVN can do the versioning and we can work in parallel. but the data shows relational behavior. with XML we loose the unique and foreign-key constraints. also we cant query it with sql like ease.

any help here will be appreciated.

Answer 1

Ideally, you develop a small program to do the editing, so you can edit the file (and maybe even do the svn update and commit as part of the program). That may be more work then you have time for. I have never had that time.

Instead, I had people edit in the database in place, and built a small script that they ran (maybe as an svn trigger, maybe not, I never tried) that loaded/dumped the database to one or more files. The files then become the "master". Any changes to the database are lost unless dumped and committed. It's a slight culture change, but in my experience, after people lose their work once or twice, they get it. Ideally, everyone has their own copy of the database built from the files they checked out, and you no longer have a development database (you do have a nightly build database, of course, etc.), so you don't have to worry about picking up others changes when you commit, and svn handles the parallelism. Make sure you dump your database in a reasonable order of rows so you can easily see what was changed.

Good luck. I wish there was a better answer (there is, of course, but it costs money).