locked
How to design table for multiple branches RRS feed

  • Question

  • Dear Sir,

                     I've designed salesbill table for single user. But now client is having multiple branches of same company, say A and B. How to store salesbill for both branches? Should I use BRanch field in table? If so, then how to increment salesbill number for individually?  Any help is greatly appreciated. Thanks in advance.

     

    Regards

    iCreator


    Irshad
    Thursday, July 1, 2010 8:29 AM

Answers

  • SalesBill table should contain Companyid and BranchID as well
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 1, 2010 8:47 AM
  • Can you provide some DML for your tables, not 100% sure what  you are trying to do.  If you are using an identity column and you want to increment them separately, that won't work.  If you simply want to get the next number to present to the user, it is pretty easy, but you have to be careful with transactions.  I have a blog here about implementing a critical section using applocks, which allows you to single thread sql calls through a critical section, such as to get an incrementing number. (http://sqlblog.com/blogs/louis_davidson/archive/2007/05/07/using-application-locks-to-implement-a-critical-section-in-t-sql-code.aspx). However, you have to use this outside of external transactions or you will get a lot of blocking.

    So you would go fetch the next invoice number (probably like Naom says, in one or two separate tables), then use that number for the transaction. You would basically agree to lose numbers that never got used, like if the user changed their minds.

    In any case, a simplified example of the ddl would help one of us to put together a quick, yet more complete, example for you.


    Louis

    Thursday, July 29, 2010 3:30 AM
  • I think the only way to increment this properly is to use an extra table holding max number for each branch. It is still a bit tricky.

    Assuming you have a Keys table with BranchID LastKeyID fields

     

    declare @LastKey int
    
    if not exists(select 1 from Keys where BranchID = @BranchID)
    
      begin
    
       set @LastKey = 1
    
       insert into Keys (with tablock) values (@BranchID, @LastKey)
    
    end
    
    else
    
    begin transaction
    
    select @LastKey = LastKeyID + 1 from Keys (WITH TABLOCK) where BranchID = @BranchID
    
    update Keys (with tablock) set LastKeyID = @LastKeyID where BranchID = @BranchID
    
    end transaction
    
    insert into myTable (...) values (@BranchID, @LastKey,...)
    
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, July 5, 2010 5:04 PM
    Answerer
  • You have to add BranchID to you table and mark that as part of Primary key. 

    For salebill number, before inserting or in Insert statement get the last billnumber for that branch and increment it by one. In this way if two users wants to add a record, you would not get any duplicate record. 

    Declare @NewBillNumber As SmallInt 

    Select @NewBillNumber = IsNull(Max(BillNumber), 0) + 1 From Table1 Where BranchId = @BranchId

    Insert Into Table1 (BranchId, BillNumber, ...)

    Values (@BranchId, @NewBillNumber, ...)

    Wednesday, July 28, 2010 10:32 PM

All replies

  • SalesBill table should contain Companyid and BranchID as well
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 1, 2010 8:47 AM
  • Could you please explain the your requirement properly?

     

    Thanks,

    Sandeep

     

     

    Thursday, July 1, 2010 11:22 AM
  • Please post the TABLE layout.
    Monday, July 5, 2010 4:47 PM
    Answerer
  • I think the only way to increment this properly is to use an extra table holding max number for each branch. It is still a bit tricky.

    Assuming you have a Keys table with BranchID LastKeyID fields

     

    declare @LastKey int
    
    if not exists(select 1 from Keys where BranchID = @BranchID)
    
      begin
    
       set @LastKey = 1
    
       insert into Keys (with tablock) values (@BranchID, @LastKey)
    
    end
    
    else
    
    begin transaction
    
    select @LastKey = LastKeyID + 1 from Keys (WITH TABLOCK) where BranchID = @BranchID
    
    update Keys (with tablock) set LastKeyID = @LastKeyID where BranchID = @BranchID
    
    end transaction
    
    insert into myTable (...) values (@BranchID, @LastKey,...)
    
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, July 5, 2010 5:04 PM
    Answerer
  • You have to add BranchID to you table and mark that as part of Primary key. 

    For salebill number, before inserting or in Insert statement get the last billnumber for that branch and increment it by one. In this way if two users wants to add a record, you would not get any duplicate record. 

    Declare @NewBillNumber As SmallInt 

    Select @NewBillNumber = IsNull(Max(BillNumber), 0) + 1 From Table1 Where BranchId = @BranchId

    Insert Into Table1 (BranchId, BillNumber, ...)

    Values (@BranchId, @NewBillNumber, ...)

    Wednesday, July 28, 2010 10:32 PM
  • Can you provide some DML for your tables, not 100% sure what  you are trying to do.  If you are using an identity column and you want to increment them separately, that won't work.  If you simply want to get the next number to present to the user, it is pretty easy, but you have to be careful with transactions.  I have a blog here about implementing a critical section using applocks, which allows you to single thread sql calls through a critical section, such as to get an incrementing number. (http://sqlblog.com/blogs/louis_davidson/archive/2007/05/07/using-application-locks-to-implement-a-critical-section-in-t-sql-code.aspx). However, you have to use this outside of external transactions or you will get a lot of blocking.

    So you would go fetch the next invoice number (probably like Naom says, in one or two separate tables), then use that number for the transaction. You would basically agree to lose numbers that never got used, like if the user changed their minds.

    In any case, a simplified example of the ddl would help one of us to put together a quick, yet more complete, example for you.


    Louis

    Thursday, July 29, 2010 3:30 AM
  • Note that this code is not guaranteed to work, because if two people are in the select at the same time, they will get the same answer and will try to insert the same value.  You need to single thread that select statement using updlock and holdlock or applocks (as was my suggestion), and then make sure to get the row inserted prior to the next reader...

    You can use update with an output clause too I believe in 2005 and 2008, it just depends on your algorithm (a simple incrementor like this is pretty easy.)


    Louis

    Thursday, July 29, 2010 5:13 AM