sql - Database design for frequent select and insert -

i'd know best database design following case.

i use spring , sql server , case simplified follow:

let there're 3 tables here:

item:

• id(unique,primary-key,auto-increase)
• name
• category

address:

• id(unique,primary-key,auto-increase)
• address
• postcode

order:

• id(unique,primary-key,auto-increase)
• price
• date
• itemid(foreign-key)
• addressid(foreign-key)

the business logic spring handling incoming message including information of item, address , order.

if incoming item data exists in database(exist means name , category identical), return id of record. it doesn't exist, insert new record , return new id.

address pretty same. if incoming address data exists in database(exist means address , postcode identical), return id of record. it doesn't exist, insert new record , return new id.

and insert order table previous item id , address id foreign key.

what did right put select , insert logic 1 stored procedure reduce latency between communication of server , database.

i'm wondering if there's better solution case.

---

updates:

here's proc use:

create procedere spinsert  --list of parameters  set @tmpitemid = (select id item name=@name , category=@category)  if @tmpitemid null     begin         insert item values(@name,@category)        set @tmpitemid=@@identity    end  --the same logic applied address  insert order values(@price,@date,@tmpitemid,@tmpaddressid) 

after testing while, speed not satisfied , takes around 10-20 seconds insert 100 records.

may ask methods improve?

btw, think bottleneck of store procedure select id every time , insert frequently. using uuid index of table rather auto-increase index in database? in way, java side handle select part because java side provides uuid , sql server cares insert

is choice?

what have planned handle these 3 database transactions in 1 stored procedure best solution unless forsee deadlock situation, multiple users trying add same order (same address , item) @ same time.