How can all users share one table in the database, but each user's order number has an independent namespace?

1. Actual scenario:
The order table in the database is public, and all users are on one order table.
How to make each user’s order number an independent namespace? For example, if user A places two orders, his order numbers should be C001 and C002, and if user B places three orders, the starting position of his numbers should still be C001, not C003, and all his order numbers should be C001. C002,C003.
How to do this?
Is it necessary to save a field under each user for how many orders he has currently opened, and then read it out first, add one, and then save it back? This is not good... Is there a better way to do it on the database side? I seem to have heard someone talk about using database functions or triggers?

2. If the database can do the above, does this C001 generally exist in the primary key field? Or is the primary key still an incremental primary key of numeric type, just adding a field to the table to store this C001?

Note: It is in the Mysql database.

Reply content:

1. Actual scenario:
The order table in the database is public, and all users are on one order table.
How to make each user’s order number an independent namespace? For example, if user A places two orders, his order numbers should be C001 and C002, and if user B places three orders, the starting position of his numbers should still be C001, not C003, and all his order numbers should be C001. C002,C003.
How to do this?
Is it necessary to save a field under each user for how many orders he has currently opened, and then read it out first, add one, and then save it back? This is not good... Is there a better way to do it on the database side? I seem to have heard someone talk about using database functions or triggers?

2. If the database can do the above, does this C001 generally exist in the primary key field? Or is the primary key still an incremental primary key of numeric type, just adding a field to the table to store this C001?

Note: It is in the Mysql database.

1: There is no need to store them in the database like this. You only need to traverse all the orders and display them in order.
2: Add static member variables to each user attribute, and add corresponding fields to the database. The data added each time will be what you said.

What the poster means is to establish a unified order management library for different customer accounts. Different accounts can only see their own orders, which is somewhat similar to cloud ERP.

The order table can have a redundant primary key id. In terms of data storage uniqueness, it can be viewed as order code + user id to uniquely confirm a piece of data.

If you need to optimize the design of the table structure, you can automatically create an order table according to each user, and separate the order data of each user. The table name is similar to order_userid. Each time the corresponding table name is found according to the user, the data is fetched, but at the end The comprehensive background statistics of all user order data may be a little troublesome.

How can you ensure the uniqueness of your order

If the database is designed according to the meaning of the title, only the operation of finding orders will cause an exception. Because we cannot determine the unique order based on the order number.
The value of the primary key is used to uniquely identify a record in the table.

(About your project
I suggest:
The primary key of the Order table can be OrderId
And the primary key of the User table can be UserId
I think your problem should lie in the basic understanding of database tables. It is recommended to read Database Normalization related content )

1. Use an orderid table to store the IDs of all users' next orders. If you find it troublesome, it is recommended that you use time as part of the order number.

2. Cannot be used as a primary key. There is a principle in primary key design - it must be irrelevant to the business. Therefore, the primary key will always be a meaningless auto-incrementing ID or the like.

I don’t see what actual need your question is trying to solve

The design of table structure must comply with the requirements of the three most basic paradigms. The purpose of the three paradigms is to ensure that there is no redundancy or dependency between fields. I won’t repeat the issues mentioned above. Your design like this violates the principles of the three major paradigms of database table structure design. I suggest you clarify your requirements carefully. You should not do such a design, or we have misunderstood it. You rephrase the question. Or directly post your table creation statement.

You can use a joint primary key to combine uid and cid, which ensures uniqueness. But please pay attention to the following two points:
1. To use mysql joint primary key auto-increment, you need to use MyISAM as the storage engine.
2. When using a union to auto-increment the primary key, the auto-increment key cannot be the leftmost key of the primary key.

Just add a user_owner_order_key to the order table, and then write your own code to handle the exchange logic between C001 and the real order_id.

<code>//伪码 
$user_owner_order_id = (select count(*) from order where user = $uid and order_id < $currentOrderId order by order_id asc) + 1;
$user_owner_order_key = $uid . fillZero($user_owern_order_id);</code>

To talk about another issue, generally third-party payment cannot make multiple payments for the same order number, so it is best to consider carefully whether to do so.

Solution

1. The user table can have a field to store the order quantity. Every time there is a new order, add one to this value and then update it back;

2. The new order gets the number of existing orders, plus one. However, when deleting the order, it must be soft deleted