news <[EMAIL PROTECTED]> wrote on 05/06/2005 06:08:18 PM: > hi, > When designing data it is common to have lookup tables such > animal_type : dog=1, cat=2,bird=3 etc > > And then in other tables to refer to animals by their number 1, 2 > or 3. This is memory and > presumably speed efficient. Howver not much fun for humans who are > "reading/debugging" the data. > > Alternatively it's possible to have a lookup table containing animal > types as text strings "cat" > "dog", "bird" and actually then use the actual "names" where ever > required in other tables. > > If table size & speed are not top priority is there anything wrong > with using 2nd method? > > Views/opinions please > > > -- > zzapper > vim -c ":%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?" > http://www.rayninfo.co.uk/tips/ vim, zsh & success tips > >
No, there is nothing wrong with using the actual value in your animal_type field and not a number (usually an INTEGER) that represents that value. As you said, your data tables may be larger (due to using duplicate strings instead of duplicate integers) and the comparisons will be slower (string comparisons are noticeably slower than integer comparisons). However, if those are trade-offs you are willing to make, go ahead with your design changes. As already mentioned, make sure you protect yourself against spelling errors by your users or your records may no longer line up. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
