Welcome Guest, Not a member yet? Register   Sign In
ID incrementing for multiple users - please help
#1

[eluser]phester[/eluser]
I'm trying to create an invoicing application (similar to many out there) that would users to issue invoices to their customers.

This is what I'd like to have:
Each user signs up for an account. And can start to issue invoices. Invoice IDs should start at 1000 and auto increment sequentially for each of their invoices from there.

My dilemma however, is how can I have each user start at 1000 and have these IDs sequential for all THEIR invoices only? The only way I can think that is to have separate databases for each user, but I dont like this idea! I'm sure there's a better way out there.

Another way to do it is to have an ID integer field and manually increment it so that the IDs for their clients are sequential.

Any help is greatly appreciated. I hope you get what im describing.
#2

[eluser]Alexxz[/eluser]
I think you can make different tables for users, but not databases.
And I think it is not good decision to use this way.

You can make one sequence for all invoices and manually add their second ("usered") IDs. It is really good way to make that.
#3

[eluser]ejangi[/eluser]
Yeah, you could have separate tables for each user and set the AUTO_INCREMENT value to start at 1000.
Code:
create table user_###
(
id MEDIUMINT NOT NULL AUTO_INCREMENT,
....
) AUTO_INCREMENT=1000;
#4

[eluser]Michael Wales[/eluser]
or just manually increment it

Code:
update invoices set num = (SELECT num FROM invoices WHERE user_id = 1 ORDER BY num DESC LIMIT 1,0) + 1 WHERE user_id = 1

Not sure if that is valid SQL - look up subqueries in the MySQL docs.
#5

[eluser]Sarfaraz Momin[/eluser]
All the suggestions seems to be good but what I will do in this is have 3 tables. One for invoices, second for invoice IDs which will be have 2 fields, one will be the primary key from the invoice table and other will be the real invoice id prepended with the username like for user 'aaa' it will be 'aaa-1000' and so on. It will be linked to the invoice table on the primary key. Third will be to keep a record of the next invoice number for the user so you don't have to do a check on the invoice id table and keep searching the last invoice number.

Hope that makes sense.

Good Day !!!
#6

[eluser]Alexxz[/eluser]
Be careful.

Code:
select nextID from thirdtable;
update thirdtable set nextID = nextID + 1;

This must be in transaction. Or another method to except this

Code:
1> select nextID from thirdtable;
2> select nextID from thirdtable;
1> update thirdtable set nextID = nextID + 1;
2> update thirdtable set nextID = nextID + 1;
#7

[eluser]wiredesignz[/eluser]
Store the next invoice number for each user in their account details table and manually increment it.

Store all the invoices in a common table, index each invoice number with its owner account id + unique id
#8

[eluser]phester[/eluser]
These suggestions area all really good. But I thought there would be a more elegant solution that will not require me to manually increment. Holding separate tables or modifying the DB for each new user is not too elegant either in my eyes.

Thank you all for your help. But is there a more elegant solution out there?
#9

[eluser]marcoss[/eluser]
Define elegant.
#10

[eluser]tonanbarbarian[/eluser]
You might want to consider a whole new take... or you might not

Anyway...

How about instead of having invoices IDS as a sequential number for each user what about a timestamp approach. Have the ID be the date time the invoice was issued. i.e. Y-m-d H:iConfused

Invoice ID: 20071209232547
Invoice ID: 20071210105423

Not only does it allow you to see what order the invoices are issued, it allows you to immediately see when they are issued.
And if for whatever reason an invoice needs to be backdated it can be without having to give it a high invoice number.
Sorting will show the the invoices in the order they were created etc.
And of course no real code needed to generate the invoice id or process in any way, just insert the datetime or use a timestamp field.




Theme © iAndrew 2016 - Forum software by © MyBB