• (cs) in reply to DWalker59
    DWalker59:
    ... but perhaps the commenters weren't actually saying that, and if so, I apologize.  Still, Jeff hasd some good things to say.


    What I understood most people to be saying is that once they need to go international, the table would no longer be a US States, but instead be changed to a States/Province/whateverYourCountryCallsThoseThingsThatAreBiggerThanCitiesButSmallerThanTheCountryItself table.
  • Jimmy (unregistered) in reply to DrJames

    Probably Java hence the case of String and int, also C# UCASE method naming convention sucks(as does the language [:D]

     

  • (cs) in reply to Some guy
    SomeGuy:

    rogthefrog:

    Primary keys should be meaningless and numeric, and their assignment should have NOTHING to do with the semantics of the data. I.e. a higher id should never be interpreted as "the data refer to a later event", as something could be backfilled, etc. That's what date/time/timestamp fields are for. Primary keys should be numeric because indexing and searching can be a lot more efficient that way.

    ....

    No wonder sites like this exist with what some folks post in response to WTFs.

    You will not find a single expert in the field who will agree with you. Your assertion is based on an incorrect understanding of relational databases and goes against over fourty years of "lessons learned" in data modeling.

    I can tell that you have "self-taught" yourself databases. One thing you skipped over is the fundamentals. Go pick up a book on databases. I recommend Date's INTRODUCTION TO DATABASE SYSTEMS. You can find used copies for $5.

    Once you get a grasp of the Relational Model, you will probably realize how true your latter statement is, though not really in the way you intended it to be.



    I just received my copy of the book you mentioned.  However, after a cursory skimming, I'm unable to find anything that mentions that meaningless primary keys are contrary to the Relational Model and/or should be avoided.  While I fully intend to read the book in its entirety, as it obviously contains a wealth of useful information that I should know, I was wondering if you could point me to a specific section or chapter of the book that supports your point, in the interest of expediency.
  • (cs) in reply to Jeff S
    Jeff S:

     My PK of Abbreviation ensures that only 1 entry for MA exists in the table; your PK of StateID does not do this.  My PK gives the code "MA" a unique meaning, a definition, it the clearly defined label for an entity.  Your StateID is meaningless.  Technically, a database cannot even be considered normalized if you have nothing but meaningless keys on your tables!

    "Hold on!" you say; for data integrity and to guard against duplicates you can always just add a unqiue constraint on the Abbreviation column as well.  Definitely a good idea .. and now your meaningless, random StateID column has *no purpose at all* in your table, it is just there for that warm and fuzzy "I have an ID column like MS Access taught me I should!" feeling ....

    Do you have a random MonthID column on a table of Months?  or a random NumberID column on a table of numbers?  YearID on a table of years?  (it will be interesting to read the responses regarding the notion of having tables like these in your db's -- a great way to get a feel for how much database skill and experience is out there)

    It's simple: if a natural key arises in your data, use it.  If nothing short or simple naturally can be used, go ahead and use a surrogate key.  Heck, even identities aren't always surrogate in my opinion -- if your business rules dictate that each employee should be assigned a unique numeric ID, and that this code should be assigned at the point of data entry into the system, by all means use an identity !  I think assigning short codes to entities is a great idea in general.  But it is NOT a good idea to ignore preexisting short codes or ways to identify an entity if they exist already and then to assign your own random ones instead. 

    If you want to blindly ignore the natural ways to validate and constrain and relate your data, and use identity columns in all of your tables, go ahead, but guys like me are going to yell at you when you (or the poor guy who inherits your work) come to us for help to clean things up.



    It is interesting to me to read this conversation, and see a guy who's self-taught in DB desing (Chris) arguing with a guy who I am guessing has had formal DB education (Jeff), and then finding myself disagreeing with the the guy who has the formal education...

    I say that because I've been what I would call "classically trained' in DB architecture, BCNF, Relational Calculus, Relational Algebra,  etc... And it's interesting to me to see people forget that there are different levels of DB modeling at work when we're designing a DB, namely: Conceptual, Logical, and Physical.

    At the Conceptual and Logical levels, Jeff is right. The primary key has to be meaningful, well-defined. There is no way to normalize a database without a meaningful Primary key. But Physical DB modeling does not have to adhere to that standard, and indeed it shouldn't.

    History has taught us (at least those of us who work on real systems) that using real Business entitites as primary keys isn't practical. Meaningful data is not static, and it does change, whether it's a primary key or not. 

    Hence surrogate keys used in conjunction with unique contraints.

    I don't know why people would argue against the use of a Surrogate key. It's a perfectly legitimate way to model a database in a physical environment, abstracting from the logical model. Experience has shown a lot of us that it's actually a very effective way to maintain a database and handle FK dependancies. Nothing sucks more than having a meaningful primary key as a FK in many related tables, and then having that key change... The surrogate key gives us the flexibility in design, development, and maintenance, to avoid the hairy problems of implementing a strict logical model.

    I think the thing a lot of folks misinterpret is saying an ID is a PK. It's not a PK. It's a surrogate key. The real PK is next door, in the next column, with a unique constraint on it. And to the business user, that unique value really is the PK - that is what makes that row unique - be it a special customer ID, or a state abbreviation, or whatever.

    Auto-incremented Surrogate keys are a tool for Physically representing a logical model, and shouldn't be perceived as PK's. I think if Jeff were to see them that way, he might understand the key element in all of this: when using a Surrogate key in a Physical modeling of a DB, the logical modeling hasn't really changed...



  • (cs) in reply to pjabbott
    pjabbott:

    Why re-use the variable "r" for both a recordset and an exception?

    Object r = null;
    r = executeQuery("select * from " + table + " where id = " + i);
    if (r == null) {
         return new ClassNotFoundException;
    }
    else {
         return r;
    }

    Heck, why not do this?  Only declaring one variable saves memory, right? [;)]

    Object r = null;
    r = "select * from " + table + " where id = " + i;
    r = executeQuery(r)
    if (r == null) r = new ClassNotFoundException;
    return r;

    I assume this is exactly the kind of programming WTF that prompted Java inventor to enforce strong typing :)

    Apart from jokes, I like this code a lot. It reminds me of my first year in CS when, programming in C, all variables were "int", all pointers where "char*" and all loops were "while".

  • David Schwartz (unregistered) in reply to Manni
    Manni:
    6. I just realized this: how would you know the ID of the record you want to access but not have read in that record already? If you know the ID, you've already looked at the record obviously, unless he has this in a loop that tries ID's sequentially, hoping to get a match.

    You know the ID of the record because the ID was stored somewhere else, say in another record.

Leave a comment on “Exception Disfunction”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article