May 1, 2013 at 6:28 am
jasona.work (5/1/2013)
Brandie Tarvin (5/1/2013)
SQLRNNR (4/30/2013)
ROBODBAApparently I lack the internet savvy.
HUH?
RoboCop == ROBODBA
😀
"normalize your tables.
Or there will be trouble"
😎
And here I was trying to parse an acronym. :blush:
May 1, 2013 at 7:34 am
Jeff Moden (4/30/2013)
L' Eomot Inversé (4/30/2013)
Anyway, I'm at a loss to think of a good role to assign to DBAs in the worlds of science fiction and fantasy. Of course that is possibly because I haven't read sufficient Lovecraft.Heh... wouldn't it be fun to write a story about the DBAs who first managed the Matrix or SkyNet and how, like many other jobs, were ultimately replaced by machines and the code that drives them? Maybe even have the intelligence and skill of a DBA transferred to a machine (Robocop style).
I think the result might be closer to Marvin the Paranoid Android (from the Hitchhiker's Guide).
May 1, 2013 at 11:13 am
ChrisM@Work suggested that I should revisit the complex many-to-many relationship thread, so I've done so. I found it quite difficult to avoid calling the OP an idiot, since several people have told him the obvious simple solution to his problem but he has refused to recognise it, claiming that we should assume it impossible to devise a particular abstraction although he himself is referring to exactly the required abstraction and even to one of its attributes in just about every post (Chris, that's why I didn't point out the lunacy of a reference whose target table was determined by a different column until now - I couldn't find a gentle enough form of words). I've done my best to explain why the right solution works and why he shouldn't object to it without calling him silly, but I'm not sure. Maybe someone can take a look here and if I've been too harsh post some sugar to alleviate the effect.
Tom
May 1, 2013 at 11:26 am
Ah, the myth of de-identified data...http://www.fiercehealthit.com/story/anonymous-research-patients-easily-re-identified-harvard-researchers-find/2013-04-29
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 1, 2013 at 11:28 am
L' Eomot Inversé (5/1/2013)
ChrisM@Work suggested that I should revisit the complex many-to-many relationship thread, so I've done so. I found it quite difficult to avoid calling the OP an idiot, since several people have told him the obvious simple solution to his problem but he has refused to recognise it, claiming that we should assume it impossible to devise a particular abstraction although he himself is referring to exactly the required abstraction and even to one of its attributes in just about every post (Chris, that's why I didn't point out the lunacy of a reference whose target table was determined by a different column until now - I couldn't find a gentle enough form of words). I've done my best to explain why the right solution works and why he shouldn't object to it without calling him silly, but I'm not sure. Maybe someone can take a look here and if I've been too harsh post some sugar to alleviate the effect.
I thought you showed remarkable restraint, personally.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 1, 2013 at 11:43 am
jcrawf02 (5/1/2013)
Ah, the myth of de-identified data...http://www.fiercehealthit.com/story/anonymous-research-patients-easily-re-identified-harvard-researchers-find/2013-04-29
I saw an article on that study a few weeks ago. Didn't think to post about, but I find it very telling.
May 1, 2013 at 12:30 pm
I just started reading through the topic, it almost sounds like two things to me:
1. A moving target. Every time a reasonable answer is proposed, "that won't work for the purposes of this application..."
2. He's trying to take the phone numbers to a ridiculously high level of normalization that isn't needed for this problem.
Jason
May 1, 2013 at 1:05 pm
jasona.work (5/1/2013)
I just started reading through the topic, it almost sounds like two things to me:1. A moving target. Every time a reasonable answer is proposed, "that won't work for the purposes of this application..."
2. He's trying to take the phone numbers to a ridiculously high level of normalization that isn't needed for this problem.
Jason
About the moving target I agree completely.
But if you think that what he is trying to do involves a ridiculously hight level of normalization you have a very different view of normalization from anything I've ever come across before. the function of normalisation is to enable the schema to enforce business rules using its structure and its keys. He is actively preventing that, I believe, and trying to build a schema in which rules that would normally be enforced by structure and keys can only be enforced by explicit code (in triggers, probably). That's a ridiculously high lever of denormalisation!
Tom
May 1, 2013 at 1:06 pm
CREATE TABLE TelephoneDigits (
DigitID INT IDENTITY PRIMARY KEY,
Digit CHAR(1) NOT NULL UNIQUE -- todo. Check constraint to limit to 0..9, (, ) and +
);
CREATE TABLE TelephoneNumberDigits (
TelephoneNumberDigits INT IDENTITY PRIMARY KEY,
PhoneNumberID, -- todo foreign key
DigitID INT, -- todo foreign key
Position INT
);
CREATE TABLE TelephoneNumbers (
TelephoneNumber ID INT IDENTITY PRIMARY KEY,
TelephoneNumberTypeID INT, -- todo foreign key
ActiveStatusID INT, --todo foreign key
DefaultStatusID INT -- todo foreign key
);
--..... 3 more tables at least
Silly enough?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 1, 2013 at 4:07 pm
GilaMonster (5/1/2013)
CREATE TABLE TelephoneDigits (DigitID INT IDENTITY PRIMARY KEY,
Digit CHAR(1) NOT NULL UNIQUE -- todo. Check constraint to limit to 0..9, (, ) and +
);
CREATE TABLE TelephoneNumberDigits (
TelephoneNumberDigits INT IDENTITY PRIMARY KEY,
PhoneNumberID, -- todo foreign key
DigitID INT, -- todo foreign key
Position INT
);
CREATE TABLE TelephoneNumbers (
TelephoneNumber ID INT IDENTITY PRIMARY KEY,
TelephoneNumberTypeID INT, -- todo foreign key
ActiveStatusID INT, --todo foreign key
DefaultStatusID INT -- todo foreign key
);
--..... 3 more tables at least
Silly enough?
Almost. To reach the depths achieved in the weird join thread you need to split the TelephoneDigits table into two tables, OddTelephoneDigits and EvenTelephoneDigits with check constraints restricting each to its 6 digits. Then the TelephoneNumberDigits table has to have a DigitType field indicating whether the digit is odd or even, and a single field which contains an ID of a row in either the Odddigit table or the Evendigit table (to ensures you can't use a foreign key to ensure referential integrity), and add a big comment stating that it is clearly impossible to invent an abstraction from odd digits and even digits and have a single table representing both. With that small modification you would achieve the required standard of silliness.
Tom
May 2, 2013 at 5:25 am
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.
May 2, 2013 at 6:24 am
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?
May 2, 2013 at 7:09 am
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.
May 2, 2013 at 8:40 am
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.
May 2, 2013 at 10:42 am
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...
Viewing 15 posts - 39,571 through 39,585 (of 66,738 total)
You must be logged in to reply to this topic. Login to reply