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 «««3,9573,9583,9593,9603,961»»»

Are the posted questions getting worse? Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 5:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
What I would do is the join table scenario, something I actually use in my own workplace.

CREATE TABLE PhoneNumbers 
(PhoneID INT IDENTITY(1,1) PRIMARY KEY,
InternationalCode INT,
PhoneNumber VARCHAR (20) /* To allow for non-U.S. Numbers */
);

CREATE TABLE EntityPhone
(EntityPhoneID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EntityID INT NOT NULL,
EntityType VARCHAR (20),
PhoneID INT CONSTRAINT FK_PhoneID FOREIGN KEY REFERENCES PhoneNumbers(PhoneID)
);

The term "Entity" makes sense in this scenario given he appears to have multiple tables for different entity types. There's no reason why he can't have a single phone table and then a join table in the EntityPhone sense so that phone numbers can be "reused" and so can EntityIDs. In fact, if I were to expand on the design, I'd add an EffectiveDate and an EndDate in the table or create a history table where previous phone / entity associations are kept while only the current associations exist in the EntityPhone table itself.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1448766
Posted Thursday, May 2, 2013 6:24 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 20,801, Visits: 32,725
Okay, I have to ask without pointing to anything specific, do any of you think I have been harassing any of the OP's recently rather than helping them?



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 #1448780
Posted Thursday, May 2, 2013 7:09 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 675, Visits: 6,810
Brandie Tarvin (5/2/2013)
What I would do is the join table scenario, something I actually use in my own workplace.

CREATE TABLE PhoneNumbers 
(PhoneID INT IDENTITY(1,1) PRIMARY KEY,
InternationalCode INT,
PhoneNumber VARCHAR (20) /* To allow for non-U.S. Numbers */
);

CREATE TABLE EntityPhone
(EntityPhoneID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EntityID INT NOT NULL,
EntityType VARCHAR (20),
PhoneID INT CONSTRAINT FK_PhoneID FOREIGN KEY REFERENCES PhoneNumbers(PhoneID)
);

The term "Entity" makes sense in this scenario given he appears to have multiple tables for different entity types. There's no reason why he can't have a single phone table and then a join table in the EntityPhone sense so that phone numbers can be "reused" and so can EntityIDs. In fact, if I were to expand on the design, I'd add an EffectiveDate and an EndDate in the table or create a history table where previous phone / entity associations are kept while only the current associations exist in the EntityPhone table itself.


Phone is an interesting piece of information. Somewhat like Name, can change over time. And it is an attribute of a person or company. I can see doing consistent validation. But in the possible scenario of shared numbers, i.e. company switchboard, fax number, etc., a whole different can of worms is opened up maintaining. Consider when a person, who you have associated the main company phone number to, gets a direct line.
Makes a person think, although you quickly spiral down into a lot of work, and the value would be very questionable.
It does make for a good example of how to normalize data, something which I know Tom is very good at.
Post #1448798
Posted Thursday, May 2, 2013 8:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
Greg Edwards-268690 (5/2/2013)
Brandie Tarvin (5/2/2013)
What I would do is the join table scenario, something I actually use in my own workplace.

CREATE TABLE PhoneNumbers 
(PhoneID INT IDENTITY(1,1) PRIMARY KEY,
InternationalCode INT,
PhoneNumber VARCHAR (20) /* To allow for non-U.S. Numbers */
);

CREATE TABLE EntityPhone
(EntityPhoneID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EntityID INT NOT NULL,
EntityType VARCHAR (20),
PhoneID INT CONSTRAINT FK_PhoneID FOREIGN KEY REFERENCES PhoneNumbers(PhoneID)
);

The term "Entity" makes sense in this scenario given he appears to have multiple tables for different entity types. There's no reason why he can't have a single phone table and then a join table in the EntityPhone sense so that phone numbers can be "reused" and so can EntityIDs. In fact, if I were to expand on the design, I'd add an EffectiveDate and an EndDate in the table or create a history table where previous phone / entity associations are kept while only the current associations exist in the EntityPhone table itself.


Phone is an interesting piece of information. Somewhat like Name, can change over time. And it is an attribute of a person or company. I can see doing consistent validation. But in the possible scenario of shared numbers, i.e. company switchboard, fax number, etc., a whole different can of worms is opened up maintaining. Consider when a person, who you have associated the main company phone number to, gets a direct line.
Makes a person think, although you quickly spiral down into a lot of work, and the value would be very questionable.
It does make for a good example of how to normalize data, something which I know Tom is very good at.


And I admit my above code doesn't take into consideration things like extensions or phone number type (work, mobile, home, voip, etc.). But I'm thinking about this from a high level POV with all that sort of stuff to be worked out after the logical model connections are created.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1448836
Posted Thursday, May 2, 2013 10:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 2,857, Visits: 5,826
Rally good first day at sqlbits. Workshop (or pre-cons) day today. Now a q&a with Conor Cummingham and some of The CAT on Azure. Then pub and pub quiz...

Sounds like the some of you are having 'fun' with a poster and normalisation...

Rodders...



Post #1448913
Posted Thursday, May 2, 2013 11:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 17,950, Visits: 15,950
This is sooo true

http://xkcd.com/1205/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1448928
Posted Thursday, May 2, 2013 3:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 13,220, Visits: 12,697
For the first time in 106 years it is snowing in Kansas City in May with anticipated accumulation. How bizarre!!! They are saying we should expect anywhere from 1 - 6". Just yesterday at soccer practice it was over 70 degrees.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448988
Posted Thursday, May 2, 2013 3:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 5,438, Visits: 7,607
SQLRNNR (5/2/2013)
This is sooo true

http://xkcd.com/1205/


LOL, I know. I've actually printed it out and hung it on my cube wall.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1448992
Posted Thursday, May 2, 2013 3:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:47 AM
Points: 7,855, Visits: 9,603
Evil Kraig F (5/2/2013)
SQLRNNR (5/2/2013)
This is sooo true

http://xkcd.com/1205/


LOL, I know. I've actually printed it out and hung it on my cube wall.
Take care not to forget that this is only useful if you are a committed optimist. Every working time is based on the assumption that you save the time specified on the left. So for a realist these figures are not useful - we need to see what is the probability that you will save X time per execution if you spend Y time on optimisation, not how much time we can spend to break even if X time per execution is saved (actually the latter is useful once you have the former, but without the former it is just a waste of space on your wall).


Tom
Post #1448994
Posted Thursday, May 2, 2013 3:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 5,438, Visits: 7,607
L' Eomot Inversé (5/2/2013)
Evil Kraig F (5/2/2013)
SQLRNNR (5/2/2013)
This is sooo true

http://xkcd.com/1205/


LOL, I know. I've actually printed it out and hung it on my cube wall.
Take care not to forget that this is only useful if you are a committed optimist. Every working time is based on the assumption that you save the time specified on the left. So for a realist these figures are not useful - we need to see what is the probability that you will save X time per execution if you spend Y time on optimisation, not how much time we can spend to break even if X time per execution is saved (actually the latter is useful once you have the former, but without the former it is just a waste of space on your wall).


Well, true enough. However, its purpose on my wall is not as a reference, but to deter some overly optimistic folks about just how important their five minute monthly report is.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1449000
« Prev Topic | Next Topic »

Add to briefcase «««3,9573,9583,9593,9603,961»»»

Permissions Expand / Collapse