We take id Software's classic on the road, literally, to see how it stacks up on the Nintendo Switch.
DOOM. Nintendo Switch. Review.
An in-depth behind-the-scenes look at the game. And bass fishing.
How Ubisoft Bottled Beauty and Batshit Crazy for Far Cry 5
Join us on a chronological journey as we go through some of the highlights from three decades of Creative Assembly, the studio responsible for the brilliant Total War.
Celebrating 30 Years of Creative Assembly
Recently we had the chance to sit down with the head of Microsoft’s indie game service ID@Xbox, Chris Charla, to discuss all things Xbox, indie, and the future of the platform.
Talking Indies and Xbox One X with ID@Xbox Director Chris Charla
Moderately Advanced SQL Problem
Hogfather
Cairns, Queensland
5667 posts
Platform: MS SQL 2005 (2008). The product is targetted at small enterprise so the solution needs to work with Express editions.

I reckon you guys may have encountered this in the past, it seems like the sort of problem that would come up commonly.

We have a Project table that has a unique Number column. dbo.Project.Number is basically a custom identity column - it increments via a scalar function on insert, by doing a max(Number) + 1, with some custom code for them to arbitrarily set the minimum Next Number. This has worked well, but the client has of course shifted the goal posts slightly.

We now need to also allow the insertion of ad-hoc numbers, without affecting the "next" number unless there is a clash. For example, if we have the following existing project numbers:

1, 2, 3, 4, 5, 6, 10, 11, 12

The revised NEXT Number function will return 7, then 8, then 9, then 13, 14, ... as project numbers are auto-generated. We have a 3-tier framework, so I can insert a quick and dirty lookup seeking holes in the series manually but that's very ugly and won't be scalable. Other options I've found tend to rely on a discrete table of valid values, but our valid range is 0-2^32 basically, as the client needs to be able to set whatever Next Number they wish.

I guess I could get closer to the database by f*****g around with the CLR, but I -really- don't want to do that. DBA's heads catch fire when you mention it in passing ;)
08:48am 31/03/10 Permalink
system
Internet
--
08:48am 31/03/10 Permalink
Hogfather
Cairns, Queensland
5668 posts
Hmm google seems to have saved me. Still a fun problem.
08:56am 31/03/10 Permalink
Dazhel
Gold Coast, Queensland
1269 posts
it's an Islands and Gaps problem, I've heard Itzik Ben Gan talk about the problem in a class once so I always google for his name when I hit these ones :)
08:58am 31/03/10 Permalink
scuzzy
Brisbane, Queensland
13818 posts
Oh clients, what dumb changes won't they request.
08:59am 31/03/10 Permalink
Spook
Brisbane, Queensland
28564 posts
reckon, whats with clients!?

my job would be so much easier without them
09:02am 31/03/10 Permalink
Hogfather
Cairns, Queensland
5669 posts
it's an Islands and Gaps problem

