1

Let's say u have following architecture.

User have many departments, (fk user_I'd)

Each department HV many offices (fk department_I'd)

Each office HV many transactions ( encounter s) (fk office_id )

Now question is, if I want to get all encounters of a user I will need to search for all encoutets in offices in department s of a user.

That's lots of joins don't u think ?

Yet if I include all foreign keys of the tree in encounter table ? Then I can just do select * from encounters where user_I'd = X

Yet this makes my tables looks ugly and contain lots of repeated data of parent I'd !

So my question is, what is best practice in such case ? More fk or less and use joins ?

Zalaboza
  • 8,899
  • 16
  • 77
  • 142
  • What are encounters in your business model ? I think the answer may also depends on the number of times you will need to request the encounters of a user vs the encounters of an office. If you only care about encounters for users, then you should change your model to do less joins on this request. If it's a rare request, having a slowest one is okay for a clearer more natural design. – Pierre Feb 16 '14 at 18:05
  • dan ehr system, encounters are a patient-doctor interaction that usually occur inside an office -yet one encounter can have many doctors.. i kept it simple for sake of example – Zalaboza Feb 17 '14 at 00:22

3 Answers3

0

When I design my tables, I usually make no redundancy whatsoever, and rather keep the complexity of multiple joins inside my Service layer on the server-side code. It allows more flexibility when you want to change the design afterwards.

That may be a matter of religion rather than pure logic, so you may need more answers of the sort to decide.

Pierre
  • 853
  • 9
  • 21
  • i like that.. yet not sure if i can afford such flexibility impact on performance, yet i will keep it in mind :), thanks – Zalaboza Feb 17 '14 at 00:20
0

First, that's really not that bad of a hierarchical structure. If you're going to be doing a lot of queries where you want to get all of the encounters associated with a particular user, create view. This might look like:

create view Encounters as
select
    Transactions.Id as TransactionId,
    Offices.Id as OfficeId,
    Departments.Id as DepartmentId,
    Users.Id as UserId
    -- other fields
from Transactions
join Offices on Transactions.OfficeId = Offices.Id
join Departments on Offices.DepartmentId = Departments.Id
join Users on Departments.UserId = Users.Id

This way you can execute the query for any of the Id fields without having to recreate the joins each time, select * from Encounters where UserId = 12345

Scott Corbett
  • 380
  • 1
  • 7
0

Wouldn't call three a lot myself. Only way to know if the benefit of denormalising encounters table outweighs the cost, is to see if you get a problem and fix it with denormalisation. To make that decision now would be premature in my opinion.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39