Am trying to plan database and would like input as to best method to create the following:

I want to apply category(s) to a record. (its a table listing schools and there are numerous categories they can be a member of such as elementary, middle, high school, reserve, and other properties). There may be up to 10 categories (or types/descriptions/properties may be a better word) that applies to that one school.

What is the best way to structure this? In playing with this, a checkbox with defined value lists works on the interface level, but I think it may be problematic in reporting/sorting/filtering by those attributes.

I have this need multiple times - for instance I want to be able to check which product categories I supply to a particular school (again multiple values possible and I need to be able to do detailed reporting on this). Same with contacts at I have at the school, I need to be able to check off what role(s) they play at the school.

Obviously I'm a very green, I've been reading and also watching Osbourne FMP10 tutorials to learn but have not yet come across best way to do this. I've been planning my database solution extensively on paper but am not sure of proper structure to ensure no problems in reporting later.

Any help is greatly appreciated!

Bernhard

Welcome to the forum. The technique that you're missing is a "join table." A join table captures the intersection of two tables and resolves their many-to-many relationship. You have a mtm relationship btw Schools and Categories.

Here's a demo.

JoinDemo.fp7.zip

Edited March 6, 200916 yr by Guest

Thank you! OK, that was my lesson of a many-to-many relationships. Had not used those before hence my troubles. I will learn more about many-to-many relationships and then integrate this in my planning.

I really appreciate your help. I will need this for work but want to design my own solution and learn something in the process.

I've played with designing the database the same time I'm learning and I've attached it in case you want to see what else I've done. There may be a lot of other big issues - if you have time for a quick peek, I would appreciate it!

Thank you again.

Welcome to the forum. The technique that you're missing is a "join table." A join table captures the intersection of two tables and resolves their many-to-many relationship. You have a mtm relationship btw Schools and Categories.

Here's a demo.

BAK_JDB_1.zip