Hmm nice one - found a lotta s*** to read up on now. I must have been asleep (or playing EverQuest) during that lecture hahahah ... :(
09:03am 31/03/10 Permalink
Thundercracker
Brisbane, Queensland
2412 posts
As a general approach I would probably make a table that contains all the reserved values that you want to skip. This is off the top of my head SQL, untested and probably quite incorrect so be warned. I think the trickiest part will be the lock, as you don't want this being run at the same time by 2 people

Then I would:

* Apply some kind of mutex lock so this can't be run concurrently

declare @currentmax int

if ((select count(*) from project = 0)
begin
set@currentMax = 1 // or whatever your starting number is
end
else
begin
set @currentMax = (select max(id) from project) + 1
end

// increment till you find the next
while ( (select count(*) from reservedProjectIds where reservedId = @currentMax) > 0)
begin
set @currentMax = @currentMax + 1
end

insert into project (id, blah, blah2) values (@currentMax, blahValue, blah2value)

* Release lock
09:09am 31/03/10 Permalink
Hogfather
Cairns, Queensland
5670 posts
As a general approach I would probably make a table that contains all the reserved values that you want to skip. This is off the top of my head SQL, untested and probably quite incorrect so be warned. I think the trickiest part will be the lock, as you don't want this being run at the same time by 2 people

Then I would:
-- snip code --

Yeh that's what a lot of people recommended via the googles, but the range of legal values in this case is f***en ridiculous. Its also a wee bit inefficient as you could compare a couple million row values before finding the next open number - every single time.

For concurrency we just do some s*** in the middle tier where the number is being generated, or rley on SQL server's concurrency locking.

http://www.mssqltips.com/tip.asp?tip=1191 looks to be the goods, nice and elegant using simple nested selects that should benefit from indexing. There's (thanks Dazhel!) an MSDN article with a description of the Islands and Gaps problem with some load-tested solutions here http://msdn.microsoft.com/en-us/library/aa175780%28SQL.80%29.aspx
09:14am 31/03/10 Permalink
hast
UK
1164 posts
You can just have a garbage table where you put ids after you have deleted them. Should do less work scanning tables but it will do extra writes on deletes/inserts + it is a bit of a pain to keep it sync. You need to remember to do an insert into the garbage table for every delete
09:22am 31/03/10 Permalink
Hogfather
Cairns, Queensland
5673 posts
That would work hast, but its not a true identity column, and the holes in the series don't necessarily arise from deletion. There's some custom mojo that is run when the number is generated - and no doubt it will get worse... bloody clients ...
09:31am 31/03/10 Permalink
hast
UK
1165 posts
yer.. if its an identity column you can get holes from failed inserts :( but if you keep the last number in another table and lock around that then you won't have that problem. if you want guaranteed no holes then you need to take a fat arse look that will only allow one insert at a time which kinda sucks.
09:35am 31/03/10 Permalink
hast
UK
1166 posts

nice and elegant using simple nested selects that should benefit from indexing. There's (thanks Dazhel!) an MSDN article with a description of the Islands and Gaps problem with some load-tested solutions here http://msdn.microsoft.com/en-us/library/aa175780%28SQL.80%29.aspx


honestly this article is disturbing. looking at it naively i can't understand how you can find a hole faster than just scanning through the table in order of id and looking for the holes. and as far as i can see none of the solutions ACTUALLY does that. the 'row' based solution scans through the ids iteratively but then indexes into the table so it is like O(nlogn) when you should be able to do it O(n) and who knows wtf the set based solutions are doing.
09:42am 31/03/10 Permalink
Raven
Melbourne, Victoria
4230 posts
This syntax won't work exactly but tweak it a bit and you'll get there:

select index from (select index, row_number() as rowNumber from myTable order by index) where index <> rowNumber

Use temporary tables if you need to.
09:55am 31/03/10 Permalink
tequila
Brisbane, Queensland
6470 posts
just make it a unique field, don't rely upon it for your ID field and check for errors upon input
you're just falling back to basic sql if it fails to insert, it would be quicker than checking if you can insert it, before you insert it

then just make the errors pretty
10:02am 31/03/10 Permalink
Thundercracker
Brisbane, Queensland
2413 posts
Ah I slightly misunderstood the problem w.r.t. filling in gaps.

I was also thinking along the same lines as teq, ie using a proper id and just having that number as another column that you can control at the software level.
10:07am 31/03/10 Permalink
Hogfather
Cairns, Queensland
5674 posts
Yeh its not as simple as adding a unique constraint and error handling, or there wouldn't be a athread :)

The auto-generation needs to be robust and scalable, think its sorted anyway.
10:10am 31/03/10 Permalink
Plasma
1013 posts
Perhaps keep track of the in use project IDs in string form, like:

1-500,502-1000,1002,1004,1006-20000,....

Write code that can generate this string by scanning the table.

Write code that can interpret this string and quickly notice the 'free slot' at position 501, etc.

The other important bit I think is to make sure that:
1) Its cheap to generate this string for subsequent runs (when using a new project id, simply update this string instead of re-scanning the table)
2) Be sure its transaction/thread safe

Its a horrible solution but that may work.

PS: You should never use identity columns as business identifiers; as these problems just start to happen. They may want to soon introduce department codes or other things to the identifier, and you're back at this problem again.

You should make the 'Id' column auto increment and unique (let the database manage it), and other tables refer to this Id column.

Add a new column 'ProjectId' that matches the project identifier the business wants - or - stop using Project Identifiers and use the Id the database provides when referring to projects.
10:14am 31/03/10 Permalink
trog
AGN Admin
Brisbane, Queensland
30040 posts
PS: You should never use identity columns as business identifiers; as these problems just start to happen. They may want to soon introduce department codes or other things to the identifier, and you're back at this problem again.
plasma potatowned me, I was just about to chime in and say this. Things like this come up on Slashdot semi-regularly and you should see the rage of DBAs etc that complain, it's hilarious value!
10:16am 31/03/10 Permalink
Hogfather
Cairns, Queensland
5675 posts
PS: You should never use identity columns as business identifiers; as these problems just start to happen. They may want to soon introduce department codes or other things to the identifier, and you're back at this problem again.

If that's directed at me, I'm not using an identity for this value, and I've said that quite a few times in the thread - half the solutions raised related to identities when we're not using them for the column. The Project Number is a plain unique-constrinaed INT with some logic assigned to it on insert (as explained in my careful first post).

We use identity integers or GUIDs for PKs. F*** DBAs and their desire for candidate keys.
10:22am 31/03/10 Permalink
Any
Brisbane, Queensland
421 posts
Can't you just record a single number which is the last auto-generated id? Then on next creation of an auto-generated number just start from the last one and increment until you find an unused number? This assumes that you are auto-generating 90% of the time. If you're expecting large squences of user specified id's then it wouldn't scale well.
10:23am 31/03/10 Permalink
trog
AGN Admin
Brisbane, Queensland
30042 posts
Can't you just tell the client he's a retard, I would say
10:26am 31/03/10 Permalink
Hogfather
Cairns, Queensland
5676 posts
Can't you just record a single number which is the last auto-generated id? Then on next creation of an auto-generated number just start from the last one and increment until you find an unused number? This assumes that you are auto-generating 90% of the time. If you're expecting large squences of user specified id's then it wouldn't scale well.

Thanks for the recap.
Can't you just tell the client he's a retard, I would say

Isn't it harder to invoice a client who has told you to get f***ed?
10:26am 31/03/10 Permalink
Any
Brisbane, Queensland
422 posts
Sorry Hoggy I missed where you said that users will be acting as sequential number generators most of the time. Out of interest, whats the use case for this?
10:32am 31/03/10 Permalink
Hogfather
Cairns, Queensland
5677 posts
Sorry Hoggy I missed where you said that users will be acting as sequential number generators most of the time. Out of interest, whats the use case for this?
heh no worries!

Its for a job management system.

For some stupid reason they want the project number to auto-increment, as well as be able to assign them manually. We avoided the use of identity columns because I knew that user assignment was coming, but the need to back-fill numbers was quite a surprise - we had been runninng with the idea that if the last number was 1804, then they manually add project 6000, that 6001 would logically be next.

Apparently not ... ;) Oh well they pay the bills, and we have a solution that should work and appears to be scalable to requirements.
10:37am 31/03/10 Permalink
Any
Brisbane, Queensland
423 posts
Ok I can see that, but is it normal for the user to manually add project 6000, 6001, 6002,...,10000 then want to auto generate 10001 after the last auto generated number was pre-6000?
10:44am 31/03/10 Permalink
Opec
Brisbane, Queensland
6370 posts

