Sql Design Question

mewso

Was thinking about this problem with querying a system I developed. It's a customer enquiry system. Take the following tables as an example:-

Users
Id
Username
Password
DeptId
DisplayName

Departments
Id
DepartmentName

Enquiries
Id
SubmittedBy
EnquiryContent
DateReceived
...

EnquiryReferrals
EnquiryId
UserId
DateReferred

Now this is a simplified example of the tables in question to explain the problem. The problem is to list enquiries of various criteria (by date, by overdue and so on) along with the name of the user it is currently referred to and their department. So as far as I can see this leaves me with no choice but to use sub queries (e.g. select displayname from users where id = (select top 1 userid from enquiryreferrals where enquiryid = enquiries.id)). Unless I am unaware of some fancy way of doing a join that limits the joined table to one row based on ordered date desc.

So my only conclusion is that it's bad design on my part (my excuse is I did it years ago and we are learning every day yada yada). Perhaps I should have a CurrentReferrals table with a 1 to 1 with Enquiries. It still leaves you with problems if someone wants a list of enquiries that were ever referred to a particular user or department but it's a help. What do others think? Is there a better design here or a better way of querying it?

hobochris

Can you clarify what it is exactly your are trying to retrieve?

which fields? and what conditions?

mewso

I'm saying any kind of query on the enquiries table that also need to include the user it is currently referred to and their department name. e.g.

select e.id, e.enquirycontent, e.datereceived,
(select displayname from users where id = (select top 1 userid from enquiryreferrals where enquiryid = e.id order by datereferred desc)) as CurrentUser,
(select departmentname from departments where id = 
(select DeptId from users where id = (select top 1 userid from  enquiryreferrals where enquiryid = e.id order by datereferred desc))
) as DepartmentName
from Enquiries e
where datereceived < getdate()

Pretty convoluted but I'm guessing here that I've caused this by my own bad design.

amen

unless I am missing something just use joins so

Select
e.id, e.enquirycontent, e.datereceived,U.DisplayName AS CurrentUser,D.Department
From
   Enquiries E
JOIN
   EnquiryReferrals EN 
ON 
     E.ID = EN.EnquiryID
JOIN
  Users U
ON 
   U .UserID = EN.UserId
JOIN
  Departments D
ON 
  D.ID = U.DepID

where datereceived < getdate()

mewso

As I said in the original post. You want the most recent referral information for each enquiry. Doing a join will get you all referrals so if an enquiry has been referred to 5 people you will get 5 rows for that enquiry and not just the most recent.

fasty

but if you get those 5 rows and sort by the most recent and select the top 1, is that what you want?

mewso

I must not have explained this properly. If I want one enquiry then yes it's trivial to simply filter out the unwanted rows.

If the query is, for example, a list of all enquiries received yesterday and you want a list of those enquires and the user and department they are currently with. Now if you just do a join then you get a list of records with the enquiries appearing multiple times and the user and department for every referral. With the sub query I posted as an example you get what you want (1 row per enquiry with the current referral info.) but it's not ideal. Thats why I'm wondering if the best design would have been to record the current referral in a separate table. I think it would be.

The other option would be to loop through your records in code and only create an object for your display collection if it's the current referral row in each case. Not sure how efficient this would be.

Jagera

I think I know what you need, so here's my two solutions (based on SQL Server):

Here's the ugly way to achieve what you are doing. It uses sub queries, but will make better use of indexes than your version. If you don't have too many rows, this will work fine.

select e.ID, e.EnquiryContent, e.DateReceived, u.DisplayName, d.DepartmentName
from Enquiries e
inner join (
	select er.EnquiryID, er.UserID, er.DateReferred
	from EnquiryReferrals er
	inner join (
		select EnquiryID, DateReferred=max(DateReferred) 
		from EnquiryReferrals 
		group by EnquiryID ) emax 
			on er.EnquiryID = emax.EnquiryID 
			and er.DateReferred=emax.DateReferred ) maxref on e.ID = maxref.EnquiryID
inner join Users u on maxref.UserID = u.ID
inner join Departments d on u.DeptID = d.ID

mewso wrote: »

Unless I am unaware of some fancy way of doing a join that limits the joined table to one row based on ordered date desc.

I think RANK and PARTITION are the "fancy way"

select * from
(
select e.ID, e.EnquiryContent, e.DateReceived, u.DisplayName, d.DepartmentName,
ReferRank = RANK () OVER (PARTITION by e.ID Order By er.DateReferred DESC) 
from Enquiries e
inner join EnquiryReferrals er on er.EnquiryID = e.ID
inner join Users u on er.UserID = u.ID
inner join Departments d on u.DeptID = d.ID
) res
where ReferRank = 1