[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.