On the weekends, I've been occupied with updating one of the OP's database programs to Delphi 12CE and unicode. I'm becoming very familiar with this so the conversion has been straightforward for most of the time. I'm not going to write about that today.
I was working on the forms that allow data in the MATSCALE table to be edited; this table is really a 'junction table' that defines which 'scales' are connected to which 'purposes', where the 'purposes' table contains entries such as 'work', 'absorption to kibbutz' and similar. Had I was defining this table for the first time instead of inheriting something that I defined 20 - if not 30 - years ago, then I would probably have only the two columns, 'purpose' and 'scale', where combined they create the primary key. I have found it occasionally difficult to work with such a primary key and find it easier to create an auto-increment integer that can serve as the primary key.
It occurred to me early this morning on the dog walk that I could take a leaf out of the way that Priority defines tables; there must always be a primary key (denoted U, for unique) and there can be an autoincrement (denoted A), where this column often serves as a foreign key in other tables. Although I have never done this before, I should change the MATSCALE keys so that the primary key is purpose + scale, leaving 'id' as an autoincrement that is not a key. This way, the same scale can never be defined twice for the same purpose.
Having the idea was relative simple but implementing it was hard. The first thing that I did was to duplicate the MATSCALE table, including copying the data, so that I would have an easy to access table with the data should I need to restore it. I had tried various means of changing the primary key, but none of them worked until I found the following solution: first, define 'id' as a normal integer instead of being an autoincrement (this was in case of having to recopy the data). Then I defined both 'purpose' and 'scale' as not null, committing each change in turn. Then I was able to edit the primary key, adding the two new columns and removing the original column. I then reconnected the 'id' column to the key generator. I dropped the copied table, of course.
So now I have an autoincrementing column that is not the primary key; the actual PK is combined of two columns. I am documenting this in case I decide to implement the same idea elsewhere. I know that there are similar tables in both this database and in the management database.
| Title | Tags | ||
|---|---|---|---|
| 97 | Life is stranger than art | Personal | |
| 98 | 1972 - the year I've been leading up to | Israel, Habonim, Kibbutz, Richard Thompson, 1972 | |
| 184 | Better late than never - II | Richard Thompson, Fleetwood Mac, Albion Band | |
| 269 | The in-basket 4 | Programming, Delphi, In-basket, Resource files | |
| 387 | Human Resources Management results | MBA, HRM | |
| 880 | Vinyl log 21 - 25 July | Richard Thompson, Vinyl log | |
| 1056 | Guitar corner | Guitars | |
| 1246 | Running a procedure from a screen trigger | Priority tips | |
| 1970 | Ding Dong, the witch is dead! | Personal |
No comments:
Post a Comment