4

The context

I'm modeling a database for a small ERP system. However I've recently hit a difficult spot that I'm having a hard time wrapping my head around. The logic of it involves a few special cases, I'm hoping someone with DB design background might help (this is my first large DB model project).

  1. Contact is a table holding information on various people.
  2. A contact has a organization_id field which is a foreign key to Organization, id
  3. We handle a case where if a contact has no organization (organization_id = null) it is a "freelancer"...
  4. Organization is a table holding information on organizations. An organization is linked to many contacts.
  5. Invoice is a table holding invoice information.

The problem: Suppose a contact A has an invoice X and that contact changes organization (after the transaction). Who owns the invoice? (in other words, how do I link invoices to certain entities).

Possible solutions I have explored

  1. Link Invoice to Organization with a foreign key (organization_id) in table Invoice.

However, this does not handle the case where a Contact has no organization (is a freelancer). If such a contact has a sale/invoice... the system can't handle it.

  1. Link Invoice to Contact with a foreign key (contact_id) in table Invoice.

However, if a contact changes organization, that organization would inherit the contact's past invoices (which is WRONG).

  1. On the front-end, auto-generate an Organization based on a Contact's information when that contact is a "freelancer".

To be honest, I don't like this solution. It feels like a cheap hack.

  1. Force contacts to have an organization...

I'm hoping there is another solution than this one...

EDIT #1

After analyzing some the answers, I've realized an important piece of information is missing. The small ERP system will be used by many clients, some of which follow the B2B (Business-to-Business) model and others which follow the B2C (Business-to-Customer) model. In the B2C model, Contacts DON'T have an Organization. But they should still be able to have projects/sales associated to them.

  • "If such a contact has a sale/invoice... the system can't handle it." Why not? – Ben Aaronson Sep 20 '14 at 0:10
  • Because the contact has no organization, it can't be linked to the invoice. If I add a contact_id field to Invoice, it comes back to the same as point 2. – Sebastien Daniel Sep 20 '14 at 0:15
  • "Because the contact has no organization, it can't be linked to the invoice." Why not? Contact and Invoice both have a foreign key to organization, both of them are set to null in the case of a freelancer. – Ben Aaronson Sep 20 '14 at 0:19
  • So how do I distinguish two freelancers' invoices? – Sebastien Daniel Sep 20 '14 at 0:21
  • The invoice would have keys to both the contact and the organization – Ben Aaronson Sep 20 '14 at 0:21
10

There is nothing wrong with providing an individual organization for each freelancer, even when there is only one "employee" in this organization. Actually, this reflects the legal situation much better, since a freelancer can have the role of a company (own address/mail account / phone number) and the fully separated role of a private person or employeed person as well. And it will help you to model other things more uniform as well.

