Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

wHICH MODEL IS BEST? Expand / Collapse
Author
Message
Posted Friday, May 18, 2012 12:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:20 AM
Points: 332, Visits: 196
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.

Post #1302759
Posted Friday, May 18, 2012 12:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:57 PM
Points: 23,009, Visits: 31,508
Model 1. Doesn't require the parsing of the data that Model 2 would.



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)
Post #1302762
Posted Friday, May 18, 2012 12:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:20 AM
Points: 332, Visits: 196
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?
Post #1302771
Posted Friday, May 18, 2012 1:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:57 PM
Points: 23,009, Visits: 31,508
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.



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)
Post #1302776
Posted Friday, May 18, 2012 1:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:20 AM
Points: 332, Visits: 196
Thanks Lynn. Good solution. I will follow that.
Post #1302807
Posted Thursday, May 31, 2012 1:13 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, 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! :)
Post #1309290
Posted Wednesday, June 6, 2012 3:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
Is there a construct in SQL Server to handles columns of a list data type?


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

Read any book -- and I mean ANY book -- on RDBMS. Look at First Normal Form, the basis of the entire relational model. Read Codd's 12 rules and look at the Information Principle in that list. Al relationships are shown by scalar values in the columns of rows of tables.

Since you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

It is really good and I use it for classes that have students without any SQL or math background.






Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1312221
Posted Friday, June 8, 2012 4:18 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 3,609, Visits: 5,222
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!
Post #1313014
Posted Friday, June 8, 2012 4:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:49 AM
Points: 2,276, Visits: 779

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



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
Post #1313029
Posted Friday, June 8, 2012 11:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
michael vessey (6/8/2012)

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



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.
Post #1313311
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse