If I have understood correctly I think that I have a similar need: I have a LP/Cassette/CD/DVD, etc identity number column which is 6 digits long with two zeroes at the end – I doubt I’ll ever have 9999 of them. So 100100, 100200...
I use a two digit track number column – I doubt there ever be more than 99 on any of them per item. I simply use a 6 digit code made up of the two numbers – item first then track but replacing the two zeroes with the track number. I use 100100 as the whole item when I don’t need the track info or for common info and 100101, 100102, 100103... for the tracks. The trick is that the primary key for every table is the computed column of the item number plus the track number – I like easy arithmetic! I can make views based on the computed column that include the common info (essentially track “00”) which was essential for me. It has worked for years – back to v3.1 if I remember correctly. Regards, Alastair. From: Bruce A. Chitiea Sent: Wednesday, October 15, 2014 5:34 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Primary Key Construction: Wisdom Needed 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