The CRM we are using in our company works exactly like this

  • you first create a new organization/company in the system, with address, central mail address, web site, telephone etc.

  • contact persons are always added to the "active" organization. You can enter an address differing from the companies address, if you like (but you don't need to). And you cannot add contacts without an organization.

  • contact persons never change their company; they may become inactive, and you can add a new contact person beeing a copy of an existing one. This helps to manage the historic information who once was my contact person in the past (even when in a new company today) and avoids problems like yours.

The last point is surely not the best solution for every system. You have to decide of this kind of model suits your needs in your specific case.

To your edit: for the case where you really want organizations and individual persons to be accountable, it may be better to follow Fowler's ideas from his book "Analysis patterns" on how to model accountability (see page 4). Create an additional table "party" for persons and organizations. Each entity in "party" has either an corresponding entry in "organization" or one in "person". When doing object-relational modeling, "party" would be just the base class of person and organization. Invoice gets just a "PartyID" as the referencing foreign key.

This will allow to deal with individual persons as well as with organizations in a uniform manner.

  • I agree, people working oueside a company have to take on certain org-like responsibilities, probably should others and can even more. Some might even have a dba or other name you could put down for it; for the rest you can make a standard name suffix [Jo Bort [Freelancer]] or flag /org type field for it. – StarWeaver Sep 20 '14 at 13:27
  • I had juggled the idea (your last point) a bit. I like your thinking (from an administrative standpoint). I'll review both cases (your suggestion and Eric King), as they both are viable. i'll run some scenarios. – Sebastien Daniel Sep 20 '14 at 14:34
  • I think an important aspect to take in mind is that your analysis is looking at the situation as B2B context (Business-to-Business). Where as the B2C (Business-to-Customer) is also plausible. In such a case a Contact has no Organization. Im going to add this to the original question, sorry it wasn't clear, my mistake. – Sebastien Daniel Sep 20 '14 at 15:36
  • This is kinda the basis of a temporal database. As I understand, a common approach to this is for an entity that can change, have a 'header' table with the unchanging fields and a 'version' table with changeable fields, foreign keyed to the header. Then any other table can either foreign key to the header or the version, whichever is more appropriate. – Ben Aaronson Sep 21 '14 at 0:24
  • @SebastienD.: see my edit on how to model this situation. – Doc Brown Sep 21 '14 at 6:49
3

At first blush, I would say two things would make things simpler:

Create an Organization that represents "Freelancers" and assign all freelance Contacts to that Organization.

and

An Invoice should contain both an OrganizationId and a ContactId, which represents the Organization and Contact relationship at the time of the invoice.

If the Contact changes Organizations later, it won't matter, because the Invoice still has the original OrganizationId.

  • I dont see your first suggestion as being a "good-practice" as that makes no sense from a data perspective. However, your second suggestion is an idea. You're basically suggesting to "record" contact/organization at the time of invoice creation. Which could be done in a separate table? – Sebastien Daniel Sep 20 '14 at 0:16
  • I disagree to your first suggestion - this is not better than to allow a NULL everywhere where OrganizationID is used as a foreign key. – Doc Brown Sep 20 '14 at 13:35
  • @DocBrown I think it depends. I've seen databases that are an absolute nightmare to work with because of null values on foreign key columns all over the place (as would be the case for every invoice assigned to a freelance contact). In this case, I think having an explicit "Freelance" organization will avoid lots of headaches down the road. – Eric King Sep 20 '14 at 20:59
  • @EricKing that's an interesting comment. Care to delve a little deeper into why the "null" organization would cause problems, or relate to your experiences? – Sebastien Daniel Sep 20 '14 at 21:07
  • 1
    @SebastienD. I hear the most complaints when it causes problems for the folks who have to put together reports on the data, whereby instead of doing simple joins they have to compose awkward expressions that handle nulls. Everything is much simpler when they don't have null foreign keys to deal with, even if that means sacrificing a little bit of relational database integrity. It's a compromise. – Eric King Sep 21 '14 at 21:39
0

A caution from an experienced designer who loves databases: Trying to guess data relationships without having an executive approved set of Business Use Cases, Entity-Relationship Diagrams, a Master Data Flow Diagram across the enterprise, etc. is dangerous. Your question seems to show that you are trying to capture the relationship flaws "on-the-fly" from the database perspective, instead of planning the structure from the approved data diagrams and then building the bridges you may need to wedge the old system into the new. That can lead to accounting problems... not just data nightmares like the ones you have already surmised. (a sample case )

An interesting trend I have seen with some systems is to have Persons tracked as data objects, who can be validated as real-world objects by personally identifiable information. Persons can have a portfolio including Roles and Histories, allowing them to be referenced as they move across organizations, or Tags allowing them to be referenced across various data services or data warehouses. Watch out for privacy concerns...

Customer as a role that can toggle between using data from Organization objects or Person objects, with a flag indicator, may allow you to manage your b2c b2b integration problem without requiring too much transitional modification. In general Transactions store details as the foreign key of an object in the position of a role and timestamp. For example an object called Location, connected to GIS, could have a role as ship-from, ship-to, bill-to, bill-from or mail-to.

Tables for critical systems should have: unique id, active/inactive flag, active date-time, last modified date-time, inactive date-time, status code - These will allow you the best control over historical data queries and transaction control logs. Status code allows you to track things like merging records, entered in error, legal flags, etc. as well as minimizing data duplication in transactions

Newer ERP systems will deal with structured Database and unstructured data, SQL and NoSQL, etc. Objects, Roles and Transactions as a mindset used in planning may minimize your headaches going forward.

  • Your comments are very true and constructive. But this system isn't for a specific business, it's for "any" business, so it's a very generic model for very generic use-cases. – Sebastien Daniel Sep 21 '14 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.