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 type
-column:
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?