0

While designing a relational database I got the questoin in my head if the design I made could be made easier. The design consists of 4 tables: (simplified)

  • location
  • office
  • school
  • group of schools

ERD PK = primary key, FK = foreign key

Lets say we want to get details about a location. If the location is a school we want to see the name, amount of students and the name of the group of schools its part of. If it is a office we only want to see the name of the office. With the current design this is impossible to do in 1 query (with my knowledge).

question: Is there a better way to design this database so I can get the needed details about a location in 1 query.

Although there may be a way to get these details in 1 query I am more interested in enhancing the database design.

Thanks in advance, Knarfi

ANSWER: I finally found the term that describes my problem: Polymorphic Association. Especially this question gave me a good answer.

Community
  • 1
  • 1
Frank
  • 83
  • 2
  • 10

3 Answers3

1

You can try something like this

SELECT location.*,school.*,office.*
FROM location
LEFT JOIN school ON school.locationID=location.locationID
LEFT JOIN office ON office.locationID=location.locationID

In result you will have NULL values for school fields if there will be office and vice versa. But you have to check this in your application and make decision there - database sends you all data.

Tomas Majer
  • 44
  • 2
  • Although this is usefull I am really hoping to not make those decisions in the application. So I am really hoping to enhance the database since I really think this current setup is a bit clumsy. – Frank Apr 27 '15 at 20:35
1

You can merge School and Office to one Entity, say: Organization with the following attributes:

  • Organization_ID (PM)
  • Type (1=School, 2=Office)
  • Location_ID (FK)
  • Group_ID (FK) (it can have values only for the schools, unless you want to add group info for offices too ex: Accounting, Sales, etc in table Groups)
  • Name
  • NumOfMembers (it can be applied to both schools and offices)

About your last question: "Although there may be a way to get these details in 1 query I am more interested in enhancing the database design." I must say that this is not a design question but an implementation one. Remember that it is a very bad practice to change your design according to your implementation needs...

In any case you can use ONE query in both cases (yours and mine) see other people answers. tIp: try to use views... they are usefull in such cases.

Andreas Venieris
  • 452
  • 3
  • 15
  • I thought of a lot of ways to merge the tables but the problem is that a organization entity would not fit in the design. The database is for a group of schools and so a organistatoin entity does not really fit in the database. And you are right about the fact it is rather a implementatoin question :) – Frank Apr 27 '15 at 20:52
1

The database design doesn't restrict a location from having both a school and office related to it at the same time. (Could be multiple school and/or multiple office.)

There's several possible ways to retrieve information related to location.

Here's one approach:

SELECT 'school' AS source
     , s.name
     , s.students
     , g.name AS school_group_name
  FROM location sl
  JOIN school s
    ON s.location_id = sl.id
  LEFT
  JOIN group_of_schools g
    ON g.id = s.group_id
 UNION ALL
SELECT 'office' AS source
     , o.name
     , NULL
     , NULL
  FROM location ol
  JOIN office o
    ON o.location_id = ol.id

If you need to restrict to a specific set of location, you'd need to add WHERE clause on each SELECT.

Add an ORDER BY if you want the rows returned in a particular order.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • I do like the approach, but I am more interested in the first fact you pointed out, right now a location can be a school and a office... Do you know a way to solve this problem? I think that is the improvement I am looking for! – Frank Apr 27 '15 at 20:37
  • 1
    There are several approaches to mapping an inheritance hierarchy to relational tables; each approach as benefits and drawbacks, and the best option depends on your use cases. Based on the information in your question, merging location, school and office into a single table may be a suitable approach. Add a discriminator column (to indicate whether a location is an instance of "school" subclass,an instance of "office" subclass, or just an instance of "location" super class. And add columns for the attributes specific to each subclass. ... – spencer7593 Apr 28 '15 at 03:08
  • 1
    I recommend Scott Ambler's article for a review of the O/R mapping approaches to handling the "impedance mismatch" between OO class hiearchy and relational database tables: [**http://www.agiledata.org/essays/mappingObjects.html**](http://www.agiledata.org/essays/mappingObjects.html). (For example, in your current model, you could make `location_id` unique in `school` and `office`, but there's no declarative constraint in the database to prevent a location from being both a school and an office.) – spencer7593 Apr 28 '15 at 03:09