the same field in multiple tables

I have the following tables: trainers, trainees and health professionals. I need to incorporate the mailing list开发者_JS百科 field into each of them to flag those people who want to receive our newsletter.

Is it ok to have the same filed in each of these tables, or there is a better way to resolve this issue?

Many thanks, Zan

You should probably have one single people table, which contains mailing_list. Then if you have different data that needs to be stored for trainers, trainees etc, hold this in separate tables and make them joinable through the use of a foreign key.

I would try to avoid duplicate the data as much as possible,

for the mailing list, here how I would do to avoid having to repeat it in different tables with Foreign keys

|     trainers    |    |     trainees    |  |   Health_prof   |
|_________________|    |_________________|  |_________________|
|       ....      |    |       ....      |  |       ....      |
| mailing_list_id |    | mailing_list_id |  | mailing_list_id |

and a table mailing list

| mailing_list    |
|_________________|
| mailing_list_id |
| all orther infos|

in the case your persons can be registered to multiple mailing list I would use a third table to make the link between the people and the mailing list with the corresponding ID's as rows, so to register, unregister you would just have to insert/delete rows in this table

Assuming that there is common information (such a name, gender, date of birth, email address, etc) for trainers, trainees and health professionals, you would want to store that information in a separate table, say person_info.

If you plan to have only 1 newsletter or very few types of newsletters (e.g. site updates, offers, etc), then you could store that info as one combined or one-per newsletter type in the person_info table.

But in my experience, the newsletter system should be its own set of tables in which the subscriptions are stores as rows, more like EAV rather than as columns. This reduces the amount of DDL needed when a new newsletter type is required by the business folks.