Bruce, It’s actually two tables – the item one contains common info such as overall time, dates, etc.
The tracks one contains title, artist and running time. Other tables contains writers and publishers, musicians and instruments, comments, etc. Any idnumber than ends in “00” refers to the whole item so musicians, for example, that play on all tracks only get listed once rather than for every track. It’s partly a throwback to the days when space was at a premium but mostly because it keeps reports much more concise without repeating the same info for every track. Although tracknumber “00” technically often exists in the reports I replace it with a space. Obviously, it sorts before track number 1 so it follows the item or header data. On forms I use it so I can enter common data. Regards, Alastair. From: Bruce A. Chitiea Sent: Wednesday, October 15, 2014 8:41 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Primary Key Construction: Wisdom Needed 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 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
