Advice on database structure/layout (inventory of practitioner types and specialties)

[Grek]

Hello

I'd like to build a simple and easy to use database in MS Access (or an online tool like Ninox or something else).



The goal is to manage a list of hundreds of health practitioner activity types (+ short and long description), specialties linked to each activity (a specialty can also apply to more than one activity), and benefits linked to each activity (a benefit can also apply to more than one sectors). I'll need to manage the data in 5 or 6 languages.

I'm here to find some help to get started. Based on your experience, how would you recommend me to structure the tables and visual interface?

Many thanks,

Example 1:

Activity: Yoga Instructor
Short description: Blablabla
Long description: Blablabla
Specialties: Nidra Yoga, Hata Yoka, Reiki Yoga, XYZ Yoga, etc. (can be up to 20 or 30 benefits)
Benefits: reduce stress, improve quality of sleep, etc. (can be up to 20 or 30 benefits)

+ translation in foreign languages

Example 2:

Activity: Massage Therapist
Short description: Blablabla
Long description: Blablabla
Specialties: Back Massage, Deep Tissue Massage, Foot Massage, Thai Massage, Head massage, etc. (can be up to 20 or 30 benefits)
Benefits: reduce muscle tension, reduce stress, improve quality of sleep, stimulation of lymphatic system, etc. (can be up to 20 or 30 benefits)

+ translation in foreign languages

[isladogs]

You need to think through logically what setup this means in practice and design your tables accordingly.

As I understand it, each activity will have many related specialities and many related benefits. Each benefit will not necessarily relate to a set speciality and can relate to more than one activity.
Similarly each speciality may relate to more than one activity.
This means you have many-many joins between 3 at least separate tables: tblActivities, tboSpecialities, tblBenefits and to handle that you need Junction tables each linking two of those tables e.g. TblActivitiiesBenefits each with a one-many join.
Put your two description fields in tblActivites if there will only be one of each description per activity.

Surely you also want to have a table tblPracitioners each of whom may offer many activities so that's another table with a one to many join

Whatever you do, avoid any temptation to use multivalued fields or you will regret it later

[CJ_London]

agree with Colin's comments.

I would add that to manage multiple languages adds a further layer of complexity. Managing translations for 'Massage Therapist', 'reduce stress' etc if pretty straight forward as it is data - just have a translation table you can use to look up 'Massage Therapist' with a language key. But translating labels such as 'Activity','Benefits' requires more work on the form/report design.

[isladogs]

Thanks Ajax. I forgot about the languages part of the question.
It really depends on which/how many fields would need translating into several languages but I would also go for a separate translation table

[Grek]

Thanks for your advices!