0

I have a database schema that includes the following tables:

  • People
  • Organisations
  • RelationshipTypes

What I want to design is a concept of a relationship type, and then the structure of that relationship. So a person could have a type of relationship with another person or with an organisation. An organisation can have a relationship with another organisation or a person.

I've got the following schema, but I'd like to run it past the community to see if there are any better ideas.

CREATE TABLE OrganisationRelationshipTypes
(
    ID INT PRIMARY KEY IDENTITY,
    RelationshipTypeID INT NOT NULL REFERENCES RelationshipTypes(ID)
    FromOrganisationID INT NOT NULL REFERENCES Organisations(ID),
    ToOrganisationID INT NOT NULL REFERENCES Organisations(ID)
)

CREATE TABLE PersonRelationshipTypes
(
    ID INT PRIMARY KEY IDENTITY,
    RelationshipTypeID INT NOT NULL REFERENCES RelationshipTypes(ID),
    FromPersonID INT NOT NULL REFERENCES People(ID),
    ToPersonID INT NOT NULL REFERENCES People(ID)
)

CREATE TABLE OrganisationPersonRelationshipTypes
(
    ID INT PRIMARY KEY IDENTITY,
    RelationshipTypeID INT NOT NULL REFERENCES RelationshipTypes(ID)
    FromOrganisationID INT NOT NULL REFERENCES Organisations(ID),
    ToPersonID INT NOT NULL REFERENCES People(ID)
)

CREATE TABLE PersonOrganisationRelationshipTypes
(
    ID INT PRIMARY KEY IDENTITY,
    RelationshipTypeID INT NOT NULL REFERENCES RelationshipTypes(ID)
    FromPersonID INT NOT NULL REFERENCES People(ID),
    ToOrganisationID INT NOT NULL REFERENCES Organisations(ID)
)

The idea of this is that it covers such scenarios:

  • An organisation has done business with another organisation
  • A person is another person's father
  • An organisation recently hired the person
  • A person recently worked for the organisation

This schema seems a little messy, but at the minute I can't think of an alternative.

Do you have any suggestions?

Paul
  • 3,072
  • 6
  • 37
  • 58
  • Do those latter two need to be separate tables? Couldn't the relationship type determine whether the relationship is "from" the person and "to" the organisation, or vice versa? – Damien_The_Unbeliever Aug 09 '12 at 06:39
  • Have you considered the Party Model? See [answer here](http://stackoverflow.com/questions/5466163/same-data-from-different-entities-in-database-best-practice-phone-numbers-ex/5471265#5471265) – nathan_jr Aug 09 '12 at 07:23
  • How would you evaluate alternative solutions? What criteria should we consider? – Neville Kuyt Aug 09 '12 at 08:53
  • how will you know "A person is another person's father"? is there something in the RelationshipType table? can you show the structure of this table as well. – MakkyNZ Aug 09 '12 at 08:54
  • @MakkyNZ - Yes, the RelationshipType table will contain a description of the relationship such as "Father", or "Past employer". – Paul Aug 10 '12 at 05:05
  • @NevilleK - I don't think there are any more scenarios, I think I've got them covered. I was just looking for any alternative schema's to try and replicate what I'm doing. – Paul Aug 10 '12 at 05:05

2 Answers2

2

Party is often used as a generic term for person or organization, as in "parties in contract" ...

enter image description here

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • Interesting, I like the look of that. – Paul Aug 10 '12 at 05:07
  • I like the look of that as well. It's interesting to note that the object labelled "party-type" is a feature of ER modeling, but not of relational modeling. It's an instance of the "gen-spec" pattern, otherwise known as class hierarchy. For the design of a relational model to implement gen-spec, see other questions in "database design". A search on "Class table hierarchy" will give lots of threads. – Walter Mitty Aug 10 '12 at 11:16
1

It looks nice.

I suggest squishing all of the relation tables into one

CREATE TABLE RelationShips (
    ID INT PRIMARY KEY IDENTITY,
    RelationshipTypeID INT NOT NULL REFERENCES RelationshipTypes(ID),
    FromPersonID INT REFERENCES People(ID),
    ToPersonID INT REFERENCES People(ID),
    FromOrganisationID INT REFERENCES Organisations(ID),
    ToOrganisationID INT REFERENCES Organisations(ID)
)

This way you can easily filter relationships with just one database lookup. I.e. "Find all relationships to the specific person". Of course you'll need to remove NOT NULL constraints.

Zaar Hai
  • 9,152
  • 8
  • 37
  • 45
  • You could add `CHECK` constraints to this definition such that one (and exactly one) `From` column is non-null, and similarly for the `To` columns. Not sure if I'm in favour of the overall design though. – Damien_The_Unbeliever Aug 09 '12 at 06:43
  • This is something I was possibly looking at. Avoiding unnecessary calls to the database was a reason why I wasn't particularly happy with the current design. – Paul Aug 10 '12 at 05:07