For some stupid reason they want the project number to auto-increment, as well as be able to assign them manually. We avoided the use of identity columns because I knew that user assignment was coming, but the need to back-fill numbers was quite a surprise - we had been runninng with the idea that if the last number was 1804, then they manually add project 6000, that 6001 would logically be next.


Reusing project number as a "unique" identifier is bound to cause them problem down the road, especially if you delete the row and fill the gap (reuse) the same number for a different/new project. That's why they're called "unique identifier" heh. I know it's your client's requirement but boy it's going to suck down the road when they have 3-4 projects that are now magically have the same "unique identifier" project number lol.
10:56am 31/03/10 Permalink
plok
Brisbane, Queensland
498 posts
Thirded on the don't-give-identity-columns-any-semantics.

Haven't read any of the links but I wonder if you want a purely DB solution then one possibility would be to:

* have an id column and don't f*** with it
* add an extra column that contains the number the client cares about, ClientID

Now it depending on how you feel about triggers, but you could add an insert trigger that either inserts the supplied ClientID (a unique constraint on the column will prevent dupes) or if none is supplied then look up the current next value. You can easily store you "actual" next value in another table so you don't need compute it every time.

Dunno, it's a bit rubbish to be honest but it's all in the DB and will be very quick unless you hit very high insert rates.

