Just like how Mac or Windows gives incremental names to new folders, how can we implement such a system in a database?

Let's say I have currently 3 folders (default names) i.e., "untitled folder", "untitled folder 2", "untitled folder 3". Now if I delete "untitled folder 2" and try to create new folder again "Mac" will name the new folder as "untitled folder 2".

  • 1
    In.. what software? This clearly needs more detail, it's unclear what is even creating the folders
    – talfreds
    Aug 4 '21 at 18:15
  • 1
    Hi @talfreds, this is a System design question. I am looking for generic approach not some platform specific answers. Thanks
    – user16595023
    Aug 4 '21 at 18:18
  • I guess I will let others answer then, because as I see it there are numerous design patterns that could achieve this.
    – talfreds
    Aug 4 '21 at 18:43

One important aspect is how critical it is that folder names are never repeated.

Common sense would suggest you have some logic that, when asked, tells the caller what the next folder name should be - or even just what the next number should be.

For that to work, and never repeat a number, it would need to keep a record of what numbers it has issued in the past. That means it also needs to maintain state, state which helps the logic know what the next number should be based on the last one it gave.

So the folder creation sub-system would probably have two parts: something that just worried about what the next number should be, and something that took whatever number and used it to return the folder name. E.g. string.format("{0}{1}", sFolderName, iUniqueNumber)

Regarding Unique Number Creation

One approach would be to maintain a ledger, if you imagine a database table called tblNeverToBeRepeatedNumber with a single column: int NeverToBeRepeatedNumber. Every time the logic is asked to issue a new number:

  1. Look up last NeverToBeRepeatedNumber value in tblNeverToBeRepeatedNumber.
  2. Add 1 to it, store that value in the table, and (assuming the transaction is successful) return it.

Another approach is to just have a single row somewhere, say in a key-value pairs config table, which just act as a counter... so same approach as above but you're updating the number, not adding a new one.

The latter approach uses much less space and will be more efficient over time as the table size won't grow as much (assuming you issue way more folder numbers than you have config key-value pairs. The former approach would allow you to add columns/data such as when a number was issued, etc.

The first approach also works for unique values that aren't integers - you could store unique GUID's if you wanted to. the only thin gthat is really different is how the logic calculates what the next value is, based on whatever rules you design.


The approaches above have no idea what the folder state actually is (i.e. what happens to the values that get issued - the folders or whatever). If the logic issues #647 (and the calling logic then created Folder 647), and then a user comes along and renames it to Folder 648, what happens when the logic gets called again, and will want to issue #648?

You can try and keep across folder state by have a watcher or something but then you're trying to coordinate two separate things - the unique numbers and folders in reality - that's not easy to do, especially reliably over a long time.

Consider the system qualities that are needed, and factors like the volume of calls for new folders you expect, including peaks/bursts, number of callers, etc.

You will need to be aware of how the logic routine is called, so that you don't turn the whole thing into a bottleneck.

Developing this and getting it working is easy - what's going to be much harder, if you're seeking a high level of robustness, is testing all the failure cases.

Thinking about performance: Lets say your are making folders in the file system. I'd be ready to believe that generating the number could be quite quick (especially if the current numbers are managed in memory, with a [Write Behind][1] pattern to the database / number register) but the actual folder creation could be relatively longer - so, do you need to develop it in such a way that callers get the number really fast, without having to wait for the previous caller to get it's number and then spend twice as long making it's folder?

[1]: https://stackoverflow.com/questions/27087912/write-back-vs-write-through-caching#:~:text=Write%2Dback%20(or%20Write%2D,and%20the%20back%2Dend%20storage.


There is a very simple approach which works well for small numbers of similarly named things (pseudocode):

name = intended_name                   // the first name to try is simply the intended name
index = 1
  if already_used(name):
    index = index + 1
    name = intended_name + " " + index // this will try names with 2, 3, ...
    return name

However, without more detail and clarity it's not decidable whether this meets your requirements. "Requirements by example" is a bad approach to getting a good solution, you need to work on writing a clear, complete and unambiguous specification so that it is possible to devise a solution that meets the specs.

Your Answer

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