Multiple increments on one table

[eapro]

Each time a new person is added, they will get their own id starting at 1, and each time their name is added, their id will increment by 1.

To add a new entry, I would have to first lock the whole table, then get the last id for that person, then do another query to insert with id + 1.

How can I achieve this without creating a separate table for each person? Any ideas?

NOTE: These example tables are ONLY to give you an idea of what I'm trying to achieve as simply as possible.

Showing all names
+--------------+-----------------------+
| id | name |
+--------------+-----------------------+
| 1 | Bob |
| 1 | Marry |
| 2 | Bob |
| 1 | John |
| 3 | Bob |
| 2 | Marry |
+--------------+-----------------------+


Showing only where name = Bob
+--------------+-----------------------+
| id | name |
+--------------+-----------------------+
| 1 | Bob |
| 2 | Bob |
| 3 | Bob |
+--------------+-----------------------+

[Old Pedant]

I think this may be a mistake.

I think you should have *two* tables.

In the primary (USERS??) table, BOB gets an AUTO_INCREMENT id value.

Then in the secondary table, you add "instances" each with a foreign key to the primary table and a secondary sequence number.

Now, it's *still* possible that two different people may be trying to insert "BOB" records at the same time. Yes, you'll need to protect against that, but you could do that all in a Stored Procedure, perhaps.

Having said all of the above: I think you *could* do this with a single table without locking.

INSERT INTO table ( id, name )
SELECT MAX(t2.id)+1, t2.name
FROM table AS t2
WHERE t2.name = 'Bob'
GROUP BY t2.name

[eapro]

Old Pedant, that works perfectly except if the person (Bob) doesn't already exist, it saves id as 0 (zero) I need id to begin from 1

[Old Pedant]

I don't understand why that saves at all, given the query as I wrote it, if the name doesn't already exist. Should have gotten no record at all.

I think you must have altered something.

But never mind, I misunderstood the problem. I assumed that you would know if the record existed or not.

I assume that this must be a highly concurrent system? Tons of people altering it all at the same time?

[bazz]

Each time a new person is added, they will get their own id starting at 1, and each time their name is added, their id will increment by 1.


How do you add a person, without using their name? Unless I totally misunderstand, you need one table


create table users
( id int auto_increment primary key not null
, last_name varchar (99)
, first_name varchar (99)
) .....


You'll need to look into indexing and normalisation too.
hth

bazz