allow multiple values - should I? (1 Viewer)

[jmlight]

I am helping a non-profit set up a database of resources for their clients. The ultimate goal is for a client to come in, state their need (eg. immigration lawyer), and for us to to be able to pull up a listing of lawyers (or pediatricians or....)

Some resources have multiple areas of specialty (immigration, criminal, civil, divorce for lawyers...or pediatrics, geriatric, kidney etc. for doctors)

I was thinking of allowing the person entering the data to store multiple values in the "specialty" field for the resource - but am worried about the design.

If multiple values are stored, will that prevent us from being able to run a query and pull up all the Lawyers who have immigration as a specialty?

Would it be better to have a resource guide that has 10 entries for a resource if the resource has 10 areas of specialty?

Seems so nice and easy for the data entry person to just pick from a combo-box for ALL the specialties at one time..

But, don't want to save time now only to have to re-do everything later..

Suggestions?

 

[RuralGuy]

In order for your tables to be normalized you will need one for the Professional, one for the occupation or talent, and another that puts the two PrimaryKey fields together in a record for the M:M relationship.

 

[jmlight]

so...

Table1 - Doctors Name and Address with an ID for the DR.
Table2 - List of Medical Specialties with an ID for the specialty
Table3 - two fields - the Dr ID & the Medical ID

Is that right?

 

[RuralGuy]

Bingo! Right on the button. Use an AutoNumber for the PrimaryKey fields and a LongInteger for the ForeignKey fields. Name the ForeignKey fields in table 3 the same name you use where they are a PrimaryKey. It make it ia lot easier to understand and maintain later.

 

[jmlight]

Got it...will give this a shot on Monday!

 

[RuralGuy]

Great! Lots of luck with the project and have fun.