No doubt there's a much more elegant approach in the links already given :D
11:06am 31/03/10 Permalink
GumbyNoTalent
Brisbane, Queensland
6443 posts
Have an auto indexing column hidden from the client, and have a field where they can enter in a integer and set the column to unique, save a counter else where to increment for a "auto number".
11:48am 31/03/10 Permalink
Dazhel
Gold Coast, Queensland
1274 posts
F*** DBAs and their desire for candidate keys.


In more general terms:
F*** business requirements for initially modelling a piece of data as a natural key, only to have business rules change for some extremely stupid reason later on down the track.

<3 Surrogate keys
12:20pm 31/03/10 Permalink
TicMan
Melbourne, Victoria
5752 posts
I don't know the full details of the client requirements but I wonder if you're approaching it the wrong way. There is an obvious requirement from the client to use a number that isn't the MAX so would it be smarter to check if the number exists and if it does then return an error to the client saying that the number already exists and they should double check they want to use that particular number rather than having your app pick the next number for them?

Otherwise the only solution albeit a s***** one would be to create a stored proc, loop through the current records using a cursor until a number doesn't exist and return that as the next value in the sequence;

DECLARE @intNextNum INT = 0
DECLARE @intNumber INT
DECLARE NumberValues CURSOR FOR SELECT Number FROM dbo.Project.Number ORDER BY Number ASC

OPEN NumberValues
FETCH NEXT FROM NumberValues INTO @intNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SET @intNextNum = @IntNextNum + 1
IF @intNextNum <> @intNumber
BEGIN
BREAK
END

FETCH NEXT FROM NumberValues INTO @intNumber
END

CLOSE NumberValues
DEALLOCATE NumberValues
SELECT @intNextNum
12:21pm 31/03/10 Permalink
Dazhel
Gold Coast, Queensland
1275 posts

// increment till you find the next
while ( (select count(*) from reservedProjectIds where reservedId = @currentMax) > 0)



loop through the current records using a cursor


Scary stuff. Have you seen what happens to these solutions on systems containing tens of millions of rows?
12:30pm 31/03/10 Permalink
Any
Brisbane, Queensland
424 posts
from a guy i work with:

select min(ProjectID + 1) from Projects
where (ProjectID + 1) not in
(
select ProjectID from Projects
)

How would that perform? (I know nothing about db performance... obviously?)
12:50pm 31/03/10 Permalink
Hogfather
Cairns, Queensland
5678 posts
Any: that does look really interesting, and its similar to what we're using for now. I wonder how it would execute though, I suspect that it will turn into a table scan (slow) because the calculation in the select means that the indexes can't be used. I'm not a DBA but we've had performance trouble doing arithmetic in selects like that. Our current solution may have a similar flaw anyway, it might be an idea to build a temporary table and then work with that..

