Is this the best way to handle this?
IMO, not really. Bringing both types of entities together into reports requires workarounds, and you would need more TOs and relationships for the ususal child tables (Adresses, Notes etc.)
I'd suggest storing both in one table
Projects --< Customers >-- People/Companies
and, if necessary, using a flag field to distinguish between the two types.
I agree with erolst—keep them in a single table. You will end up with a few fields that are relevant only to one type of entity (eg. personal name fields that are not relevant to company records; official company registration number that is not relevant to person records) but this is a smaller price to pay than all the overhead of maintaining separate tables for essentially the same purpose.
Agreeing: your table-entity is the "customer" regardless of whether that's an LLC, person, family, international conglomerate, or other form of customer. Handle any necessary differentiation -- if any is really needed -- via special fields or even a single "type" field.
Keeping all customers in a single table will make your reporting much more straight-forward down the road.
I think you mean you'll have three tables, Customers, People, and Companies? That's a fine way to do it. You could also do one table just for Customers. Either would work. Which is best? Depends on other needs. Will you be referencing multiple people as contacts for a Company? Are you also keeping track of other "contact-type" records, such as vendors? Is it a tight industry where people may switch between companies and that's something you'd like to know?
Thanks to all who have replied thus far. All replies have been helpful.
Yes, we need to track people that work for companies and also have the ability to "move" a person from one company to another. It is even possible that a person may work at two or more companies at the same time (contractors for example)!!
Some of the companies will be vendors. Is it best just to have a vendor flag field or another table?
It does raise lots of possibilities doesn't it.
I like to create an "address book" with one to one links to people and organisations. That allows me to handle everything through the address book. The things that make people special (DOB, first/last name, etc) and organisations special can be handled in their own space. A join table between people and organisations allows us to link the two as required.
Having a special table for customers seems odd. A customer is an entity engaged in a business transaction (projects). The important thing is the business transaction (projects). The customer is the "who" of that transaction. If you agree with this view, then you don't make a customers table. To obtain a list of customers, you search your address book or your projects for people/organisations that have undertaken projects.
After many, many iterations, we've come up with the following. I've seen lots of other ways, and tried most of them, but I can testify that the following principles work in the real world, with a wide variety of solutions and without compromise and/or gimmicks. Before I go on, let me acknowlege that there are some very strong opinions about this, my own included, and firmly state that I am not trying to "sell" our methods or tell you the One Right Way. These principles have served us well, though, and I'm sharing them in hopes that even if you don't agree wholeheartedly, it'll give you some food for thought.
A "Contact" is either a person OR a company. Never both (like Outlook).
We used to have a contact linked to either a record in Companies or a record in People (a "Party" model), but found that there are actually very few fields different between the two, and it was easier to just combine them in one table. Where people and companies differ (i.e. first and last name vs. company name) we have a hidden tab and onRecordLoad to show the relevant fields for the contact type. Sometimes the difference is just labels. For example, people have a date of birth, companies have the date they were founded. People have SSN's, companies have FEIN's.
A Contact may be related to one or more other Contacts. The most obvious of these relationships (for business) is an employment relationship, but in reality, a company may be related to another company (subsidiary/branch), a person may be related to another person (family, friends) and a person may be related to companies they don't work for (consultant, like most of us, volunteer, customer, etc.). Also, some people work two jobs. Our Relationships table allows any Contact to be related to any other Contact, and gives us a place to list each contact's role in the relationship (sister/brother, employer/sales director, headquarters/branch).
(On a side note, we actually managed to display the "other" contact in a portal in past versoins, but now we bite the bullet and create, via script triggers, two relationships for each pairing, one for each direction. Either solution isn't difficult until you try to do it.:-) Either was is worth the effort, though; it's reality, so we deal with it.)
Things like Customers, Vendors, Volunteers, Participants, etc. are NOT contacts. They are particular roles that a Contact plays, each with their own attributes. So, for instance, ABC Company might be both a customer and a vendor. As a Contact, they have phone numbers, fax numbers, addresses, a name, etc. Their Customer or Vendor role is linked to that Contact. If a phone number changes, you change it in one place, and you're done. Likewise, one person (Contact) might be a customer, an employee, have three user accounts and also be a voluteer on the side. That's one Contact linked to the various role records in the other tables.
Those "role" tables provide two things: a place to see only "customers" (or whatnot) and a place to store attributes (like terms) that are only relevant to that role. In the end, though, a contact is a contact. They didn't get a new customer name, they got a new name. They didn't get a new vendor phone number, they got a phone number.
One interesting attribute of a role, like Customer or Vendor is who, among all the related Contacts, is the main contact for this role? For example, as a Customer ABC Company may want you to talk to John, but where they act as a Vendor, you should talk to Jane. Or even, John might be a customer, but when he's actiing as your vendor you should talk primarily to his wife, Jane. The various role tables allow you to set those role-specific settings, but in the end, ABC Company is ABC Company, John is John and Jane is Jane.
It's also important to consider that in dealing with your customer, you may in fact deal with one or more companies and one or more people, but only one if them is truly the "Customer". Try this thought experiment: If the company closed it's doors and dissolved all its partnerships, who are you going to sue to get paid? That's your real customer.
Another offshoot of thinking this way: all your contacts may have phone numbers, addresses, etc. ("Contact Info"), but who does that contact info really belong to? I work for Extensitech, but Extansitech's phone number is not "my" phone number. "My" phone numbers are my cell phone, home phone and/or a direct number. The main company number belongs to Extensitech. If I look at my contact record, I can see my company record and its contact info, but if I were to leave Extensitech, I don't actually change my phone number; I'm associated (hopefully) with another company, and you'll see their numbers from my record from that point on. If a company changes its address, I should change it once, on the company, not for everyone who works there.
I hope this is helpful.
FM13 Certified Developer
Thanks for that detailed expose Chris. I have recently developed a Contacts solution for a client, using very much the same thinking because my client has clients of his own that form complex webs—a person owns several companies, and is a director of another company, and has a couple of family trusts, and is a silent partner in another couple of companies, and is married to someone who … etc, etc. Not only that, but I myself am also one of my client's clients. Furthermore, each client can also have multiple addresses, phone numbers and email addresses.
The underlying structure is that each of the main entities lives in its own table—contact, address, email, phone, web—and then there are join tables to make whatever links are needed. Links can be made or broken whenever necessary without touching the underlying data, and that data can be edited if necessary. This means that where previously my client had the same address, say, stored multiple times (and often written in different ways—Street or St, Ave or Av, for example) it now need only be stored once—and corrected or standardised once.
Yes, except I would suggest that one customer could have many projects, not the other way 'round.
Because the Customer table is really just a join between Contacts and Projects, it is 1:1 for each join instance of a Contact-Project, with additional join records for different projects and for different contacts on the same project.
That allows each Customer-join record to relate to only one project and only one contact while allowing multiple join-records to provide unlimited links between mutliple contact-projects and mutliple project-contacts.
I do not believe that Customers really are a join table in this case.
One contact would presumably have 0 or 1 Customer records. I can imagine scenarios where the same contact could have two customer records, but those scenarios are awfully unlikely.
When I say that customers are roles, I do not mean one role in one project. I mean one role with you, the user of the system. I realize now that that may have been unclear.
Sorry for any confusion. I was using the wording in the diagram provided by David, rather than the terminology in your posts. I think David has come up with a variant on your idea which will serve, though calling the join table "customers" creates some confusion -- using that name differently than most of us think of customers. As a simple join table to match a single contact (really a customer in the everyday sense of the word) to a single project, David's diagram looks good.