Oracle Database Discussions

Hello all!

I'm currently having a debate in my organization concerning the use of IDs in tables.

We have several tables representing different geographic concepts (like for example, states, counties, cities, etc). In each of them, we have a unique "code" that uniquely identify each row of the table. However, this code is a string, with a specific meaning, usually reprensenting the "hierarchy" of these geographic concepts (for example: state = 01, county = 0101 and city = 010101).

We would like to add a REAL ID in these tables, something numeric, without a meaning other than its purpose (of uniquely identifying each row of each table).

In my opinion, the ID of a table is supposed to identify uniquely the rows of THAT table only. So when you make a foreign key to it, you use the parent's table name for naming the column, like "state_id" in the county table. That way, everything is clear.

However, my collegue suggest to use some kind of universal ID, that would uniquely identify each row from ALL TABLES. Yes, it would work has an individual ID for each table (since the sequence number would be unique throughout the database). But the problem is that he'd like to name that column "ID", and refer to THAT same ID in foreign keys and change it's name like the example above (state_ID).

As you see, the debate is around the use of the term "ID".

To me, how can we justify using a column named ID, in EACH table, to uniquely identify the rows in ONE table at a time? For example, when I use a foreign key to the state table, how am I suppose to know and understand that State_ID is supposedly identifying a unique state, when the ID of the county table is actually coming from the same sequence? I always assumed that an ID of a table can be named only "ID" because we know that it resides in that specific table. So when we refer to it like "state_ID" (FK), we know it comes from the state table. Also, I always assumed that a column in a table should have ONLY ONE purpose and meaning (a name, a code, an area, and consequently, an ID).

But, if we have a universal ID in each table, it has a different scope and 2 meanings: it covers the whole database (same sequence) and it means that it uniquely identifies a row in the table AND in the whole database...

What I'm trying to propose to my collegue is to keep the "ID" in EACH individual table as its unique identifier (and change its name for the foreign keys as usual), but then, add another id like "GID" or "UID", for its real purpose of uniquely identifying the rows throughout the database! In other words, one ID per table (for the scope of the table) and a "GID" per table (for the database scope), that would never change name, because it's universal (and not used in FKs). Is that reasonable? Or, is it really redundant?
My collegue instead would prefer to use a universal ID (called ID) and use it in foreign keys (so the name would change)! His main argument is that he wants to try to follow the O-O model (inheritance, etc.), but we are modelling these tables in a relational database.

My real concern is to use this universal ID for the wrong purpose, and especially, I want to prevent any confusion for the database clients...

So... what do you think about all this? Did I make any sense at least?

Thank you for your invaluable comments...

Yanick :o)