January 17, 2017 at 3:09 pm
Any chance you can change the data model? It doesn't make any sense to have a separate table for students and employees, as you could easily incorporate a PersonType field that can identify which is which, and remove the JOIN complexity associated with that. However, that said, I would think that something along the lines of the following might work:
SELECT COALESCE(S.email, E.email) AS email
FROM Messages AS M
INNER JOIN MessageThreads AS MT
ON M.ThreadId = MT.ThreadId
INNER JOIN MessageThreadParticipants AS MTP
ON MT.ThreadId = MTP.ThreadId
INNER JOIN People AS P
ON MTP.PeopleId = P.PeopleId
INNER JOIN MessageReadStatus AS MRS
ON P.PeopleId = MRS.PeopleId
AND M.MessageId = MRS.MessageId
LEFT OUTER JOIN Students AS S
ON P.StudentId = S.StudentId
LEFT OUTER JOIN Employees AS E
ON P.EmployeeId = E.EmployeeId
WHERE MRS.ReadData = <value indicating the message is unread>
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 17, 2017 at 3:24 pm
sgmunson - Tuesday, January 17, 2017 3:09 PMAny chance you can change the data model? It doesn't make any sense to have a separate table for students and employees, as you could easily incorporate a PersonType field that can identify which is which, and remove the JOIN complexity associated with that. However, that said, I would think that something along the lines of the following might work:
SELECT COALESCE(S.email, E.email) AS email
FROM Messages AS M
INNER JOIN MessageThreads AS MT
ON M.ThreadId = MT.ThreadId
INNER JOIN MessageThreadParticipants AS MTP
ON MT.ThreadId = MTP.ThreadId
INNER JOIN People AS P
ON MTP.PeopleId = P.PeopleId
INNER JOIN MessageReadStatus AS MRS
ON P.PeopleId = MRS.PeopleId
AND M.MessageId = MRS.MessageId
LEFT OUTER JOIN Students AS S
ON P.StudentId = S.StudentId
LEFT OUTER JOIN Employees AS E
ON P.EmployeeId = E.EmployeeId
WHERE MRS.ReadData = <value indicating the message is unread>
Actually it can make sense to have a separate table for employees and students, because the same person can be both an employee and a student and because there are attributes that apply to students that do not apply to employees and vice versa.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 17, 2017 at 4:40 pm
drew.allen - Tuesday, January 17, 2017 3:24 PMsgmunson - Tuesday, January 17, 2017 3:09 PMAny chance you can change the data model? It doesn't make any sense to have a separate table for students and employees, as you could easily incorporate a PersonType field that can identify which is which, and remove the JOIN complexity associated with that. However, that said, I would think that something along the lines of the following might work:
SELECT COALESCE(S.email, E.email) AS email
FROM Messages AS M
INNER JOIN MessageThreads AS MT
ON M.ThreadId = MT.ThreadId
INNER JOIN MessageThreadParticipants AS MTP
ON MT.ThreadId = MTP.ThreadId
INNER JOIN People AS P
ON MTP.PeopleId = P.PeopleId
INNER JOIN MessageReadStatus AS MRS
ON P.PeopleId = MRS.PeopleId
AND M.MessageId = MRS.MessageId
LEFT OUTER JOIN Students AS S
ON P.StudentId = S.StudentId
LEFT OUTER JOIN Employees AS E
ON P.EmployeeId = E.EmployeeId
WHERE MRS.ReadData = <value indicating the message is unread>Actually it can make sense to have a separate table for employees and students, because the same person can be both an employee and a student and because there are attributes that apply to students that do not apply to employees and vice versa.
Drew
I don't believe those attributes should actually be stored in the People table nor that there should be two separate tables for this. Every time I've seen someone do such a thing, something bad has gone wrong or become much more difficult because of the separation.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2017 at 8:52 am
Jeff Moden - Tuesday, January 17, 2017 4:40 PMdrew.allen - Tuesday, January 17, 2017 3:24 PMsgmunson - Tuesday, January 17, 2017 3:09 PMAny chance you can change the data model? It doesn't make any sense to have a separate table for students and employees, as you could easily incorporate a PersonType field that can identify which is which, and remove the JOIN complexity associated with that. However, that said, I would think that something along the lines of the following might work:
SELECT COALESCE(S.email, E.email) AS email
FROM Messages AS M
INNER JOIN MessageThreads AS MT
ON M.ThreadId = MT.ThreadId
INNER JOIN MessageThreadParticipants AS MTP
ON MT.ThreadId = MTP.ThreadId
INNER JOIN People AS P
ON MTP.PeopleId = P.PeopleId
INNER JOIN MessageReadStatus AS MRS
ON P.PeopleId = MRS.PeopleId
AND M.MessageId = MRS.MessageId
LEFT OUTER JOIN Students AS S
ON P.StudentId = S.StudentId
LEFT OUTER JOIN Employees AS E
ON P.EmployeeId = E.EmployeeId
WHERE MRS.ReadData = <value indicating the message is unread>Actually it can make sense to have a separate table for employees and students, because the same person can be both an employee and a student and because there are attributes that apply to students that do not apply to employees and vice versa.
Drew
I don't believe those attributes should actually be stored in the People table nor that there should be two separate tables for this. Every time I've seen someone do such a thing, something bad has gone wrong or become much more difficult because of the separation.
The problem with relying on anecdotal evidence is that it may not be representative of the full range of possible outcomes, particularly since there is an inherent observational bias. Also, we all know that it's much easier to come up with a bad design than a good design. The fact that you have only encountered bad designs does not mean that the approach is flawed only that those particular design were flawed.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2017 at 4:43 pm
drew.allen - Wednesday, January 18, 2017 8:52 AMJeff Moden - Tuesday, January 17, 2017 4:40 PMdrew.allen - Tuesday, January 17, 2017 3:24 PMsgmunson - Tuesday, January 17, 2017 3:09 PMAny chance you can change the data model? It doesn't make any sense to have a separate table for students and employees, as you could easily incorporate a PersonType field that can identify which is which, and remove the JOIN complexity associated with that. However, that said, I would think that something along the lines of the following might work:
SELECT COALESCE(S.email, E.email) AS email
FROM Messages AS M
INNER JOIN MessageThreads AS MT
ON M.ThreadId = MT.ThreadId
INNER JOIN MessageThreadParticipants AS MTP
ON MT.ThreadId = MTP.ThreadId
INNER JOIN People AS P
ON MTP.PeopleId = P.PeopleId
INNER JOIN MessageReadStatus AS MRS
ON P.PeopleId = MRS.PeopleId
AND M.MessageId = MRS.MessageId
LEFT OUTER JOIN Students AS S
ON P.StudentId = S.StudentId
LEFT OUTER JOIN Employees AS E
ON P.EmployeeId = E.EmployeeId
WHERE MRS.ReadData = <value indicating the message is unread>Actually it can make sense to have a separate table for employees and students, because the same person can be both an employee and a student and because there are attributes that apply to students that do not apply to employees and vice versa.
Drew
I don't believe those attributes should actually be stored in the People table nor that there should be two separate tables for this. Every time I've seen someone do such a thing, something bad has gone wrong or become much more difficult because of the separation.
The problem with relying on anecdotal evidence is that it may not be representative of the full range of possible outcomes, particularly since there is an inherent observational bias. Also, we all know that it's much easier to come up with a bad design than a good design. The fact that you have only encountered bad designs does not mean that the approach is flawed only that those particular design were flawed.
Drew
Drew,
I've seen a rather large number of "flawed designs" over the last 30 years, and the number I see keeps growing by leaps and bounds every time I start a new contract. Jeff is perfectly justified in relying on fact as opposed to mere anecdotal evidence. The amount of real "crap" out there is astounding. Much of it has so much organizational inertia that fixing it is impractically expensive, and in many cases, even migrating away from it is in a similar category of impractical. It's not that either Jeff or I have a bad attitude... it's just that we repeatedly see the bad stuff because we're the ones who get the call to try and fix them. Most of the time, the best we can do is apply a band-aid of sorts. However, I'm quite sure Jeff would be preppin' the beer popsicles for the ones he can actually do something really useful with. It's not uncommon for folks with a background that spans a relatively small number of longer-term employers to just not see that much of the crap, so I can't blame you for your point of view. You just need to realize that there's an extraordinarily large universe of stuff you can do best by hoping to never see...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 19, 2017 at 9:11 am
sgmunson - Wednesday, January 18, 2017 4:43 PMdrew.allen - Wednesday, January 18, 2017 8:52 AMJeff Moden - Tuesday, January 17, 2017 4:40 PMdrew.allen - Tuesday, January 17, 2017 3:24 PMsgmunson - Tuesday, January 17, 2017 3:09 PMAny chance you can change the data model? It doesn't make any sense to have a separate table for students and employees, as you could easily incorporate a PersonType field that can identify which is which, and remove the JOIN complexity associated with that. However, that said, I would think that something along the lines of the following might work:
SELECT COALESCE(S.email, E.email) AS email
FROM Messages AS M
INNER JOIN MessageThreads AS MT
ON M.ThreadId = MT.ThreadId
INNER JOIN MessageThreadParticipants AS MTP
ON MT.ThreadId = MTP.ThreadId
INNER JOIN People AS P
ON MTP.PeopleId = P.PeopleId
INNER JOIN MessageReadStatus AS MRS
ON P.PeopleId = MRS.PeopleId
AND M.MessageId = MRS.MessageId
LEFT OUTER JOIN Students AS S
ON P.StudentId = S.StudentId
LEFT OUTER JOIN Employees AS E
ON P.EmployeeId = E.EmployeeId
WHERE MRS.ReadData = <value indicating the message is unread>Actually it can make sense to have a separate table for employees and students, because the same person can be both an employee and a student and because there are attributes that apply to students that do not apply to employees and vice versa.
Drew
I don't believe those attributes should actually be stored in the People table nor that there should be two separate tables for this. Every time I've seen someone do such a thing, something bad has gone wrong or become much more difficult because of the separation.
The problem with relying on anecdotal evidence is that it may not be representative of the full range of possible outcomes, particularly since there is an inherent observational bias. Also, we all know that it's much easier to come up with a bad design than a good design. The fact that you have only encountered bad designs does not mean that the approach is flawed only that those particular design were flawed.
Drew
Drew,
I've seen a rather large number of "flawed designs" over the last 30 years, and the number I see keeps growing by leaps and bounds every time I start a new contract. Jeff is perfectly justified in relying on fact as opposed to mere anecdotal evidence. The amount of real "crap" out there is astounding. Much of it has so much organizational inertia that fixing it is impractically expensive, and in many cases, even migrating away from it is in a similar category of impractical. It's not that either Jeff or I have a bad attitude... it's just that we repeatedly see the bad stuff because we're the ones who get the call to try and fix them. Most of the time, the best we can do is apply a band-aid of sorts. However, I'm quite sure Jeff would be preppin' the beer popsicles for the ones he can actually do something really useful with. It's not uncommon for folks with a background that spans a relatively small number of longer-term employers to just not see that much of the crap, so I can't blame you for your point of view. You just need to realize that there's an extraordinarily large universe of stuff you can do best by hoping to never see...
The problem is that that's not what Jeff was doing. He was relying on anecdotal evidence ("every time I've seen someone do such a thing") instead of facts, not the other way around. Yes, there are lots of bad designs out there, but Jeff was arguing that because he's seen a lot of bad designs, the approach is wrong. I'm reminded of a quote attributed to Thomas Edison, "I have not failed. I've just found 10000 ways that won't work." Jeff has found lots of ways that don't work, but that doesn't mean that none of them will work.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 19, 2017 at 12:33 pm
drew.allen - Thursday, January 19, 2017 9:11 AMsgmunson - Wednesday, January 18, 2017 4:43 PMdrew.allen - Wednesday, January 18, 2017 8:52 AMJeff Moden - Tuesday, January 17, 2017 4:40 PMdrew.allen - Tuesday, January 17, 2017 3:24 PMsgmunson - Tuesday, January 17, 2017 3:09 PMAny chance you can change the data model? It doesn't make any sense to have a separate table for students and employees, as you could easily incorporate a PersonType field that can identify which is which, and remove the JOIN complexity associated with that. However, that said, I would think that something along the lines of the following might work:
SELECT COALESCE(S.email, E.email) AS email
FROM Messages AS M
INNER JOIN MessageThreads AS MT
ON M.ThreadId = MT.ThreadId
INNER JOIN MessageThreadParticipants AS MTP
ON MT.ThreadId = MTP.ThreadId
INNER JOIN People AS P
ON MTP.PeopleId = P.PeopleId
INNER JOIN MessageReadStatus AS MRS
ON P.PeopleId = MRS.PeopleId
AND M.MessageId = MRS.MessageId
LEFT OUTER JOIN Students AS S
ON P.StudentId = S.StudentId
LEFT OUTER JOIN Employees AS E
ON P.EmployeeId = E.EmployeeId
WHERE MRS.ReadData = <value indicating the message is unread>Actually it can make sense to have a separate table for employees and students, because the same person can be both an employee and a student and because there are attributes that apply to students that do not apply to employees and vice versa.
Drew
I don't believe those attributes should actually be stored in the People table nor that there should be two separate tables for this. Every time I've seen someone do such a thing, something bad has gone wrong or become much more difficult because of the separation.
The problem with relying on anecdotal evidence is that it may not be representative of the full range of possible outcomes, particularly since there is an inherent observational bias. Also, we all know that it's much easier to come up with a bad design than a good design. The fact that you have only encountered bad designs does not mean that the approach is flawed only that those particular design were flawed.
Drew
Drew,
I've seen a rather large number of "flawed designs" over the last 30 years, and the number I see keeps growing by leaps and bounds every time I start a new contract. Jeff is perfectly justified in relying on fact as opposed to mere anecdotal evidence. The amount of real "crap" out there is astounding. Much of it has so much organizational inertia that fixing it is impractically expensive, and in many cases, even migrating away from it is in a similar category of impractical. It's not that either Jeff or I have a bad attitude... it's just that we repeatedly see the bad stuff because we're the ones who get the call to try and fix them. Most of the time, the best we can do is apply a band-aid of sorts. However, I'm quite sure Jeff would be preppin' the beer popsicles for the ones he can actually do something really useful with. It's not uncommon for folks with a background that spans a relatively small number of longer-term employers to just not see that much of the crap, so I can't blame you for your point of view. You just need to realize that there's an extraordinarily large universe of stuff you can do best by hoping to never see...
The problem is that that's not what Jeff was doing. He was relying on anecdotal evidence ("every time I've seen someone do such a thing") instead of facts, not the other way around. Yes, there are lots of bad designs out there, but Jeff was arguing that because he's seen a lot of bad designs, the approach is wrong. I'm reminded of a quote attributed to Thomas Edison, "I have not failed. I've just found 10000 ways that won't work." Jeff has found lots of ways that don't work, but that doesn't mean that none of them will work.
Drew
I think you're missing the point. Just because you can "make it work", doesn't mean it's a good idea. The future consequences of such decisions are predominantly bad, and not just because Jeff has seen it a lot, but because I and most of the other SQL development contractors I know, that know much about databases have consistently been brought in to try and "put lipstick on a pig", where multiple truly ugly pigs are lined up awaiting their "facial transformation". On those occasions when we manage to get pork or bacon out of the pig instead of a bloody mess with no meat, we celebrate. It just doesn't happen often enough to conclude that "making it work without changing it" is all that likely to be a good idea, and I'm pretty sure that's what he was getting at. And no, it's not "anecdotal" when it's so darn common.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 19, 2017 at 1:57 pm
sgmunson - Thursday, January 19, 2017 12:33 PMdrew.allen - Thursday, January 19, 2017 9:11 AMsgmunson - Wednesday, January 18, 2017 4:43 PMdrew.allen - Wednesday, January 18, 2017 8:52 AMJeff Moden - Tuesday, January 17, 2017 4:40 PMdrew.allen - Tuesday, January 17, 2017 3:24 PMsgmunson - Tuesday, January 17, 2017 3:09 PMAny chance you can change the data model? It doesn't make any sense to have a separate table for students and employees, as you could easily incorporate a PersonType field that can identify which is which, and remove the JOIN complexity associated with that. However, that said, I would think that something along the lines of the following might work:
SELECT COALESCE(S.email, E.email) AS email
FROM Messages AS M
INNER JOIN MessageThreads AS MT
ON M.ThreadId = MT.ThreadId
INNER JOIN MessageThreadParticipants AS MTP
ON MT.ThreadId = MTP.ThreadId
INNER JOIN People AS P
ON MTP.PeopleId = P.PeopleId
INNER JOIN MessageReadStatus AS MRS
ON P.PeopleId = MRS.PeopleId
AND M.MessageId = MRS.MessageId
LEFT OUTER JOIN Students AS S
ON P.StudentId = S.StudentId
LEFT OUTER JOIN Employees AS E
ON P.EmployeeId = E.EmployeeId
WHERE MRS.ReadData = <value indicating the message is unread>Actually it can make sense to have a separate table for employees and students, because the same person can be both an employee and a student and because there are attributes that apply to students that do not apply to employees and vice versa.
Drew
I don't believe those attributes should actually be stored in the People table nor that there should be two separate tables for this. Every time I've seen someone do such a thing, something bad has gone wrong or become much more difficult because of the separation.
The problem with relying on anecdotal evidence is that it may not be representative of the full range of possible outcomes, particularly since there is an inherent observational bias. Also, we all know that it's much easier to come up with a bad design than a good design. The fact that you have only encountered bad designs does not mean that the approach is flawed only that those particular design were flawed.
Drew
Drew,
I've seen a rather large number of "flawed designs" over the last 30 years, and the number I see keeps growing by leaps and bounds every time I start a new contract. Jeff is perfectly justified in relying on fact as opposed to mere anecdotal evidence. The amount of real "crap" out there is astounding. Much of it has so much organizational inertia that fixing it is impractically expensive, and in many cases, even migrating away from it is in a similar category of impractical. It's not that either Jeff or I have a bad attitude... it's just that we repeatedly see the bad stuff because we're the ones who get the call to try and fix them. Most of the time, the best we can do is apply a band-aid of sorts. However, I'm quite sure Jeff would be preppin' the beer popsicles for the ones he can actually do something really useful with. It's not uncommon for folks with a background that spans a relatively small number of longer-term employers to just not see that much of the crap, so I can't blame you for your point of view. You just need to realize that there's an extraordinarily large universe of stuff you can do best by hoping to never see...
The problem is that that's not what Jeff was doing. He was relying on anecdotal evidence ("every time I've seen someone do such a thing") instead of facts, not the other way around. Yes, there are lots of bad designs out there, but Jeff was arguing that because he's seen a lot of bad designs, the approach is wrong. I'm reminded of a quote attributed to Thomas Edison, "I have not failed. I've just found 10000 ways that won't work." Jeff has found lots of ways that don't work, but that doesn't mean that none of them will work.
Drew
I think you're missing the point. Just because you can "make it work", doesn't mean it's a good idea. The future consequences of such decisions are predominantly bad, and not just because Jeff has seen it a lot, but because I and most of the other SQL development contractors I know, that know much about databases have consistently been brought in to try and "put lipstick on a pig", where multiple truly ugly pigs are lined up awaiting their "facial transformation". On those occasions when we manage to get pork or bacon out of the pig instead of a bloody mess with no meat, we celebrate. It just doesn't happen often enough to conclude that "making it work without changing it" is all that likely to be a good idea, and I'm pretty sure that's what he was getting at. And no, it's not "anecdotal" when it's so darn common.
The problem is that I didn't look at the small image of the data structure that was posted at the top of the thread. I didn't realize how truly awful this particular design is. There may still be a reason to keep separate student and employee tables, but not based on the data provided.
For example, both the student and employee tables have name attributes. These are not facts about them as students or employees, these are facts about them as people, so the name belongs on the people table.
Also, the foreign keys are backwards. The people table contains foreign keys to the student and employee tables, when it is the students and employees tables that should have foreign keys to the people table.
As it is currently set up, a person who is both a student and an employee can have one name on their student record and another on their employee record. And there is no way to name a person who is neither a student nor an employee. You can also have a student or employee who is not a person.
In short, this specific design has very serious issues.
Drew
PS: I really hate that ALL of the primary keys are labelled ID. It makes it very difficult to remember which particular ID field you are talking about.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply