CODEWITHSUNDEEP
sqldatabasems-accessdatabase-design
Celdor
Celdor

Reputation: 2597

Database design issue with different hierarchical structures to store

I have a slight issue with a database design. I don't how to solve a problem with different hierarchical structure of companies I have to take care of and store these information. I am trying to make database that can store info about people who I have already contacted or I am going to contact. I also need to store details regarding the company that a particular person work for. The problem is that companies have different structures. It may also be a university with schools and faculties, research institutes if I consider an academic role of someone's job. For instance, a person works at the Univetsity of Oxford, at Schoold of Languages that is part of Faculty of Something. Now, another person may work for government etc in Department of Something. How would you solve this design related issue? I have to do it and I am not quite experienced with it yet. I hope it is not confusing.

Upvotes: 0

Views: 696

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

The exact answer to your question really depends on the precise requirements that you are yet to provide. That being said, the following idea is probably not a bad starting point from which to develop your database model:

enter image description here

  • Organizations form a "wood" and each "tree" in that wood has "root" with NULL in PARENT_ORGANIZATION_ID.
  • A single person can work for more than one organization and (of course) an organization can employ multiple persons. This many-to-many relationship is represented by a junction table WORKS_AT.

Upvotes: 2

Dan Hermann
Dan Hermann

Reputation: 1107

It's not really clear that the fact that contacts' organizations may have different kinds of structures is relevant to your database design.

I would start with something such as the following:

CREATE TABLE Contact (
  ContactId int,
  OrganizationId int,
  LastName varchar(50),
  FirstName varchar(50),
  etc...
)

CREATE TABLE Organization (
  OrganizationId int,
  OrganizationTypeId int,
  Name varchar(50),
  StreetAddress varchar(100),
  etc...
)

CREATE TABLE OrganizationType (
  OrganizationTypeId int,
  Name varchar(50),
  NonProfit bit,
  etc...
)

In the above scenario, you can associate contacts with a particular organization and you can classify organization according to different categories if that is important to you.

Upvotes: 1

Related Questions