newbie relationship/multiple tables question

[eml]

Hi there,

I am new to using multiple tables, relationships, and scripts in FMP, and have gotten myself to a point where I am too confused to know what to do! I am working on a database to catalog my various research materials, and need to make it easy to enter data and expand to great proportions while keeping things organized (I already have over 900 records).

So far it looks like my best bet is to design the database in a hierarchical manner, but I'm not sure how to do this without being redundant or losing data. I have fields such as Country, TypeOfMaterial, and UniqueID that will be required for each of the records. From there, I need to enter specific information related to the TypeOfMaterial field. The types of materials include maps, books, research samples, equipment, etc, so they will each have a set of unique fields.

I'm envisioning multiple tables that are linked together by various relationships. The MANDATORY DATA table would include info that is required of all samples, such as the Country, UniqueID, and TypeOfMaterial fields I mentioned earlier. Then I would have a separate table for fields of each TypeOfMaterial. I figure I can tie these different tables together by using my UniqueID field... but how do I make those values show up from one table to the next? There is probably an easy solution to that question, but I haven

[Ender]

Welcome Emily,

This looks like a good "How do I normalize this?" question. Normalizing is the process of identifying the entities and relationships to most efficiently describe the data. The idea is to make a table for each type of thing that you want to track data on, and avoid duplication as much as possible. Typical examples are Student - Enrollment - Class databases and Invoice - Line Item - Product databases, where these are the main entities or tables.

As you figure out the entities that you want to track, you will need to figure out how to relate them. Figuring out the structure or "schema" is not a trivial matter. It takes some time to think about all the different ways you might want relate the data. It is often helpful to sketch out on paper what fields you would put into each table, and how you want to relate them.

I started with this overview of database design because it's not clear what entities you are trying to work with in your solution. Can you identify groups of fields where each group is about one thing?

An example of what I mean: A typical Contacts database has information about people, where they work, how to contact them, and notes about contacts with them. This breaks down into tables like Person, Company, Phone, and Contact Note. Graphically, it looks like the diagram below, where the boxes are the entities (or tables) and the lines are the primary relationships. The chicken feet on the ends on the lines indicate a "many", as in a Person can have many Phone number records related to it. The underlined fields are the identifiers, or primary IDs (the thing that is for sure unique to that record.)

I find that an Entity-Relationship diagram like this is helpful for finding problems with the design. I show in the diagram that one Company can have many Persons, but there may be situations where a Person can work for more than one company. For some businesses, tracking multiple companies for each person may be important, and the design would need to be changed.

Anyway, I said all that hoping to give you the information you need to articulate how you want to group your data, to build entities that make sense. It will save you work later to start with a good relational design up front. Let me know if I should clarify further.