Relationships between entities and attributes

[eliza]

I am a FM9 newbie and am struggling to get to grips with the basics of the db design. I have inherited a "contacts" table showing all contacts related to our company, whether they be customers or suppliers. From a "contact" layout I can generate a new customer "order" using the contact info. My problem is that I need to include a portal with the ability to select using a drop-down list of filtered contacts (staff) who will work on this order. I have a feeling that this can only be achieved by creating a seperate table for staff rather than holding them in the same table as the customer contacts. Am I correct or am I missing the point?

[Anuviel]

Correct. Staff and contacts should be kept separate. You should also keep your suppliers and customers separate as well. I would recommend staff, customer and supplier tables. You can link customers and staff via order. IE. After you create an order you can then assign staff and customer to it.

[Søren Dyhr]

Staff and contacts should be kept separate.

Oh nooooo - Why?

The classic case is students, classes and teachers. In some versions people are put into two tables: students and teachers. In other versions into three: students, teachers and staff. In relational theory this is not a problem, it is even encouraged. In the real world use of FM it is completely incompetent.* This training material manages to compound this horror by putting people in more than one table in at least three example cases - and in one of the examples splits people in four tables!

...snipped from: http://network.datatude.net/viewtopic.php?t=107

From the very same hands originates this very central paper, in the filemaker world:

http://www.digfm.org/ref/FM7_key_concepts.pdf

...well it's not his point alone, Theo Gantos says the same about, what usually is called "Entity Duplication" here:

http://podcast.adatasol.com/media/Filemaker_Podcast_12152007.mp3

--sd

[Anuviel]

Hm, I read his post as customers and staff. By contacts I understood he meant a section for customer information not contact information as in I can have one customer, a company, and for that company I can have multiple contacts like sales person, inventory guy, owner. If that is the case why would you want to put your staff into same category as contacts? or am I missing something here or not getting something (as I usually do not)?

[eliza]

I am now totally confused. I don't think I need to get involved in the discussion of contacts vs customers, I have already dealt with the issues of a single company having several different contacts and I understand this issue. My problem is that for a single enquiry (where a single contact can make several eqnuiries) the one enquiry can related to several staff, who would be selected via a portal on the enquiry page.

I think I should have the table Enquiry, representing the entity "Enquiry", with the enquiry attributes, a join table "StaffEnquiry" and a Staff Table to allow for the many to many relationship of one enquiry having many staff and one staff being able to work on many enquiries. The staffEnquiry Table would also allow for fields such at amount due etc. Am I along the right tracks?

[Anuviel]

As far as I can tell yes, but as they say always get a second opinion.

[Søren Dyhr]

and for that company I can have multiple contacts like sales person, inventory guy, owner

Listen to what Theo Gantos suggests, we should call it Parties, and build the structure recursive...

http://jonathanstark.com/recursive_data_structures.php

If that is the case why would you want to put your staff into same category as contacts?

Why? Because they share way too many attributes, if splayed over many tables!

--sd

[Anuviel]

Yeah, that make sense, I think we missed each other or maybe not. I was saying that customers, contacts and staff need to / is better to keep them separate. By saying customers I did not mean people that you deal with but a "customer" as an entity that purchases from you. Now a customer can be a single person or a company depending on your business. A single customer would pretty much have the same information as a contact such as e-mail, phone number and similar. A company on other hand can have multiple contacts and those contacts then have their contact information such as e-mail, phone number and similar. Basically in the first case it would really not matter but in the second you would have to have a contact table and a customer table. Some of your contacts can be your customers but not all of them (guy in shipping department is a contact but not a customer, his company is a customer, not him). Customers and Staff "do not share" same information. Contacts can have same information as Staff as basically you can put your Staff into contact table as well as you can put your Customers into a contact table. For example your contacts table has regular contact information such as name, e-mail and so But your staff table, for example, has all the same information as your contact table plus Staff position, salary, department, date of employment and so on. Basically Staff can go into a contact table as you said but I believe it should be in its own table and only the contact information for staff should be put into the Contacts table. So if assigning a Staff to a particular job you would pull the information fro m Contacts table which in turn pulled information from Staff table.

*** All above in my opinion, as I see it and understand it, not claiming it is correct or wrong.

[David Jondreau]

A filtered drop down list can be done whether everyone is in one table or you have separate tables for different types of people.

There's a argument for having one table for "Parties" and separate tables for all the types of Parties (People, Companies) and tables for the sub types of Parties (Vendors, Customers, etc) if the information gathered about those sub types are unique to those types.