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.
Best Answer
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.