In several projects, I've seen the same problem pop up. But so far, I have not found a satisfying solution. Here's the issue. I need a table with contacts. These 'contacts' could be individual people, or organizations. From one point of view, I prefer to have these contacts mixed (one table with both types), but from another point of view, I prefer to have them separated. Here's some arguments:
- A company that sells products to the general public has 'clients' of both types. When relating a sale, it is most convenient to refer to a 'client ID' without having to specify what type of client this is (for each sale).
- A company can have many providers that it uses. Some are individual people (like a plummer or a consultant), others are companies. When relating a 'purchase order' I again prefer to relate to just one provider ID, without having to differentiate between provider types.
- When managing the actual contacts, I do want to differentiate between the two types. That way, I can have the right fields for each. For instance, "First Name" and "Last Name" are not useful for companies, but they do are for people. Also, if I have a separate table, I can actually link people working at specific organizations.
Being pretty common – I guess – I hope that others have tackled this problem before. What is the way you set this up?
Best Answer
I suggest you create them as separate tables.
Create an Individuals table and an Organizations table so that each can have the proper fields for their respective types.
Draw from the same sequence to populate the ClientID field in both tables.
Create a view to
UNION ALLboth tables for code that needs information about both.Create foreign keys to the tables as necessary.