May 18, 2012 at 12:39 pm
Hello every one, here I need your help in deciding which model is a good practice and easy to maintain:
MODEL 01:
name email ABCD
aajohn@test.com0011
bbrick@test.com0101
ccsally@test.com0110
ddaha@test.com1100
I have a table with columns A-D which are of the BIT data type. Each column represents a different scenario. To keep track of whether or not a particular type of email needs to be sent to the person (their row) 0 - indicates email sent and 1 - indicates, email needs to be sent out. As emails (different scenario's) are added to the system this model requires a new column to be added and widens the table. I’m wondering if the email type (A-D) could be stored in a list column like outline below:
MODEL 02:
name email X
aajohn@test.com'A,B,C,D'
bbrick@test.com'B,D'
ccsally@test.com'C,D"
ddaha@test.com'A,B'
In the above model, after the email is sent out for scenario A, it should dis appear from 'A,B,C,D' indication emails yet to be sent for B,C and D.
Is there a construct in SQL Server to handles columns of a list data type? Would performance suffer when querying this table with the “LIKE” command? Is there another way to query this column, like a ListFind() function? Would I have to handle all maintenance to ensure that the list is always well formed from the application code? Should I just stick with the original model and deal with all of the columns per email? I’d rather not create another table in this case.
May 18, 2012 at 12:45 pm
Model 1. Doesn't require the parsing of the data that Model 2 would.
May 18, 2012 at 12:57 pm
Thanks Lynn for the quick reply. Will there be any performance difference between the two. Meaning 1) having more columns in the table VS parsing a single column?
May 18, 2012 at 1:06 pm
MODEL 01:
name email A B C D
aa john@test.com 0 0 1 1
bb rick@test.com 0 1 0 1
cc sally@test.com 0 1 1 0
dd aha@test.com 1 1 0 0
What would make better sense is to take the four columns: A,B,C,D and move them into a separate table:
MODEL 01A
NameID Name EMail
1 aa john@test.com
2 bb rick@test.com
3 cc sally@test.com
4 dd aha@test.com
NameID Mode
1 C
1 D
2 B
2 D
3 B
3 C
4 A
4 B
This allows adding new modes by adding a row and deleting a mode by deleting a row.
May 18, 2012 at 1:40 pm
Thanks Lynn. Good solution. I will follow that.
May 31, 2012 at 1:13 pm
It would be best to have one table with name and email, foreign keyed to another table that had the values as rows for each name and email. This would give the same observant performance, without having to parse data AND its scalable and requires NO data changes as you add more values to the range.
That is afterall, what relational databases do best! 🙂
June 8, 2012 at 4:18 am
Lynn Pettis (5/18/2012)
MODEL 01:name email A B C D
aa john@test.com 0 0 1 1
bb rick@test.com 0 1 0 1
cc sally@test.com 0 1 1 0
dd aha@test.com 1 1 0 0
What would make better sense is to take the four columns: A,B,C,D and move them into a separate table:
MODEL 01A
NameID Name EMail
1 aa john@test.com
2 bb rick@test.com
3 cc sally@test.com
4 dd aha@test.com
NameID Mode
1 C
1 D
2 B
2 D
3 B
3 C
4 A
4 B
This allows adding new modes by adding a row and deleting a mode by deleting a row.
I want to help! If you've mistakenly used MODEL 01 and you want to convert to Lynn's MODEL 01A, you can do it this way:
DECLARE @t TABLE
(NameID INT IDENTITY, name VARCHAR(20), email VARCHAR(20), A INT, B INT, C INT, D INT)
INSERT INTO @t
SELECT 'aa','john@test.com',0, 0, 1, 1
UNION ALL SELECT 'bb','rick@test.com',0, 1, 0, 1
UNION ALL SELECT 'cc','sally@test.com',0, 1, 1, 0
UNION ALL SELECT 'dd','aha@test.com',1, 1, 0, 0
-- MODEL 01:
SELECT * FROM @t
-- MODEL 01A (table 2):
SELECT NameID, Mode
FROM @t
CROSS APPLY (
VALUES (CASE A WHEN 1 THEN 'A' ELSE NULL END)
,(CASE B WHEN 1 THEN 'B' ELSE NULL END)
,(CASE C WHEN 1 THEN 'C' ELSE NULL END)
,(CASE D WHEN 1 THEN 'D' ELSE NULL END)) x(Mode)
WHERE Mode IS NOT NULL
That my friends, is my stupid SQL trick for this Friday afternoon!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 8, 2012 at 4:38 am
If this was a geography forum, you would be the flat earth kid:w00t:
Since you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
that kind of snooty reply doesn't serve the sql server central community well - the guy has asked a genuine question and lynn has replied with a valid response, which the OP has taken onboard and learned.
not all of us have time to sit and study, some people get thrown in at the deep end, and the OP had the sense to ask the question.
MVDBA
June 8, 2012 at 11:52 am
michael vessey (6/8/2012)
If this was a geography forum, you would be the flat earth kid:w00t:
Since you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
that kind of snooty reply doesn't serve the sql server central community well - the guy has asked a genuine question and lynn has replied with a valid response, which the OP has taken onboard and learned.
not all of us have time to sit and study, some people get thrown in at the deep end, and the OP had the sense to ask the question.
Actually, while Lynn's response probably helped the OP solve his original question, it did nothing to educate him in the right way to manage the data. Celko's response, while "snooty" is probably the advice the OP needs.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply