imagine a database setup, where x customers can make y orders.
normally all customers would share one set of auto incremented order IDs.

* the first order of customer A has order id #1
* first order of customer B has order id #2.

i'd like to have individual sets of order IDs for each customer. it is important, that customers cannot see based on the id how many other customers have made orders.

* first order of customer A has order id #1
* second order of customer A has order id #2
* first order of customer B has order id #1

right now, the only way i can see would be to manually select the max value for the customer's order number, add +1 and then insert it manually.

```
select max(customer_order_id) from orders where customer_id = X

```

how can i create this approach in a sense of data integrity and normalization? like having some kind of auto increment for the individual order ids of the customer?

```
create table customers
(
  id int auto_increment primary key, 
  name varchar(255)
);

create table orders
(
  id int auto_increment primary key, 
  customer_id int
  customer_order_id int 
  foo varchar
  bar varchar
);

```