>I've run into a situation where I miss my SQL days: GUIDS'. Right now in Mere Mortals, we can use NewID() stored procedure to get a unique identifier --for that table--.
>
>I've taken a look at the NEWID code, and have a few questions. Why does the ID table keep track of each table's "next" id to use? Why not just use the same concept, but keep track of just one ID. So if any table is making a new record, instead of looking up the ID for it's table, just look up the new ID for all tables...
>Had this been tried before? Did Kevin run into a problem with conflicts of some kind?
>
>The reason I was looking at all this is because (for example) I have an Address table. I'd like to put all addresses of all our projects in this table. However, I have a lot of objects/tables that need addresses. (Customers, Employees, Contacts, etc.) In the Address Table, I can't reference iOwnerID = 1 to belong to AddressID #1, because each table (Customer, employees, contacts) will have their own ID=1.
> I also can't add fields to the Address Table such as: "CustomerID", "EmployeeID", "ContactID" because the list could go on forever. At the moment I'm using reference tables, but that causes headaches for something as simple as referencing addresses.
>
>Thanks,
>Dustin
>Keystone


I hope you and Kevin won't mind my interjecting a couple of points here.

1) What you are asking for does not sound that difficult and is one way (not the best) to address your specific situation. However what you are asking for is not a true global ID, of the type that SQL Server (and every other client server database ) generates. A true GUID is truly unique not accross the database, but accoss multiple databases. In fact if working perfectly (and of couse nothing works perfectly) -- a GUID is unique across the entire planet. Guids are usually based upon date, and time, and network card address. Unfortunately, in practice duplicate GUIDs have been know to happen. Windows does actually generate true GUIDs and you can use an OS call generate them. Unfortunately I can't give you the details, because I have never done this -- but if you search you will probably find threads on UT.

2) Secondly, In my opinion, the best way to handle your specific situation is to add one flag to the address table, address type. 1=contactid, 2=employid and so on. Then based on these values you would know which table to lookup the ID in. With GUIDS you still have to search all the tables to find out which table it is in, so this would give you better performance, while remaining pretty simple.

From what you say you have a LOT of tables referencing the address table, so I would maintain the relationship as to which table each code stands for in another table

Address_codes_table
Address_type | Table Name
           1 | Contact_table  (substitute  your actual table name) 
           2 | Employee_tabe  (substitute  your actual table_name)