Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
6  
500 rows is not "very long". In database terms, it is tiny. If the table contains original, non-generatable, business critical information, why on Earth isn't it version-controlled? Can't you at least store an SQL DDL version and auto-generate the sqlite image? –  Kilian Foth Jun 3 at 6:34
    
@KilianFoth, we take regular backups of the db file. but point taken. we can put the DDL Dump in version control. the thing is when someone has to update values, he has to skim through a large sql file full of INSERT statements. not very user-friendly. (user being the developers). also one might accidentally alter some keys and commit a broken DDL. but yes eventually it will be caught –  inquisitive Jun 3 at 6:47
    
Its tiny - export it to a text file, problem solved. There are better ways, but one that works is a good start –  mattnz Jun 3 at 7:32

1 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).

share|improve this answer
    
this is quite close to the XML solution i thought of. even better, you have an UI (the database). editing in-place in database will make sure of referential consistency. the only thing to take care of, is the script which dumps the database and one which builds it. –  inquisitive Jun 3 at 10:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.