The amount of comprehension fail in this thread is kind of staggering, although its spawned a nice side discussion about candidate and surrogate keys so I'm not going to rage against it anymore (the problem is solved for now anyway).
01:47pm 31/03/10 Permalink
Thundercracker
Brisbane, Queensland
2415 posts
// increment till you find the next
while ( (select count(*) from reservedProjectIds where reservedId = @currentMax) > 0)
loop through the current records using a cursor
Scary stuff. Have you seen what happe
02:03pm 31/03/10 Permalink
TicMan
Melbourne, Victoria
5753 posts
Scary stuff. Have you seen what happens to these solutions on systems containing tens of millions of rows?


Yep, although a system with tens of millions of rows wouldn't have their identity field as user editable and I did say it was a s***** solution :)
02:08pm 31/03/10 Permalink
Thundercracker
Brisbane, Queensland
2416 posts
edit: nm :P
02:10pm 31/03/10 Permalink
Hogfather
Cairns, Queensland
5681 posts
Scary stuff. Have you seen what happens to these solutions on systems containing tens of millions of rows?
Yep, although a system with tens of millions of rows wouldn't have their identity field as user editable and I did say it was a s***** solution :)

P*(&TPq p;u geri;qu vfgeqri;vugbqe i;vgue ;qivugeq vi;beq;iuv g;i

ITS NOT AN IDENTITY FIELD FOR F**** SAKE, ITS A UNIQUE INTEGER WITH SOME ASSIGNMENT & GENERATION RULES
02:18pm 31/03/10 Permalink
Plasma
1014 posts
We have a Project table that has a unique Number column. dbo.Project.Number is basically a custom identity column

Fail.
02:22pm 31/03/10 Permalink
Mass
Brisbane, Queensland
917 posts
What tic says is true I had the same problem with a third party developed application for simple project tracking. They used our "JobID" as their indexed key field........mega fail. I made them rewrite it from scratch on their $.
02:25pm 31/03/10 Permalink
Hogfather
Cairns, Queensland
5682 posts
We have a Project table that has a unique Number column. dbo.Project.Number is basically a custom identity column
Fail.

Huh? Yeh its a "basically a custom custom identity" column, not an actual SQL identity column. I then explained what the algorithm was that generated the number and the problem that was to be solved. The idea was to give the impression of an automatically-incrementing field (which it is).

In hindsight I should never have used the f*****g word identity to explain what we were doing because no matter how many times I explain it people can't let it go, and assume we're puling tricks like SET IDENTITY INSERT ON.
02:32pm 31/03/10 Permalink
Hogfather
Cairns, Queensland
5683 posts
What tic says is true I had the same problem with a third party developed application for simple project tracking. They used our "JobID" as their indexed key field........mega fail. I made them rewrite it from scratch on their $.
Its not an indexed key field. Its not a key field. Its neither an identity, nor is it a key at all - the number isn't part of any relationships. For all the reasons above (and a few of our own), we use a surrogate key as the PK that the client has no exposure to. Standard practice.

Its just a numeric name for the row. You guys need to stop freaking out because its a number that automatically goes up.
02:37pm 31/03/10 Permalink
Nathan
Canberra, Australian Capital Territory
3382 posts
Fail.

+1, the initial post while describing what you are doing, is also intermingled with stuff that just confuses the reader as to what the issue is

Perhaps this thread allows the problem to be more clearly restated:


We have a Project table that has a unique Number column, with allowed values from 1 to 2^32-1. When inserting rows into this Project table, the customer may either
a) specify an arbitrary Number, which if its not already used is stored as is
b) not specify anything, in which case the smallest integer in the range 1 to 2^32-1 that is not already in use, is inserted.

Any ideas for how to implement this in a performant manner?

