One table, auto-increment for each user of your own?

It is better to write out the question on the example:
1. Need a table with orders (it is common for all users of the system)
id int(11) auto_increment - auto-increment for all orders
id_user_creator int(11) ID of the user who created the order (the same user can be user_creator and user_for)
id_user_for int(11) ID of user for whom this order (the same user can be user_creator and user_for)

2. And then that kind of logic, each user has two tabs (inbound and outbound), depending on whether he created the order or someone else has created
3. There he sees the numbering of the orders is not a common field (id), although it is also visible, but purely for communication between two users.
4. And we need another internal numbering to within the company not to operate a total order numbers (they can be very long and they sure will jump in numbering: 12, 345, 6578), and to work with their internal numbers (1, 2, 3)

and here plugging, the simplest solution is for each user to have a table of orders where to write the common order is still here. I.e. 3 of the table: General orders, and with each additional order is entered one record in the table each participant to give them their internal numbers

and it is possible to solve within a single table or am I a dreamer?
July 9th 19 at 13:07
2 answers
July 9th 19 at 13:09
Solution
It is possible SQL request to increase through update
Well, as an option, but how to track that accidentally two bids are not received the same room. If only this field to deliver UNIQUE then the curse will be, even if that will be possible to try again to find the next number and change - jaiden_Nitzsche commented on July 9th 19 at 13:12
This structure was invented

id int(11) AUTO_INCREMENT,
customer_project_id` int(11)
customer_internal_id` int(11)
supplier_project_id` int(11)
supplier_internal_id` int(11)

PRIMARY KEY (`id`)

UNIQUE KEY `customer_project_internal` (`customer_project_id`, `customer_internal_id`)
UNIQUE KEY `supplier_project_internal` (`supplier_project_id`, `supplier_internal_id`)

1. Field id - total order number in the system, each participant will be able to call to can communicate with the customer and the contractor
2. Customer_internal_id supplier_internal_id field and internal order numbers inside each project customer_project_id, supplier_project_id to the inside of the project staff could communicate in their inner rooms (short, numbering in order) and not General (as they will be long and will be jumping)
3. The unique composite field will not add an entry with the same internal number for a single project

well, left to make a table for all projects where to store the project number and the last number of the order to him to add one and update the numbers in the total order

I do not think - jaiden_Nitzsche commented on July 9th 19 at 13:15
July 9th 19 at 13:11
Yet I see only the solution with separate tables for each user
table_USER ID
id int(11) auto_increment
order_id int(11)

which simply will keep all external orders and to auto-increment to do the order numbers for internal use

Find more questions by tags Databases