I have a table of dentists and services. And I want it to be dynamic so I have added a "add services" function. but how can I normalize it if services are not defined yet? since I didn't add yet.

the solution I've made is that I created a new table which is specialty where it creates new column every time I added a new service. But I dont know if it's very improper to create a new column within the add services function itself. But that's the only way I think. Are there any ways to solve it?

CodePudding user response：

Dynamic columns are not good at all! Tables should be static to be reliable.

Use relation-tables instead.

Example

You have a dentists table. It has an id column as primary-key.

Create a services table. Of course it should have a primary-key. besides that, put a dentist_id column. This will contain the id of the dentist in the dentists table.

If you provide more information and code or database schema, we maybe able to help more.

EDIT

As ADyson mentioned, if it is possible that multiple dentists work on one service, do a many-to-many relation.

Example

You have a dentists table And a services table. They both have an id column as primary-key.

Create a r_dentist_service table. Of course it should have a primary-key. besides that, put a dentist_id column and a service_id column. These should contain the respective IDs. This table will relate dentists to services.