Assistance with Table structure (1 Viewer)

I am building a table with medical providers. Every provider will have one or many specialities and zero or many sub-specialities.

Should I build the Provider Table with a column for every single possible speciality and sub-speciality with a checkbox? It is possible that we will have to add more specialities in the future. I'm not sure how to approach this..

Thanks in advance

Should I build the Provider Table with a column for every single possible speciality and sub-speciality with a checkbox? It is possible that we will have to add more specialities in the future. I'm not sure how to approach this..

Click to expand...

No. Google 'normalisation' to understand how data is organised in databases.

Simplistically, data should be stored once and you should not use the structure to define content - such as a column for each speciality. Otherwise when you add another speciality you will need to modify the table and all subsequent queries, forms and reports.

so in your example you might have four tables, one for provider, one for speciality, another for subspeciality and a 'many to many' linking table between provider, speciality and subspeciality. I'm assuming subspecialities can only belong to one speciality, if that is not the case you will need another many to many table to represent that relationship as well.

Note that normalisation is important, but is not a totally defining rule - that rule is defined by your requirements and process, which you have not clearly defined - such as can a provider have more than one subspeciality within a speciality, or does choosing a subspeciality define the speciality, etc

This was useful. Thanks a lot!

Just one more question.

Yes Sub-Specialities can only belong to one Speciality.

How is it going to get registered the specialities and Sub-Specialities under each Provider when we don't know beforehand how many are there going to be?

Same thing with languages. I will have a table with a list of languages and every provider will be associated with one or more language. How do you manage that variable when you don´t know how many? I am interested from the table perspective, but from the form structure as well

as suggested, google normalisation, you will find plenty of examples. You need to learn the principles so you can ask properly formed questions and understand the answers.

but a quick answer is one row per speciality, and in another table for languages, 1 row per language - like a list. You can have millions of rows.

If you have an excel background - throw all that away. Excel stores data horizontally, databases vertically. Excel combines data storage and presentation in one view, databases stores data in tables and you use forms and reports for presentation.

Be aware that access is two systems, a database for tables (known as a backend) and a presentation gui to create forms and reports and manage data (known as a front end). The access front end can just as easily be connected to a non access backend such as sql server, mysql, oracle. Even excel, csv, xml files etc.

This site shows examples of data models for different industries.
http://www.databaseanswers.org/data_models/

Thanks a lot both!