Alastair:
If I read you right, you’ve condensed an ALBUM and TRACK table into one table where the PK incorporates two meanings: Album ID Number and Track ID Number. Each record then “explains” the meaning of the PK number, such that simple arithmetic on the PK allows one to sort out [ 100101 | Grateful Dead | Europe ’72 | Cumberland Blues ] from [ 100102 | ...| He’s Gone ]. On first blush, this appears to be a vote for something akin to my Approach #2, where the PK concatenates a Table Alias and Integer ID Number. Going to think on that a bit. Thanks very much, Bruce From: [email protected] [mailto:[email protected]] On Behalf Of Alastair Burr Sent: Wednesday, October 15, 2014 10:18 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Primary Key Construction: Wisdom Needed 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 <mailto:[email protected]> Sent: Wednesday, October 15, 2014 5:34 PM To: RBASE-L Mailing List <mailto:[email protected]> 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
