Re: What does everyone else do for graphically displaying data?

mdR wrote:
anyway, one good example i've traced worked with only one table and
even that got kind of complicated managing one key id between db
objects and various gui objects. i guess there was ~a lot~ happening
behind the curtain that i've got to learn ;) iyo-does it ever get
easy?

Easier, yes, easy, no.

The matter of keys is complicated by a tendency for some to overuse integer surrogate keys, without identifying the so-called "natural" key that at least ideally identifies the row based on the attributes represented by the columns.

The "key id" I take you to mean is an integer SEQUENCE surrogate key maintained by the database to help determine row uniqueness, yes?

This surrogate key should be managed by the database - your object should use it, if at all, only as a very low-level handle for a database row, not for the object' own purposes. Sequenced surrogate keys exist only to make the database simpler, and are not (typically) part of the data model. (We'll ignore certain exceptions.) One should instead understand a row in the database to be determined by a "natural" column or set of columns, and usually there should be a UNIQUE constraint on that set of columns. [1]

Think of the object model - these attributes would be the ones that determine equals() in your object model.

The integer surrogate key should be determined, if you even need it at all, which you really don't, actually, except sometimes to underpin DAO layer machinery, by looking it up from the database, and in no wise should the middleware attempt to set or change it.

So you shouldn't any more have trouble "managing" (translation: "mucking with") the key ids.

[1]
"But so-called 'natural' keys can change in real life! People change SSNs all the time!" No, they don't do it all the time. They do it rarely. And when they do, it'll screw up your database schema. Then you'll fix it, manually. At which point their new SSN will uniquely identify them, so the key will still work as a key.

Don't get me started on data warehouse schemas. They're part of what make me so cynical about transaction db schemas.

--
Lew
.

Relevant Pages

  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... a surrogate key should support the primary key. ... But SQL does not have a pointer data type or the ... > being told to design a database. ...
    (comp.databases.theory)
  • Re: candidate keys in abstract parent relations
    ... strongly that a lot of the time the database designer does not consider the ... With the primary key change, I was more thinking of all the locking and page ... Whether they send back a small surrogate key or an entire ... I'd say the DBMS would be doing ...
    (comp.databases.theory)
  • Re: dbdebunk Quote of Week comment
    ... scalable mechanism for the generation of surrogate key values. ... has no place in a relational database, ... include a constraint that will ensure uniqueness of the real key. ... result in a constraint violation, and no rows inserted into the table. ...
    (comp.databases.theory)
  • Re: dbdebunk Quote of Week comment
    ... > scalable mechanism for the generation of surrogate key values. ... > has no place in a relational database, ... > include a constraint that will ensure uniqueness of the real key. ... > result in a constraint violation, and no rows inserted into the table. ...
    (comp.databases.theory)
  • Re: candidate keys in abstract parent relations
    ... > interface into the applications using the database. ... > which item in the drop down is selected, if we hadn't used a surrogate key ... > from a business stand point is there to store data. ... business data into a database until the argument for doing so becomes ...
    (comp.databases.theory)