Database question.....

[Torsten Marek]

John D. schrieb:
> I have a small (fewer than a few thousand records) database with the
> following properties:
> 
> Each record has four fields: A, B, C, D.
> Fields A and B are unique:
>         there is one B for every A and one A for every B.
> Fields C and D are not unique.
> Sometimes I need to use A as the key, other times B is the key:
>         I use A to return B, C, D and also B to return A, C, D.
> The items may all be represented as short ASCII strings.
> 
> Currently I am storing this in a 100KB file and converting this to two
> dictionaries. I am reading and writing the entire file to update single
> records, which is inefficient. I am thinking about using anydbm, but then
> how do I do the two-way dictionary lookup? Would I have to store the whole
> database twice? What if I had ten items, would I have to store it ten
> times? Is this what a relational database allows?
> 
First of all, why do you convert it to two dictionaries? As long as I am 
not missing something, you only should need one.
Let's say you have a tuple of tuples with your values and a dictionary:

a = {}
b=(("a", "b", "test1", "test2"), ("c", "d", "test3", "test4"))
for i in b:
	a[i[0]] = i
	a[i[1]] = i
del b
print a["a"]
print a["d"]
Ok, now there was a problem if b[1][1] would be "a" instead of "d", so 
it might be a solution to prefix the dict keys with some other character 
like "_" and "-" which has to be added for every lookup.
Now to the file:
If you are going to save an additional value P for position in your 
dictionary, you could jump to this record's position in the file, but 
you might need fixed record length.
If you use a SQL database, you should would have one table and mark the 
columns A and B as unique.
Lookup would be (given the upper example values from b)
 > SELECT A,C,D FROM table WHERE b = "d"
gives you ("c", "test3", "test4")
 > SELECT A,C,D FROM table WHERE a = "c"
gives you ("d", "test3", "test4")

and so on.
Further, you would not be able to insert duplicate keys because the 
database does not allow this. MySQL has some interesting additions to 
SQL in that case, there is REPLACE INTO... (which should be handled with 
care because it replaces (!) the values) and INSERT...ON DUPLICATE KEY 
UPDATE... which is IMHO a lesser killer feature, but only available in 
4.1, which is still alpha.

greetings Torsten