CODEWITHSUNDEEP
database-design
Farhawd
Farhawd

Reputation: 35

Database and program Design For reservation system

I want to write a reservation system. in sample it has 2 table:

_Classes: C_ID, C_title, C_quantity

_Persons: P_ID, P_Name, Class_ID

the reserve should be continue until Count of _persons of a class equals C_quantity.

my question is about the best way to check Capacity availability while inserting new person to the class? is it possible that parallel registering occurred in a busy system?

store procedure ? triggers ? trans? what is most corrected idea?

(C#, SQL server)

Upvotes: 1

Views: 540

Answers (1)

Daniel Svalefelt
Daniel Svalefelt

Reputation: 71

I would say that all solutions workw. If there is ok to use a stored procedure it would be the best way in my opinion. It is possile using triggers, but it is harder to get a good error handling.

There is no need for transactions, as you could join and get one query.

There is a little example with a temptable how you could insert new rows until a maximum. If you do that in a proc or sql statement does not matter, but I think it will be hard using trad EF.


    create table #tmp
    (
        id int identity(1,1),
        data int
    )

-- call many times 
    declare @result table (id int )
    insert into #tmp 
    output Inserted.id into @result
    select a.* from 
    (select 1 data ) a
    inner join (select count(*) cnt from #tmp ) c on c.cnt < 5

    -- to get the new row id ( null/empty if full)
    select id from @result

Upvotes: 1

Related Questions