T, FTFY? :)
02:39pm 31/03/10 Permalink
Nathan
Canberra, Australian Capital Territory
3383 posts
Any's style of solution is on the money. If you need lots of Projects (say 10,000 or more) than you have no choice IMO but to turn ProjectNumber into its own entity, pre-populate the table, and then mark them off as used.
02:41pm 31/03/10 Permalink
Hogfather
Cairns, Queensland
5684 posts
Sigh.

F*** it. I apologise if you guys couldn't decode my post that I threw together before coffee at like 7:30am - I was actually trying to give as much informsation as possible so that someone who wanted to help woudln't waste their time.

F*** it. It was solved hours ago anyway.
02:44pm 31/03/10 Permalink
Khel
Melbourne, Victoria
14524 posts
I have nfi what a surrogate key, natural key or candidate key are. Are these more fancy names for things I already know? I can never keep up with the fancy, smart way to talk about programming.

Edit: after a little bit of googling it seems that yes, they are.
02:50pm 31/03/10 Permalink
Nathan
Canberra, Australian Capital Territory
3384 posts
A surrogate key, also known as a synthetic key, is an arbitrary and meaningless identifier, such as a GUID or an auto-incrementing integer.

A natural key is the opposite - a meaningful identifier within the domain model.

I would say its commonly accepted as best practice that a primary key should always be a surrogate.
02:57pm 31/03/10 Permalink
trog
AGN Admin
Brisbane, Queensland
30051 posts
You guys made me do this:

http://trog.qgl.org/up/surrogates.jpg
03:03pm 31/03/10 Permalink
Hogfather
Cairns, Queensland
5686 posts
Hahah torg
This isn't a bad article about keys
Don't naturalize surrogate keys. As soon as you display the value of a surrogate key to your end users, or worse yet allow them to work with the value (perhaps to search), you have effectively given the key business meaning. This in effect naturalizes the key and thereby negates some of the advantages of surrogate keys.

This is basically why the Project Number isn't the PK, even though its a candidate key.
03:06pm 31/03/10 Permalink
Dazhel
Gold Coast, Queensland
1279 posts
I would say its commonly accepted as best practice that a primary key should always be a surrogate.


Look, a flame war... it's coming right for us! BAM!
03:09pm 31/03/10 Permalink
thermite
Brisbane, Queensland
4631 posts
I have nfi what a surrogate key, natural key or candidate key are. Are these more fancy names for things I already know?


Yeah, you don't need the vocabulary to figure out what you need to do. This surrogate thing I just call the autonum or serial (not to be confused with serialized). Then again I don't do MS SQL which might require you to use that lingo.
03:14pm 31/03/10 Permalink
Hogfather
Cairns, Queensland
5688 posts
thermite: Nah its not an MS SQL thing, its a data modelling thing. If you've studied relational database stuff you should have encountered the terms.
03:17pm 31/03/10 Permalink
Opec
Brisbane, Queensland
6372 posts
I would say its commonly accepted as best practice that a primary key should always be a surrogate.
Look, a flame war... it's coming right for us! BAM!


Nah just have both in the table. I do it in my all my apps, I don't care if it's not technically correct RDB design, it gives me flexibility in my application. Username is a good example, same with job tracking. I use surrogate for all foreign keys and referential integrity, and natural key for business rules related stuff like unique username/job number. Works great.
03:19pm 31/03/10 Permalink
Nathan
Canberra, Australian Capital Territory
3385 posts
This surrogate thing I just call the autonum or serial (not to be confused with serialized)

Presumedly you learned those two terms because they are what your particular RDBMS use? Surrogate key is the RDBMS-agnostic umbrella term.
03:20pm 31/03/10 Permalink
Dazhel
Gold Coast, Queensland
1280 posts
I have nfi what a surrogate key, natural key or candidate key are. Are these more fancy names for things I already know?


Fancy names? Huh? They're just names of concepts in relational modelling.

