Can I create a SQL database schema where instead of having primary keys, I create new tables for each scenario ? What are its disadvantages?

Question

For example - If I need 3 tables for my app - A,B and C.

When creating a new scenario/record instead of inserting a new row, I create a set of 3 new tables - A2,B2,C2

Next time when I create a new scenario, I create set of 3 new tables A3,B3,C3 and so on.

What are the disadvantages to this approach?

Answer 1

Here are some disadvantages that come to mind:

• The number of tables rapidly grows.
• SQL databases are designed for relatively few big tables rather than zillions of small ones.
• The tables (if they are small) are likely to have lots of wasted space -- half filled pages.
• Queries need to be modified to access each set of tables.
• It is quite difficult to answer questions such as: "How many rows in A for each scenario".
• If you decide an optimization is needed -- such as an index on a table -- then you have to repeat the operation a zillion times.

I'm sure there are more reasons. Those are just the ones that come to mind right now.