One To Ones Or All In The Same Table?

[pehrlabel]

I have one table of contacts, with all of the same general fields like name, address, etc.

There are about 12 different categories of people i work with, and each category of people has unique fields, but never more than 20.

Should I create one table of contacts, fill it with all of the fields for the different categories, and then create one layout for each type of contact? meaning one layout per category? So I will have a generic contacts layout, and then add the category-specific fields to each duplicated layout?

I know this is not using filemaker to it's full potential, since this way would just be a flat database, but this would work just fine for me, since I will never have more than 10,000 contacts, i don't think it should slow down filemaker too much to have empty category fields in each record.

Or should I create just the generic contacts table, make one-to-one links with every field of the duplicate tables for each category, and then add specific category fields to each category table? This way would be easy, too, but I just don't know if it's necessary.

Or am I stupid and there is a third way to do this?

I've read about ERDs and I'm reading the great "Using Filemaker 8" book, but i'm not sure i've figured it out yet.

Any advice would be awesome! Thanks, Adam

[Ender]

Can you give a couple examples of different fields for different categories of people?

[pehrlabel]

Oh whoops i should have checked the replies to my post within the last 2 months.

sure, here's an example: i have one contact who is at a radio station, so there are fields for the radio station's wattage, frequency, request phone #, etc. but then i have another contact who is an editor for a magazine, and that contact needs fields for whether it is national, local, or online, whether they write features or reviews, etc. so some fields are all the same for contacts, and then each type of contact might have 5-20 unique fields just for their type of job.

and i have about 20 different types of contacts. so i wasn't sure if i should, for example, have an editors table and a radio stations table, or just have a "people" table with the fields for all the types, and then have a layout for each type, so i will only add fields to the layout for that category of people.

i guess both ways would use layouts for each category. one way would have a whole lot of empty fields in one big table, the other way would have one-to-one links from the people table to the specific category table for all of the standard fields (like name, address, phone, email), and then unique fields just for that table that focus on the unique fields of that person's category.

thanks, adam

[Vaughan]

Why do you need to track radio station frequencies and wattages and publishing company distributions? If you wanted to compare radio stations or publishing companies, you need to keep them in separate tables and have fields for each piece of data. But I can't see an end to it.

If it's just needed for informational purposes and won't be used for comparisons, thenjust have a Companies table and put all the info into a big ugly text field.

I really do want to know why: I'm interested in your business needs and want to understand them.

[corn]

Radio station frequency is not an attribute of a person so it doesn't belong in that table. A simple rule some people use to help define tables is "one subject per table and only fields that describe that subject."

How you model this is determined in large part by what you're trying to model. Is it important to know the radio station frequency and power as separate facts? If not, you migt consider having an "employers" table (or perhaps an "organizations" table) and including that information in a "notes" field.

You need to decide what the roles are in your application. If all of these people are just contacts in the system then a single "people" table with all of the common people attributes (e.g. biographic and demographic data) is appropriate. You would still have separate tables for different subjects and facts about those subjects with relationships between the person and those related things.

Generally you only need to subtype your people table when people serve different roles in the system (e.g. a person can be a teacher who teaches a class or a student who takes a class). You can create generalization hierarchies if you need a flexible model to accommodate flexible or unknown roles but most systems of moderate size can get away with having subtype tables for the roles.

[pehrlabel]

Okay I've got my subtypes laid out now.

I have companies, with a unique ID for each company (serial number), as my primary key. each company record has a field for category, and these categories (retail, distributor, etc) each have a subtype table now, thanks to your previous posts, with specific info for that category.

I have a distributor subtype table, with a unique ID for each record.

I have a store subtype table, with a unique ID for each record.

One store can have several distributors, and one distributor can have several stores. I think I set this relationship correctly with the join table: I have a join table called "distributors stores," with the ID for distributors and the IDs for stores as the two keys in each record.

How should I relate companies to their subtype tables? And what if one company has more than one subtype (like a distributor that also has a store)?

For example, I want companies, whose box I check "distributors" under the category field, to be linked to a new record with that set of fields in the distributor subtype table.

I also want companies, whose box i check "stores" to be linked to the stores subtype table.

Should I just create different layouts that show data from the Companies Table, and linked records from the subtype table?

When I think about linking the company ID one-to-one with the subtype ID, then I wonder "how can I tell filemaker which subtype table or tables to link to"

I thought maybe there is a script that can trigger when i check one of the categories boxes, but this might be way too complicated and unecessary.

Thanks if you have any tips!