All: By default I use incremented Integer values ( either AUTONUM ( or coded (thanks Razzak)) ) as Primary Key values with no embedded or implicit meaning.
I'm designing a table which (ideally) references the PKs of multiple tables within ONE COMMON field in one-to-one relationships ( Were I referencing only one table, this would be an FK field. But as multiple tables are being referenced, Referential Integrity is out the window ). So I need a mechanism to reliably indicate which table is being referenced within each row of the referencing table; otherwise, all sorts of mayhem results. Seems there are two ways to go: 1. Use my default PK construction in each referenced table. Include a "TableReferenced" column in the referencing table. Insert the referenced table name in each row. Use that value in code to follow the otherwise inscrutible FK value to the correct referenced table; Example Approach #1: ReferencingTable PrimaryKey|KeyReferenced|TableReferenced 10001|10051|Apple 10002|10051|MuscleCar 10003|10051|RocketMotor 2. Embed a unique "Table Name" flag within the PK definition in each referenced table. Build a "FlagTable" relating flags to tablenames. Use the FlagTable and flag value from the PK in code to find the right table. Example Approach #2: Referencing Table PrimaryKey|KeyReferenced 10001|AP10051 10002|MC10051 10003|RM10051 FlagTable PrimaryKey|FlagValue|TableName 10001|AP|Apple 10002|MC|MuscleCar 10003|RM|RocketMotor There's probably a Door Number Three, but I'm not that quick today. Any votes? Cautions? Thanks much, Bruce Chitiea SafeSectors, Inc. 909.238.9012
