How do i properly insert a tag? SQL

Question

example

tag  { id PK, name TEXT };
item { id PK, exampleData LONG, tagId LONG //or foreign key depending on provider }

i'm using SQLite and C#. Is it proper to

1. Start a transaction
2. Insert into tag
3. If affected rows = 0 do a select to get tag.PK otherwise use last_insert_rowid
4. Insert item

or will there be a problem with the above? I plan to switch to MySql or TSQL

Answer 1

I would generally prefer :

1. Start a transaction
2. select from tag to determine whether the tag was already existing
3. if not existing, then insert into tag, and get the generated id (checking if the insert worked, of course)
4. insert into item
5. commit transaction (or rollback if there was an error)

The only difference with what your suggested is that I try to select from tag before trying to insert. Why ? Because I prefer not doing an insert that will most likely fail -- at least, if I have an UNIQUE index on the tag's name (and you probably should).

But it will probably not change much...

Answer 2

The only problem I see with your design is that each item can only have 1 tag. Was that your intention?

Otherwise, your insertion procedure is fine. You just need to check if the tag already exists, as Pascal Martin suggested.

Answer 3

You can conditionally insert the tag if it doesn't exist like:

insert into tag (name)
select 'NewTagName'
where not exists (select * from tag where name = 'NewTagName')

Then, while inserting the new item, you can query the tag id:

insert into item (exampleData, tagid)
select 'NewData', (select id from tag where name = 'NewTagName')

This method doesn't require retrieving the id of the inserted row, and will work fine without a transaction.

Answer 4

i think there is no wrong with your solution i believe its correct and use it

but when you change to MySql or TSql use the same solution

1- insert first 2- return @@identity "ID" of the inserted row .....