What is the best way to maintain a unique ID field across multiple database tables?
My database contains both businesses and people, and both entities need to have a unique ID field associated with it. In addition, there are other tables (for example Addresses) which can reference either a business or a person using their ID as a foreign key.
Some ideas I am thinking of are:
Use a non-autonumber ID field that gets calculated when a row is inserted. This would solve my problem of being unique, but then anytime I wanted to lookup something by a related property (such as by an address) I'd have to check both tables for which one contains the record I'm looking for.
Add a Prefix to the AutoNumber ID to identify what table to look for the ID in, however then my ID fields in associated tables would probably either become strings or contain flags for what table they are associated with and I'm not sure how that will affect performance.
Merge the People and Businesses into a single table. My problem with this is people and businesses have different properties and need separate fields, and this kind of goes against my nature since I prefer to have separate tables for separate entities.
Create a master table containing a unique ID field, the ID field of either the Person or the Business, and a flag to say which one it is. Then use that ID as my external reference # and in all associated tables.
Some better way of handling this that I am not aware of since I am not a dba
Whatever solution I go with needs to be able to easily to handle a large number of records (the database this is going to replace has a few million records) and is on MS Sql Server