FMP7 structure

[rivet]

I have to build a solution that can handle two type of contacts. Between the two maybe 60% of the fields are common. What do you think is the best way to proceed with the db structure.

2 completely separate table (contactA and contactB)

2 separate and 1 common (contactA, contactB and common)

break all commons into separate tables (phone table, address table, email table)

FileMaker Version: 7

Platform: Mac OS X Panther

[Doug Leib]

Without knowing more about the differences between the contact types, my first question would be do you really have to separate the tables? For example you say the contact types were vendors and customers. You could have a field that specifies the type of contact and direct them to a layout designed for that type. If that's not appropriate you could separate the contact tables but still combine and subordinate tables, ie. phones, addresses.

I think in order to offer a fully qualified answer more needs to be known. What makes the contacts different? Are there any reporting requirements that would dictate a structural benefit to one method or another?

[Ken Newell]

Another thing to consider is if it is possible for a contact to be BOTH types. For example could it be possible to be both a vendor and a customer? That would probably influence your approach.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[H.P.]

Yeap, I think Ken's appraoch is best. You can make a Contact table and a Contact Type table. With this approach, you are not limited to only Contact A or Contact B, you can add Contact C, D, E, F, easily through the layout, without modifying the database structure.

[Ted S]

The supply side and the sales side of your business can be very different. When designing you should be looking beyond present day needs and be anticipating future needs. The "customers" of your application often don't posess this ability.

We have one giant contacts DB with 18,000 records. Most of them fit into 2 categories; Supplier or Customer. While there are a lot of things in common the differences can be dramatic. We like to capture a lot of marketing info on the customer side but that is completely inappropriate on the supply side. This means that making some of the fields "mandatory" is difficult because of the nature of the data.

I'm planning on redoing our DB at some time in the future and I will be breaking it up somewhat. The tables will likely be: Supplier, Vendor, Facility, Person, Notes.

[Steve N]

I have a similar dilemma. In my current structure I have one file for names that I use for sold to, ship to and vendor. Each record has 6 fields (actually more that are not relevant here) Each record has three fields for sold to name/address/csz and another three fields for ship to name/address/csz

Since I am rebuilding to FM 7 I am thinking about changing the structure. The problem is I have only one ship to for each customer and 75% of the records, the sold to and ship are the same which means duplication of data.

One option is to have 3 separate tables for sold, ship to and vendor, but I still have the duplication with the sold to/ship to since 75% of records have the same sold to and ship to

I have created one file with following fields: name id, name, address, csz, sold to id and a self join relationship (I think I am using that word correct). The relationship is between the name id and ship id, if they match that record shows up in the ship to portal. One record can be both the sold to and ship to. So far so good.

The problem is filtering the list. I want only sold to name to show up in the sold to list and so on. I created three yes/no fields. Sold to, ship to and vendor. And a global field with the word

[dkemme]

Instead of having two sets of address fields records, ship to and sold to, create a table that has addresses related by VendorID. The user could define at data entry if the address is a ship to or sold to address. That way a vendor could have any number of addresses and the user could easily switch an address from a ship to to a sold to. You would then have an address table that would be much easier to use.

[rivet]

regarding the two type of contacts, yes they must be separate tables because they are being used by two different departments. What I was considering sharing was a table for address and other common components but how far do I break it down - email table, phone number table etc.

Is there a benefit to this approach?

Is there a drawback to this approach?