A surrogate key does not necessarily imply automatic numbering (autonum/serial/identity, etc), they're an arbitrary row identifier that has no meaning in the domain model. Integer surrogate keys are often defined as automatic numbering though.
03:30pm 31/03/10 Permalink
thermite
Brisbane, Queensland
4633 posts
If you've studied relational database stuff you should have encountered the terms.


I studied ER modelling and Oracle at uni, but I don't think I knew about autonums and stuff back then at all. My lecturers had heavy accents though, so it might have come up and I didn't realise :/

Ah yes good point that the id doesn't have to be automatic, just meaningless, but really in most cases that's just what any id is.


last edited by thermite at 15:38:59 31/Mar/10
03:34pm 31/03/10 Permalink
Dazhel
Gold Coast, Queensland
1281 posts
Nah just have both in the table.


Haha yeah, I wasn't necessarily disagreeing with Nathan, just that best practice in this case depends on who you ask.
03:40pm 31/03/10 Permalink
Hogfather
Cairns, Queensland
5690 posts
I studied ER modelling and Oracle at uni, but I don't think I knew about autonums and stuff back then at all. My lecturers had heavy accents though, so it might have come up and I didn't realise :/

ER modelling is a bit different to SQL - the keys aren't autonums (necessarily). Have aread of http://www.agiledata.org/essays/keys.html - its just relational database concepts, should be pretty well platform agnostic.
03:44pm 31/03/10 Permalink
Khel
Melbourne, Victoria
14525 posts
Yeah, I guess I just meant what thermite said, its stuff I use all the time and have done for years (the surrogate key thing that is), but never knew it had a name or what the name was. Same as natural keys and candidate keys, when I looked those things up, it was stuff I already knew, just never knew the name. But then its not like I've ever done a lot of hardcore data modelling or been a DBA before.

I've guess its just got my back up a bit recently cos I'd been going to job interviews and having people look down their nose at me because I didn't know the big fancy academic way to talk about things. They seem to be less interested in you ability as a programmer and more interested in your ability to talk programmer buzzwords. S**** me. Not to say thats what anyone here intended, it just gave me vietnam style flashbacks to wankers who interviewed me.

Ok, I'm done now. Carry on.
03:44pm 31/03/10 Permalink
Dazhel
Gold Coast, Queensland
1282 posts
How many times have you been asked to define polymorphism and inheritance in the last few months Khel?

Lambda calculus and closures seem all the rage these days with C# 3.0 too.
03:51pm 31/03/10 Permalink
Strange Rash
1192 posts
so what happens when the user tries to specify a value that's already in use?

sounds woeful
04:31pm 31/03/10 Permalink
trog
AGN Admin
Brisbane, Queensland
30054 posts
I studied ER modelling and Oracle at uni, but I don't think I knew about autonums and stuff back then at all. My lecturers had heavy accents though, so it might have come up and I didn't realise :/
haha I wonder how much stuff like that I missed because my lecturers didn't speak English well. Well, to be fair, probably not a lot, because once I realised it was easier to just read the lecture notes myself than to sit in the lecture trying to decipher bad English, I stopped going to lectures entirely.
05:20pm 31/03/10 Permalink
Khel
Melbourne, Victoria
14528 posts
How many times have you been asked to define polymorphism and inheritance in the last few months Khel?


Oh man, its painful. Especially when you're being interviewed by someone who isn't a programmer, they're a project manager or something with just a cursory knowledge of the material. They ask you about object oriented programming, and I give a big explanation of the sort of code it lets you write, some of the applications of it, gave a couple of examples from stuff I did at my last job, and the guy interviewing me looks a bit confused and lost and asks me "So what did you use to do all that, what features of object oriented programming make all that possible?" and I look a bit confused and hesitantly answer "Umm... polymorphism? classes? inheritence?" and he smiles a big smile and looks pleased, glad that I got back on script.

