I am building a web application (php/mysql). There will potentially be many users (managers). There is a table (let's call it "gurus") into which a manager/user will enter lists of gurus. These lists are used 1 time and then cleared out so the manager/user can start a new guru list. My instinct is to create 1 table that includes a userid field that will link the gurus to the manager/user that entered it. This guru table has an auto-increment ID field in it also. The other alternative would be to create a new table for each user (but I am guessing that's a bad idea).

If I stay with 1 table, is there any need (or way) to "reset" the auto-increment field in the guru table? Will it ever break if it's not able to continue auto-incrementing?

I hope this is clear and thanks for any guidance.

  • "but I am guessing that's a bad idea" -- Yes, it is. "Will it ever break if it's not able to continue auto-incrementing?" -- Is it that likely that you get that many entries?
    – sticky bit
    Jan 7 at 1:36
  • I think the best idea might be to get rid of the auto increment field.
    – Bee Tee
    Jan 7 at 1:59

Since the combo of guru and entered_by is unique, PRIMARY KEY(guru, entered_by), not an auto_increment.

To remove all the gurus that I entered, consider something like

DELETE FROM t WHERE entered_by = 'me'
  • The combination of guru name and user/manager name that entered it is unique. But the actual guru name could be duplicated. I don't want to truncate table as that will wipe out gurus entered by ALL user/managers. A particular user/manager can decide to delete the gurus only that they have entered.
    – Bee Tee
    Jan 7 at 14:07
  • @BeeTee - I rewrote my answer.
    – Rick James
    Jan 7 at 17:03

Where do these "Gurus" come from?
If a Guru can exist on its own, without association with any other Entity, then it should have its own table.
Presumably a Manager can also exist without reference to anything else, so that's another table you'll need.
To create a list, you'll need to associate the Manager with a number of Gurus. Now; can a Guru appear on more than one Managers' list? Let's assume they can.

create table Managers 
( man_id int auto_increment ... 
primary key ( man_id ) 

create table Gurus 
( gru_id int auto_increment ... 
primary key ( gru_id ) 

create table GuruList
( man_id ...
, gru_id ... 
, sequence int 
primary index ( man_id, gru_id, sequence ) 
foreign key ( man_id ) references Managers ( man_id )
foreign key ( gru_id ) references Gurus ( gru_id )
  • It's more like this... each day, each manager will create a new list of gurus. They will assign some values to the gurus and report on it. Then, repeat the next day. They do not want to retain previous day's lists or data. So, when manager A is ready, they will clear out (function to delete all gurus associated with their manager ID) their list and start over. There is never any overlap between manager's lists. I have thought of ways to do it but I am looking for best practice in handling this situation.
    – Bee Tee
    Jan 7 at 14:33
  • Do they have /brand new/ Gurus each and every day and /never/ want to reuse the same ones on more than one day? Sounds [to me] like a pretty exhausting exercise for them to carry out every day. No overlap between lists? How would you /know/ there was no overlap unless you had a separate list of [all] Gurus from which they could draw?
    – Phill W.
    Jan 7 at 16:25
  • Let me give a different example that is closer to what I am trying to do.. Each day, the manager is holding a raffle for whatever gurus show up. He enters their names in as they arrive, and a web page shows the list of guru entrants. Once there are no more guru entrants, a raffle winner is chosen at random from the list of guru entrants. Now the next day, we don't know (or care) if any of the same gurus have entered. We just want to start from a clean slate for each day's raffle. All the while, there are many managers holding raffles each day, each needing their own list of gurus.
    – Bee Tee
    Jan 7 at 16:48
  • I would suggest that you still need to uniquely identify the Gurus, otherwise there's nothing to stop a[n unscrupulous] Manager from entering the same Guru many times in each day's draw! Also, you say that each Manager needs "/their own/ list of Gurus" - without uniquely identifying each Guru, you cannot tell if the same one appears on more than one list, which this statement appears to preclude. All that said, Guru Name might be a "good enough" Key (along with the Manager's unique id). With the right indexing, a Gurus could appear only once each day.
    – Phill W.
    Jan 10 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.