Tom Austin wrote:
> I think I am grasping the concept.  If you need a 'pick list' you should 
> have a table with the picklist values.  Storing an ID or piece of text 
> doesn't matter as long as you have pk/fk set up correctly to prevent 
> data corruption.

Exactly. And it gives you a hook to expand on in the future. You have a 
place now to talk about colors divorced from where you reference them.

> Thanks for all your input.  You have helped to confirm that my gut 
> feelings on how to set up a DB are mostly in the right directions.

No problem. It helped me clarify how to word explanations too.

> 2 big points I take from this:
> 
> Be willing to explore better PK/FK uses.
> I have always known that a PK doesn't have to be a number, but it always 
> felt foreign to use text or some other number as they don't feel as 
> 'precise' as an integer.  I guess I've run into too many cases of trying 
> to match floats or case sensitive text to let it feel natural.

Yeah, exactly. You still have to be careful.

The other thing is, if upper/lower case makes a difference, or you're using 
a float, those are probably not things distinct enough for a real-world 
"unique identifier" anyway.  If you have trouble due to possible confusions 
with representation or precision, it's likely not a unique attribute of the 
object anyway.  I.e., if upper/lower case can be a confusion because it's 
(say) a human's name, well the name isn't a unique identifier anyway. If 
you're measuring the height of something and you don't know the precision to 
which you have to round it, chances are it's not a unique height anyway.

To use something like an email address, you have to decide how you're going 
to normalize it. Even tho RFC822 allows case-specific email addresses, in 
practice everybody lower-cases it. If you're talking about GPS coordinates 
and you don't have a significant minimum distance between objects, the GPS 
coordinates aren't going to be unique anyway.  E.g., telephone poles? Sure, 
the diameter of a phone pole is orders of magnitude smaller than the 
distance between them.  GPS locations of mail boxes? Probably not what you'd 
call unique.

And using a third-party identifier works well too. House addresses, zip 
codes, airport codes, phone numbers (plus timestamp of course), etc. If you 
can push the authoritative decision onto some other public organization, 
you're in good shape.

> Design the DB purely from 'what is out there'.  Don't take use into 
> account (or at least minimize it).

Yes. This is one of the really important ones that the self-educated guys 
tend to mess up.

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message