CODEWITHSUNDEEP
sqlpostgresqldatabase-design
MurifoX
MurifoX

Reputation: 15099

Opinions on database design of a Document table with multiple behavior

A Document is an structure that holds all information about a transaction between an origin and a destination. Both the origin and destination can be of two types, a company or a person. What is the best approach to model an use case like this?

Idea nº 1:

Document
**document_columns**
origin_id -> foreign key
origin_type -> customer or person
destination_id -> foreign key
destination_type -> customer or person

Idea nº 2:

Document
**document_columns**

DocumentOrigin
document_id
origin_id
origin_type

DocumentDestination
document_id
destination_id
destination_type

Idea nº 3:

DocumentCompany
**document_columns**
company_id

DocumentPerson
**document_columns**
person_id

Things that could possibly affect choosing one over another is the need for reports that gather all Documents as if they were the same. Using UNION or other more advanced SQL commands to generate information about the schema should be considered too.

Upvotes: 1

Views: 40

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76968

Let's assume you already have a company and a person table. You could create an actor table, which would have a CompanyID and a PersonID foreign key, if it is a person, then PersonID would have a value, otherwise it would be null. If it is a company, then CompanyID would have a value, otherwise it would be null. So, you would have Actor(ID, CompanyID, PersonID, ...), Company(CompanyID, ...), Person(PersonID, ...) and Document(DocumentID, OriginID, DestinationID), where both OriginID and DestinationID would be foreign keys to the Actor table, where OriginID would reference the origin person/company and DestinationID would reference the destination person/company.

Upvotes: 1

Related Questions