Hello All,
I'm currently working on a database that has started out as
predominately English based, but may end up Internationalised. As such
at this point in time, certain relationships and tables are using what
we refer to as Natural Keys. eg. A CostType has a name that is a
varchar(32), and because this must be unique (being Primary Key and all)
it's often referenced as a foreign key from other tables. Part of the
reason is that when you do a select you can also be lazy and don't need
to do a join. Also, the CostType is used in a drop down in the Java UI,
and people can add/edit existing cost types. At the time we considered
using sequence generated Ids for these kinds of things a little
overkill.
However, my concern arises when we go International, suddenly we're not
necessarily storing normal character sets, but in fact may be using any
of the International character sets. I'm just concerned about the
uniqueness of the values and whether or not what we've done is
particularily wise.
My inital instinct is to go back to having a sequence generated value as
the primary key. This helps to divorce the primary key from the actual
data values themselves. However, this now means that view will do joins
to build user-readable values etc....
What are peoples opinions on this matter? Has anybody got any words of
wisdom or horror stories about paths they've taken?
The choices are simple,
1. Continue to use Natural keys where appropriate
2. Back up now before it's to late and use Sequence generated IDs
Thank You.
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.