2

I'm fairly new to SQL and MySQL. I am designing a relational database within MySQL to store the contact information for various people that I meet at networking events. I would like to make the database as normalized as possible but I am running into difficulty when designing the hierarchical relationship between Company, Division and Job Titles. I am still only designing this database on paper.

For example in Finance, you could work for J.P. Morgan within their Investment Banking division. This division has several sub-divisions such as ECM. And within ECM there are other sub-divisions. Thus, one person may be J.P. Morgan > Investment Banking > ECM > Sales > Analyst with Investment Banking, ECM, and Sales all being sub-divisions of one another, J.P. Morgan being the company and Analyst being the Job.

However, for other professions, such as being selfemployed, a job may not be a part of any division. Thus, SelfEmployedBiz > SelfEmployed.

Clearly, my problem is that some Jobs belong to several subdivisions while other jobs belong to zero subdivisions.

My question: How do I go about designing this part of the database? The only thing that I can think of is to add several Division tables (TopDivision, SecondDivision, ThirdDivision, etc) but that seems both messy and awkward. Is there a better way?

How do I design a

Cœur
  • 37,241
  • 25
  • 195
  • 267
user2059128
  • 31
  • 4

1 Answers1

1

You can have self referencing relationships in the divisions table. Like this: http://schemadeck.com/c3uuhb

parent_division_id references another row in the same table.

nathancahill
  • 10,452
  • 9
  • 51
  • 91