I am using SQlite and the PK column seems to be auto-incrementing. I am a bit concerned about following approach where I want to normalize the
Table: Book id type name ... ... ... 15 1 Foo 16 2 Bar ... ... ... Table: Type id typename 1 magazine 2 novel
What happens, if I recreate the
Type-table and the PK-index (="id") changes, e.g. that
1 now refers to
novel instead of
magazine. So to be more robust, I am thinking about inserting a custom second column that contains the "logical" indices that I refer to:
Table: Book id type name 1 3 Foo 2 4 Bar Table: Type id type typename 1 3 magazine 2 4 novel
This way my custom type-ids would be independent from the primary keys. I think another advantage of the last approach is that I would not have to re-ask the latest (new)
id of a new row, because I would refer to the
type column that I know before. This way when I insert the row I already know the "type" id to make references (I am using threads for the database, so reading a value from the database within a method is difficult or not possible (?) - so if I would need the id, I would have to ask the db first).
Is this a legit approach? Or should I directly create a non-incrementing primary key with my "custom id's" that I manage on my own?