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