Hello everybody,

I have developed a quite large database application with a lot of
lookup-fields. Briefcase support is going to be implemented in future
releases so I needed to take this into account while designing the
database. Now my customer has started to use this system in a real
environment and, as ususal, I've begun to think whether my current
database structure actually is the best possible one. (I always seem
to start questioning my own products as soon as the customer starts to
test them....) Anyway, this is how I have solved the lookup-links in
the current release:

Every lookup field consists of two key-fields: ID and DISTRKEY. The
ID-number is generated by a generator. The DISTRKEY field contains the
distribution ID with 0 being the "main" database, 1 the first
briefcase distribution, 2 the second, etc... In this way violating
lookup keys can be avoided when all the briefcase databases are merged
into the main one.

However, as every lookup table has a unique VARCHAR-field I was
thinking of using this as the lookup key instead. This would of course
require the use of foreign keys so that if the field's value in the
lookup table is changed, all links to this particular record are also
updated. (And so far foreign keys hasn't giving me anything but weird
key violations :( )

My question is: which one of these approaches would you use? Or would
you solve the problem in a completely different way? If I choose to
redesign the database structure I should do it as soon as possible
when there still is little data in the database.

Thanks for you help, and I hope you understand my problem and my
descriptions.

-Petter-