SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


wHICH MODEL IS BEST?


wHICH MODEL IS BEST?

Author
Message
onlygoodones
onlygoodones
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 215
Hello every one, here I need your help in deciding which model is a good practice and easy to maintain:
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


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
aa john@test.com 'A,B,C,D'
bb rick@test.com 'B,D'
cc sally@test.com 'C,D"
dd aha@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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93495 Visits: 38955
Model 1. Doesn't require the parsing of the data that Model 2 would.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
onlygoodones
onlygoodones
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 215
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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93495 Visits: 38955
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
onlygoodones
onlygoodones
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 215
Thanks Lynn. Good solution. I will follow that.
DiverKas
DiverKas
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 460
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! Smile
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17499 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
MVDBA
MVDBA
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5451 Visits: 860

If this was a geography forum, you would be the flat earth kidw00t



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
DiverKas
DiverKas
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 460
michael vessey (6/8/2012)

If this was a geography forum, you would be the flat earth kidw00t



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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search