Mongodb – What mongodb database layout would fit better

Assuming you are using ObjectIDs for your _id column, then you will already have an insert based timestamp. You can even extract it from the shell directly if you wish. Of course, you may want to leave this as-is to permanently store your original insertion timestamp, but adding another would be relatively trivial. Because most drivers have the ability to generate their own ObjectID and you have the published spec you can easily insert your own with a little research, and in the language of your choice.

Since ObjecIDs are the default _id value, they are also going to be a good choice for indexing, well supported in the drivers etc.

Alternatively, in terms of implementing pure timestamps in MongoDB, there is also a BSON Timestamp data type.

Storing this (or the aforementioned ObjectID) in a standard field (say "ts" for example) across documents/values would add overhead to a field/doc as you mention, but you should be able to standardize it easily (field.ts would always have a timestamp value) as well as predict the overhead for each field with a timestamp (8 bytes for a timestamp, or 12 bytes for an ObjectID - for more info see bsonspec.org).

Given that you have two native, known datatypes that provide timestamp functionality, they would be my recommendation as a way forward here.

In terms of adding them to existing data, you can choose to "lazily" add them whenever the data is next touched or issue a batch update, depending on your needs - the benefit of a flexible schema.

"Is this realistic to do with something like MongoDB?"

If "realistic" means can be engineered to produce acceptable performance then the answer depends on whether you will do with appropriate benchmarking before putting it into production. You can replace "MongoDB" in your question with anything from Access to Oracle and the answer remains basically the same.

The MongoDB documentation has some good information on considerations for data modeling and performance. I would start there and lean towards a "collection" per each of your original source tables.

The important step is to benchmark, benchmark and benchmark. I would approach this from the standpoint of proving that MongoDB can't handle the workload. There are three possible outcomes:

1. You prove that it won't work. Maybe it's due to technical limitations or it would require excessive HW to meet your SLA. If they proceed you can't be held responsible ("I told you so").

2. You fail prove that it won't work and after it's in production it doesn't work then at least you tried. Everyone is surprised and there is a big scramble for an alternate solution.

3. You fail to prove that it won't work and it works great. You will sleep good at night feeling confident in the solution forced down your throat.

Start by benchmarking the "critical paths". If you can't get MongoDB to handle the incoming click traffic then it's "case closed". Start with an aggressive goal, maybe 300% of what business users project traffic will be. Figure out how many documents/sec you can load with your initial schema. Is it reasonable? Change the schema and benchmark again. Is it better or worse? How does it scale with additional HW? Try scaling UP and OUT. Try multiple clients performing inserts. Next look at the updates. Can you meet your performance goals?

Lastly look at reporting. Reporting performance is typically an easy problem that is solved by additional hardware. If the reports are ran every day or so then you should have some leeway in expected performance.

Conclusion

There is nothing special about MongoDB compared to the evaluation of any other database technology. Start with technical requirements and benchmark, benchmark, benchmark.