0

I have to design a database for a training system that our clients will use to provide trainings to their respective users. Initially for storing trainings data I thought I would use a table like this:

tbl_trainings, actual training data would be multiple columns, here I've used training_data to keep it short

training_id training_data client_code
1 training name, dates, etc. client_one
2 training name, dates, etc. client_two

Now, I think we might run into problems in future with this approach to visualize the data, Might not be true but that's how I feel.

Another approach I'm thinking of using is to split the tbl_training for each individual client.

tbl_client_one_trainings

training_id training_data
1 training name, dates, etc.
2 training name, dates, etc.

AND

training_id training_data
1 training name, dates, etc.
2 training name, dates, etc.

Is it okay to split it like this? If not, what problems would I run into in future or even while developing? and Is there a simpler way to achieve this.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Akash Sarode
  • 387
  • 2
  • 13

1 Answers1

0

No, it is not okay to split each client's data into separate tables. It is a maintenance nightmare! Plus databases are not designed for this.

Here are some reasons that come to mind:

  • Databases work more efficiently on large tables than small tables. Your per-client tables will end up with a bunch of partially filled data pages, which increase the size of the data and slow down the database.
  • If you decide to add an index, you have to repeat that operation for every client.
  • If you decide to add a column or change the type of a column, you have to repeat that operation for every client.
  • If you want to answer simple questions, such as "How many clients do X", you have to bring together all the tables. A nightmare!
  • And the code for such a question that worked yesterday may not work today, because new tables have been added (or removed).

What you are modeling is are trainings per client. That belongs in a single table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I use phpmyadmin to check the data, maybe that's why It felt overwhelming. But in another similar system we have a few hundred thousand entries per client and it's split in the same way and I believe with ORM there's a way to create/update tables on the fly so I thought why not use the same approach. So do you suggest keeping single table even if entries were to cross a million. – Akash Sarode May 22 '21 at 11:12
  • @AkashSarode this answer approaches your question purely from a technical angle. However, your question indicates you have multiple tenants, in which case traditional database design patterns are just one of the many aspects you need to consider. Pls see the answer to the duplicate question that go beyond not to store the same data in multiple tables principle. – Shadow May 22 '21 at 11:27