[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [ProgSoc] NULL values in primary keys in MySQL



 Okay - I'm not being obtuse here.  I just need to understand the
 reasoning behind stuff before I become a convert.

On Sunday 18 June 2006 9:20 pm, John Elliot wrote:
 ] The point is that when I used the phrase 'client-side', I wasn't 
 ] referring to physical architecture. You use GUIDs so that you can create 

 Okay, yup, get that.  I think I got that before, but was probably using
 the concept of server side to encompass the fact that the db and
 the app proper would both live on the one box.  And/or that the db
 and the app stuff was authored by the same person.  There's no API's
 to be published, no external / dubious / etc clients that can interface
 with this system.

 ] The problem with 
 ] this is that you have to configure all of your entity relationships 
 ] while you are holding a database transaction open. This creates two 
 ] problems: 1) resource contention in the database, 2) data-access code 
 ] and business-logic code are necessarily conflated under this scheme.

 Ah, yes.  I'd pondered the implications of this stuff, but had been
 happy to mostly ignore it because of the low performance requirements
 of doing this, and the belief that the practical aspects of doing the
 data preparation work would be done via a single form (.php page if
 you prefer).  When that page is submitted, I'm happy to write-lock
 the table(s) involved, have some catches there for errors or timeouts,
 do the inserts, then unlock.  This stuff works very fast, and the
 performance costs involved with locking the whole table just aren't
 worth worrying about - they're that small.

 OTOH, I do realise that this isn't the way you'd do it in the real
 world, because it simply doesn't scale.  Pragmatism .v. theory,
 though, rears its head here again.  Make the app work just as
 well as it needs to - but no more so - as I think you or Myles were
 advocating, in a different thread, a while back.

 If I was intending to start programming for dollars, or even more
 often than a few late nights every couple of months, then I'd also
 find it more compelling to worry more about the scalability of my
 designs.  Normally I'm a perfectionist with things I'm learning, but
 I know if I adopt that attitude with this stuff, it'll never get done.

 ] ... allocatable (is that a word?), but they are a single column, they 
 ] are the same data type, they are the same data format, and they are 
 ] unique in a domain-wide space (i.e. if you stumble across a random guid, 
 ] then the guid will *imply* its type if you look in the database across 
 ] all PKs; whereas if you stumble across a random integer, it might occur 

 Hmm .. this concerns me.  How often do you [guys] come across a
 piece of data and not know where it came from, what it means, or
 what you should do with it?  Or rather, how often does a programme
 do this -- and do you really code in handlers for random bits of data?

 ] .. [entity] table. You can then encapsulate your database with 
 ] stored-procedures which encapsulate this implementation detail. 
 ] Though I doubt you're likely to care about this in your scenario.)

 I've made a policy decision to avoid stored procedures.  In no small
 part because the complexity simply isn't there, and of course the
 client and db are both on the same machine.  But yeah - the idea
 of a bit table mapping GUID's to INT's in an attempt to avoid putting
 GUID's in every table .. makes my nose bleed.


 ] I don't quite understand your agenda here. Why are you fighting it? It 
 ] strikes me that you're trying to be pragmatic. The thing is, 
 ] auto-incremented integer keys are *not* pragmatic. Natural keys are 
 ] *not* pragmatic. GUIDs epitomise pragmatism.

 Yeah yeah .. I think I'm getting that now.  I find the idea of
 'extra' data within my tables to be inelegant.  Data that I don't
 need, I mean, and that appears to duplicate the functionality of
 extant data that I do need.  Certainly I'm not worried about the
 disk that'll be taken up by the extra column(s) - it's likely
 immeasurably small.  And performance wouldn't be hit at all .. ?

 Actually - applying INDEXes to columns provides comparable
 performance to having them as primary keys, doesn't it?  Or have
 I misunderstood this bit too?

 ] Are you aware that storing the balance in the account is denormalising 
 ] your schema? Typically a summation of transactions would render the 
 ] balance. I doubt that's likely to create a performance problem in your 
 ] environment.

 Ha!  I hadn't thought of that .. but yeah, you're right of course.
 I was pretty chuffed a while back when I used normalisation to resolve
 my design problem of multiple users sharing the same pool of dosh,
 and was entertaining the idea of keeping a 'number of transactions'
 against each account.  But obviously that number could be determined
 from a count function where trans-from/trans-to matched the acc#.

 Two things I don't grok, here -- the performance .v. normalised schema
 tradeoff.  Dynamically pulling out everyone's balance by cycling thru
 all the transactions  -- this would be a major hit.  Secondly, was
 thinking that dynamically doing exactly that, whilst keeping a balance
 (or #-of-transactions count) and comparing the two results to make
 sure there'd been no corruption of the underlying data -- seemed like
 a sensible idea.  What's the rules of thumb for those two types of
 design compromises?


 ] A single individual will want two  accounts for some reason. 

 I've pondered this one .. and concluded they can GFT if they want
 this.  Or perhaps just write their own system.

 Primarily because it'd be confusing as all get out (for everyone
 concerned).  But also because there's not likely to be a compelling
 reason to do so -- there's no tax implications, account fees,
 benefits from min-monthly balances -- none of that kind of stuff that
 complicates conventional currency handling stuff.

 Absolute worst case, they simply generate a new account & member
 pairing, and handle them as separate entities -- which is what they
 would, in that hypothetical scenario, want to be doing anyway.

 And as much as you think it more natural that a single member  have
 more than 1 account, than two members wanting to share a single
 account -- the latter exists for a number of users right now, but the
 former hasn't come up.  Yes .. it's only anecdotal or empirical
 evidence, but for me it's compelling because it's the same user base
 that I'm targetting.

 ] So... let me get this straight. You say that a member's natural key is { 
 ] number, jba }. Then you promise that your natural keys are immutable. 
 ] Then you say that a jba mutation is possible. (Hint: I see a problem 
 ] with this logic... :P)

 Yeah yeah .. this bites.  I'm still pondering this.  I might just say
 accounts are locked and new ones created, balance transferred (etc)
 when JBA's are to be implemented, to get around this.  You talk
 about this at some length further on -- but I'll put off a decision
 about this aspect for the moment.

 But tell me -- what is the *effect* of changing the contents of
 a PK column in a record?  Given a single client talks to the DB,
 there's nothing sophisticated like temp tables with this design,
 what's actually the worst thing that can happen?  And would it
 be ameliorated if the record was deleted and then new ones (that
 just happen to have similar PK data) created immediately after?

 ] In the problem domain the *natural* (in a human sense) 'domain key' for 
 ] a member is their username. That is, a member identifies themself to the 

 I thought of username logins .. but the current trading scheme relies
 upon member numbers, and handling clashes would simply reintroduce
 arbitrary integers (probably) anyway.   I suspect the handling of
 time-related data here would also get messy - your name may change
 when you get married, say, but your 'number' doesn't.  You can get
 a new number, but the old one is locked in situ -- less confusing.


 ] p.s. I have two server racks full of semi-configured gear, a heavily 
 ] under-utilised 1.5 Mb DSL line, and zero time for sys-admin. If you pop 
 ] over to my place and help me set it up, I can host your application on 
 ] your preferred flavour of *nix on either sparc or i386 for free 
 ] (although I can't extend any guarantees RE: availability or continuity 
 ] of service).

 Ahhh .. the closest I've got to a job offer for two months!  ;)

 Is this the same gear that recently got W2K3'd, or other stuff?

 Jedd.

-
You are subscribed to the progsoc mailing list. To unsubscribe, send a
message containing "unsubscribe" to progsoc-request@xxxxxxxxxxxxxxxxxxx
If you are having trouble, ask owner-progsoc@xxxxxxxxxxxxxxxxxx for help.