1

I am quiet new in database designing, I am trying one test case to track students. In below image, student can either be in school or club. For this I have create on LocationId which act as a global id for where ever the student is. enter image description here

But the problem is I am depending on TypeId to determine if its Club or school. So in my data access query I have to make cases. Pseudo code is :

if TypeId == 1
search in club for the LocationId and get the clubId.
else if TypeId == 2
search in school for the LocationId and get the schoolId.

How can I get rid of these cases and still be maintaining the normalized rule.

Thanks a lot for reading. Any comments are welcome. Good day!

ThomasBecker
  • 388
  • 6
  • 20
  • 1
    Not sure if you need `LOC_CONTAINER` table at all. You can `left join` with both `CLUB` and `SCHOOL` tables and use only fields you need. – keltar Mar 03 '15 at 10:27
  • You mean to completely get rid of LocationId? Can u please write the query in solution may be. – ThomasBecker Mar 03 '15 at 10:32
  • 1
    How about merging your SCHOOL and CLUB tables together and add extra column as TYPE to distinguish them, in this case you can also drop LOC_CONTAINER. – Rahul Bhati Mar 03 '15 at 10:33
  • 1
    Merging `SCHOOL` and `CLUB` was my first though, but then it was like "well, maybe at some point later they will be different". Query to get id would be e.g. `select student.id, coalesce(CLUB.id, SCHOOL.id) from STUDENT left join CLUB on CLUB.id = STUDENT.location_id left join SCHOOL on SCHOOL.id = STUDENT.location_id`. – keltar Mar 03 '15 at 10:40
  • @keltar you are right keltar, merging was not an option as these 2 tables will be different later, also I wanted to use FK instead of join incase of use of ODATA wcf data service as it not support join but support "expand" using FK. But i guess I am thinking too much may be :) – ThomasBecker Mar 03 '15 at 10:57

1 Answers1

0

This seems to be a case of table inheritance and there is more than one way to solve it. Your solution with LOC_CONTAINER doesn't work (as you have noticed) as it requires outside code to do the checking.

Take a look at this comprehensive answer about inheritance. You could for example unify SCHOOL and CLUB tables into one table called PLACE or alternatively have both SCHOOL and CLUB columns in the table STUDENT with a constraint that one of them has to be NULL.

Community
  • 1
  • 1
Simo Kivistö
  • 4,247
  • 3
  • 38
  • 42
  • Thanks but I couldnot merge school and club as later it will be different, joining is an option which is mentioned by other colleagues, but again relation between tables is then missing which may be required by the client using dataservice. – ThomasBecker Mar 03 '15 at 11:02
  • Ok, take a look at the Class Table Inheritance case provided in the answer I linked to. In that case you have the primary key of both school and club referencing to a common table, not unlike your `LOC_CONTAINER`. – Simo Kivistö Mar 03 '15 at 11:21
  • Thanks again I look at ur link of Class Table Inheritance which is very close to solve my problem, only Question I have For eg. X has policyId how its possible to know if it has "property_address" or "vehicle_reg_no", One way is to search policy_id in all sub tables but i guess its bad way. in my case its reflected by knowing locationId how its possible to know whether this is in School or club. I hope u get d point. – ThomasBecker Mar 03 '15 at 13:20
  • You can either `JOIN` the tables and test for `NULL` values or at that point use the programming logic. You will need the logic at the UI side anyhow as the UI may differ lightly depending on whether the student is at school or club. – Simo Kivistö Mar 03 '15 at 16:27
  • Ya I am doing the same thing now as you have mentioned, but I just wondered if you have say 10 sub tables instead of 2, then UI logic becomes more mess and I think DBA may avoid this situation in some manner I guess. Any ways thanks for the answers and comments as it helps me learn about different cases. For this thing I am marking ur solution as answer. – ThomasBecker Mar 04 '15 at 09:54