Mysql – Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

Stand on the shoulders of your predecessors.

If I use duplicate tables is that a scalable way for a website with millions of entries and much more revisions ?

"Duplicate tables"? Plan A: one table with all revisions. Plan B: 2 tables, one with the current value, one with all the previous revisions. Plan C (really bad): One table per revision.

Which of the two database design will be better ?

Once you have gone through the exercise of defining "better", you will be halfway to the answer.

How to change the schema in json based audits if schema changes ?

This sounds like a different dimension to the problem -- tracking of schema changes. That, itself, is a very big task. JSON has no problem adding/dropping extra "fields" as a schema changes. However if a table is split, then JSON gets trickier. You would probably need special code bridge the gap.

Bottom Line

Since you seem to be just starting in this endeavor, I suggest you flip a coin to decide which one to use.

But... Plan on revisiting the decision in 6 months. By then you will have enough experience with the code to have some feel for whether it will work for the direction you want to take it.

Sure, it will be painful (very painful) to switch after 6 months. But it will be 'impossible' after 12 months. You will probably decide to patch the chosen schema instead of switching to the other schema.

Or maybe you will spin off another company to use the other database schema. A prediction: You will find that the "grass is not greener" and the second company will fail.

Having the same types of columns is not the same thing as being the same entity type. Normalization is not the same thing as code reuse.

Normalization is about arranging your columns into tables in such a way that you avoid insert, update and delete anomalies. It is about reducing the kinds of redundancy that can lead to these anomalies, it isn't about putting things together because they look alike.

As a first choice, you should let your database engine use its declarative constraints (foreign keys, unique keys, etc.) to protect the integrity of your data. This saves writing application code to do this and makes your system less buggy in the long run.

This means that you should aim for at least third normal form (3NF) by default and then consider later whether any denormalization is necessary.

From this perspective, your first option is not good, because you are jamming three different kinds of child data into one table. You can't control referential integrity as cleanly or easily this way. The only two conventional, practical options you have are 2 and 3.

Choosing between options 2 and 3 depends on the circumstances, and may depend quite a bit on your personal preferences. Some people would avoid option 2 because they believe all nulls are evil. Other people would avoid option 3 because they don't like having too many different tables. This is a "holy war" debate without a definitive, logical answer.

For practical purposes, looking at your three main tables, I would say that they don't look like they have very much in common. There is a little overlap, but not very much. Therefore, I would tend towards option 3. Keep your tables separate so that your code and your data stay clean.

As an aside: You don't show the details of the child table(s). If these three child tables have an identical structure and you're worried about having application code that is duplicated, consider creating a class that handles the child table structure and inheriting from this class for each of the three child tables. This will give you code reuse without compromising data integrity.