Oct 1

Global Database IDs

Here’s a handy feature for a database…

Tables can have primary keys, but what about a database-wide primary key? When a row is inserted, the “id” field would be populated with a numeric key that’s unique to the entire database, not just that table.

Additionally, a two-column system table would store (1) all the keys in the database, and (2) the table each key is in. So, with just a number, you could pull a record out of anywhere in the database, without even knowing exactly what it is.

Better yet, give me SQL syntax like this:

SELECT * WHERE super_bad*ss_global_id IS [number]

Or even just:

SELECT [number]

Since I don’t know what type of object it is, I don’t know the fields, so I can’t request them explicitly anyway.

The table name could be given back to me in a system field. Then my code could read that field, figure out what type of object it is, and load it up from the data in the record.

I know, I know — you could do this manually using triggers and a stored procedure or two. But what a pain. This should be supported natively.

Someone call Larry Ellison. I want royalties.

by Deane   October 1, 2004 5:12 PM   |

Comments

by dontthinkso,   October 1, 2004 8:22 PM  

Too late you could use a GUID for a primary key in ms sql server

by Deane,   October 1, 2004 9:26 PM  

Well, yeah, I knew about that. But I want the system to maintain an index of where in the database (which table) each GUID appears.

by Sebastiano Pilla,   October 4, 2004 4:51 AM  

While syntactically convenient, I'm afraid that such a feature would not scale for high-concurrency databases, due to the bottleneck for the GUID generation. But that's only speculation on my part.

by Deane,   October 4, 2004 6:30 AM  

Well, as a previous commentor mentioned, a lot of databases have "GUID" datatypes already. The only bottleneck would be waiting for insertion into the tracking table.

by Kyle Heon,   March 20, 2006 7:08 PM  

I think Oracle offers what I believe is called a sequence number. Once requested the number is taken and forever gone. It can be used for an insert or simply lost.

by Benjamin,   March 12, 2007 9:44 AM  

Deane,

You might check out Maya's Information Commons. They're using the GUID concept to the extreme and it has promise. They see their system as a possible replacement for URL's by creating a global, permanent ID for ever piece of content in their "commons" system. It really is a cool concept. Not sure yet how it'll play out.

Whether or not their idea pans out, I'm sure you'd enjoy reading their white papers.

Add Comment

Want to advertise on this site? Contact FM.
Web Hosting Web hosting, dedicated servers and Web design services
Laser Toner Cartridges UK laser toner, toner cartridges, hp toner, lexmark toner, samsung toner, canon, toner, epson toner, oki toner, kyocera toner, xerox toner, remanufactured toner, compatible toner
Direct TV Deals Free 4 room direct tv deals. no equipment to buy. free fast professional direct tv installation. this is the best direct tv deal available anywhere.
SEO Article Learn from the experts with our SEO article.
rope light Shopping with birddog distributing, inc., gives you access to the lowest prices, the best customer service and the quickest delivery times possible.
Laptop AC Adapter We offer genuine factory direct replacement AC adapters.
Direct TV Best satellite TV deals.
Direct TV Deals Direct TV programming deals are varied and include packages containing from 50 channels up to over 250 channels.
8mm film to DVD Retain family memories with the only frame by frame digital restoration service in the United States for your 8mm film to DVD today
Rubber Stamp Shop for custom self-inking stamps, hand stamps, address stamps, label stamps, check endorsement stamps, check deposit stamps, date stamps, pre inks, pocket stamps, ink and much more!