Multiuser System with Sequential Numbers

Benji_
Benji_ used Ask the Experts™
on
Hi,

Currently developing a system that will require multi company use, what would you advise the best way to get sequential invoice numbers for each company for there orders, so for example

Company A)
Order:  A1, A2, A3

Company B)
Order: B1,B2,B3

Best Regards
Benji
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Query the last invoice number for the company, remove the alpha, add one to the number, add the company letter,  and that should be the next invoice.

Ss

Author

Commented:
if we have 800-1000 users on 1 company what are the likely chances of someone getting the same invoice number at the same time?

Regards
Ben
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Use separate tables for each company.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
the system will be generally for sale online for companies to use the product, so using seperate tables is not realy something i want to explore, as if you have 900 companies it can become very messy as there is about 9 tables which require sequential numbering.
Commented:
I was going to say the same thing. One option is to store an alphanumeric code for the company in the companies table and a (partial) numeric code for the order in the orders table -- to create the actual order code you concatenate both.

Or more precisely, you retrieve the numeric code for the latest order belonging to that company, then increase that number by one, then you concatenate the company alphanumeric code and the (new) order code.

That's a rough idea, but you can get the basics from it.
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
If you have "9 tables which require sequential numbering", then you should reconsider your plan.  It's already messy.  In addition, there is a risk of exposing one company's info to the other companies if they are all in the same database, not to mention the same table.  If I was me, each company would have it's own database.

Author

Commented:
i was going to do checks based on the logged on users id number so that only data relating their company is then displayed,

What would be your guys best option for easy updates to all the systems without spending days doing manual upgrades to each installation when new features are added?

Regards
Ben
Most Valuable Expert 2011
Top Expert 2016
Commented:
Use separate data bases for each company.  You can create data bases (assuming your PHP script has permissions) probably up to some number of data bases, depending on your hosting plan.

If you use separate data bases, you have saved yourself a huge amount of work with tuning and load balancing.  What if a company did not pay?  You can suspend a company's service by simply renaming a data base.

It is really easy to do this.  After your connect to the data base engine, you select the data base.  That's where the company and data base get hooked up.  If you never use the data base name after that point, the scripts will assume that you mean to use the last data base you selected.  Easy!

If you do not already know how to do this, you might want to get a skilled DBA to look at your data base and table structure before you get too far down this road.

Best regards, ~Ray

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial