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).
- Contact is a table holding information on various people.
- A contact has a organization_id field which is a foreign key to Organization, id
- We handle a case where if a contact has no organization
(organization_id = null) it is a “freelancer”… - Organization is a table holding information on organizations. An organization is linked to many contacts.
- 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
- 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.
- 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).
- 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.
- 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.
8
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.
7
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.
5
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.
1