Sorry hogfather, I wont use your thread to b**** about wankers anymore, promise.
10:52pm 31/03/10 Permalink
GumbyNoTalent
Brisbane, Queensland
6447 posts
@hogfather

I feel your pain...

me "its an auto incremented value, the reference field is where you put your identifier"
them "but we want to tell it what project we are working on"
me "use the reference field not the project ID, you can't edit it"
them "but that is too confusing"
me "so what happens if you have 2 projects with the same project number?"
them "won't happen, we (the project managers) won't allow it"

Quick sql statement show differently... dumbasses.
10:36am 01/04/10 Permalink
Hogfather
Cairns, Queensland
5697 posts
so what happens when the user tries to specify a value that's already in use?

sounds woeful

Computer says no :)

Gumby: never, ever show them the surrogate key. Hide that mother f***** and pretend it doesn't exist.
10:38am 01/04/10 Permalink
GumbyNoTalent
Brisbane, Queensland
6449 posts
Sometimes ya gotta live with what you are given... ;)
10:47am 01/04/10 Permalink
Hogfather
Cairns, Queensland
5700 posts
No s***. Isnt it GREAT to be a programmer?! No, really. Its awesome-sauce.
10:48am 01/04/10 Permalink
GumbyNoTalent
Brisbane, Queensland
6450 posts
lol after 25 years commercial coding the awesome-sauce is spread thinner then a Dominos pizza sauce, but it still their occasionally.

EDIT
Worse yet is explaining this s*** to some young gun coder who is green and wet behind the ears all at the same time, makes me wish for the old days when you could beat your employees and it was ok.

last edited by GumbyNoTalent at 10:54:24 01/Apr/10
10:53am 01/04/10 Permalink
Dazhel
Gold Coast, Queensland
1291 posts
It always amuses when business owners want to specify their own identifier anyway. It seems like so much hassle just so manager A can say to manager B 'i've got a bigger project number than you' or 'we're managing project 2000 which is four times as rad as project 500'.
11:57am 01/04/10 Permalink
Hogfather
Cairns, Queensland
5705 posts
It always amuses when business owners want to specify their own identifier anyway. It seems like so much hassle just so manager A can say to manager B 'i've got a bigger project number than you' or 'we're managing project 2000 which is four times as rad as project 500'.
Yeh its dopey. What can you do though, they want the feature!

I told them things like: "its complicated", "it could bite you in the arse later" and "you can more easily and cheaply handle this by ..."
They said "we'll pay"
I said "OK then" :)
01:05pm 01/04/10 Permalink
Dazhel
Gold Coast, Queensland
1294 posts
They said "we'll pay"
I said "OK then" :)


Hahaha, if there's one thing I found out early in this caper is that anything can happen when the magic words get spoken!
01:18pm 01/04/10 Permalink
plok
Brisbane, Queensland
499 posts
I don´t think the database is the right place for this anyway to be honest. You mentioned you have a middle tier and this sounds like business logic so why not just put it there?

When your middle tier starts up it can do the scan of the table and store a list of tuples represting the gap ranges you have (or any number of easy approaches), then have an ID allocator in your middle tier and it´s all solved?

The DB then just gets given the correct number every time.

While you can get away with letting the DB handle your concurrency I´d be a bit hesitant. Languages like T/SQL etc make it very easy to get it wrong in subtle ways (more so than in your middle tier anyway, depending on which language you´re using).

Should be faster too, not needing a DB round trip to test for collisions etc.
08:05am 04/04/10 Permalink
Hogfather
Cairns, Queensland
5732 posts
When your middle tier starts up it can do the scan of the table and store a list of tuples represting the gap ranges you have (or any number of easy approaches), then have an ID allocator in your middle tier and it´s all solved?

That's actually a really good idea plok. Props.
12:40pm 04/04/10 Permalink
system
Internet
--
12:40pm 04/04/10 Permalink
AusGamers Forums
Show: per page
1
This thread is archived and cannot be replied to.