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 ««123»»

how to fetch records from multiple tables Expand / Collapse
Author
Message
Posted Friday, December 28, 2012 1:45 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 PM
Points: 1,945, Visits: 3,173
Usually I have to identify a person by the role they play in the Universe of Discourse. Most often the role does actually involve a person; a bank account number is universal (part of the IBAN, used by the Fed Reserve, etc) and can belong to a "lawful person", such as corporations, organization, etc.

For a reasonable level of trust, I use the email address + password, like every website on earth. Seems to work well enough for Paypal, Amazon, et al. People do not change them much any more

I happen to like the DUNS for commercial work; it is free and I had to get one to get paid by Dell Computers. Here in Austin, we have radio ads from D&B for them!

If I have to create an identifier (seldom happens actually, if you do your research), then I have rules for designing encoding schemes. I need to play with the Damm check digits when I get my had above water.

In about 5 years I am hoping that Fujitsu's DNA reading chip will be cheap enough to use in a stick of some kind. Go to the doctor, put my finger in one slot and my stick in the other; push the button and it says "Yep, the DNA in the finger is the public key for the exabyte of medical data on this stick" and I can get privacy. If nobody cuts off my fingers

I used to move inmates in a state prison system by "10-cards" and the Henry-Galt codes.




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 #1401049
Posted Friday, December 28, 2012 2:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
No offense Joe but I think you have lost your mind if you actually think that Amazon uses the customer email as part of the primary key. How many supporting tables would that value be needed as a foreign key? We all know that varchar is not a great candidate for performance when the length can be all over the place like an email. Given that the user is allowed to change their email at any time it goes against the notion that a primary key not change. Can you imagine how ridiculous it would be to change your email? There is not a chance on this earth that Amazon uses that as part of a primary key. They would be better off using the person's name instead of email, most people's names never change unlike their email address. If they used email as part of the key they would just about be forced to use an update trigger to update all the other tables in their system that need that value. We can also be pretty sure that they don't have an update trigger on the account table to propagate foreign key values all over the place.

I agree that identity is probably overused but to say that it has no place in the real world is just flat out rubbish. To use your own arguments about turning Cindy Lou Who upside down to verify an identity, the same is true of SSN and email. They are no more a part of the physical entity than some arbitrary number.


_______________________________________________________________

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 #1401059
Posted Friday, December 28, 2012 2:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,139, Visits: 12,762
CELKO (12/28/2012)
Usually I have to identify a person by the role they play in the Universe of Discourse. Most often the role does actually involve a person; a bank account number is universal (part of the IBAN, used by the Fed Reserve, etc) and can belong to a "lawful person", such as corporations, organization, etc.

For a reasonable level of trust, I use the email address + password, like every website on earth. Seems to work well enough for Paypal, Amazon, et al. People do not change them much any more

I am with you on the theoretical aspects of this discussion but I have a hard time wrapping my head around the practical application aspects. In this example how would you model the primary key in the parent-table that stores people from which most people-attribute tables might be related? Assuming email is stored as a CHAR(320) and password is stored as a one-way hash in the database, let's say in a CHAR(100), then are you saying your primary key would be 420 bytes wide? Would you use a variable-length type like VARCHAR for email to save on some of the storage? What would happen if the email did change to something wider than before?

All of that would seem impractical when compared to a 4-byte INT, or in the case of Amazon likely an 8-byte BIGINT would be required, regardless of how it might fly in the face of the theory that a machine's idea of a physical insert attempt is an invalid key to identify a person. Or do you use a checksum or hash algorithm of some sort on the combination of email and password to reduce the size while maintaining uniqueness?

Even in the case of a bank account number you'll have a 34-byte key which could weigh down internal data processing when compared to an 8-byte BIGINT.

Bottom line question: what actual value would you store in the parent table to use in uniquely identifying a person, as well as to relate the parent person table other tables where that person's addresses (0-n) might be stored?

As an aside, a SQL Server identity column's values in a database stored on serverA are portable to serverB. In the case where serverA dies and we restore the database to serverB from backups SQL Server will know where serverA left off in terms of the next number to issue from serverB.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1401070
Posted Friday, December 28, 2012 3:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:19 PM
Points: 2,328, Visits: 3,505
CELKO (12/28/2012)
For a reasonable level of trust, I use the email address + password, like every website on earth. Seems to work well enough for Paypal, Amazon, et al. People do not change them much any more




That CAN'T be serious!! But sadly it is. HOLY IDIOCIES BATMAN! You have NO control over that.

Yahoo and every other free provider AFAIK reclaims email addresses after a certain period of nonuse (say, after the user DIES, rather unavoidable for every user eventually!).

So "JSmith2147" dies, his id is reassigned, and then you email his business info to someone else.

You're one of those consultants that clearly sells only to the pointy-haired manager types who rely solely on perceived reputation and don't have a real clue about the actual implementation details of what you're spouting.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1401085
Posted Friday, December 28, 2012 5:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 PM
Points: 1,945, Visits: 3,173
Surely there's some good SQL programmers out there doing exactly what CELKO says they do not do.


I have been at this for 25+ years now and I disagree. Can you give me a scenario where the physical insertion attempt count can have any meaning in a logical data model? How about the blink rate of the front panel of the disk drive?

Back in the original 16-bit minicomputer days on UNIX in the 1970's, the hardware was slow, people did not understand RDBMS and this was assumed to be a way to speed up access. It kept joins short, etc. All you had to give up was data integrity and portability. But we did not know about those things back then. People thought they would always be on one platform, there were no standards and very little theory. And who would have a Terabyte of data on a small machine??

Now move to the 21-st Century. 64-bit hardware, faster and bigger than all of the mainframes on Earth in 1970, SSD, ANSI/ISO Standards and decades of theory and research.

Confession time: when I learned FORTRAN, we had six letter variable names. I got good at inventing six-letter variable names, so I did not stop doing this when I got better FORTRAN compilers and modern languages. I did not realize it was weird and made maintaining code almost impossible for years.



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 #1401096
Posted Friday, December 28, 2012 5:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:19 PM
Points: 2,328, Visits: 3,505
CELKO (12/28/2012)
Surely there's some good SQL programmers out there doing exactly what CELKO says they do not do.


I have been at this for 25+ years now and I disagree. Can you give me a scenario where the physical insertion attempt count can have any meaning in a logical data model? How about the blink rate of the front panel of the disk drive?

Back in the original 16-bit minicomputer days on UNIX in the 1970's, the hardware was slow, people did not understand RDBMS and this was assumed to be a way to speed up access. It kept joins short, etc. All you had to give up was data integrity and portability. But we did not know about those things back then. People thought they would always be on one platform, there were no standards and very little theory. And who would have a Terabyte of data on a small machine??

Now move to the 21-st Century. 64-bit hardware, faster and bigger than all of the mainframes on Earth in 1970, SSD, ANSI/ISO Standards and decades of theory and research.

Confession time: when I learned FORTRAN, we had six letter variable names. I got good at inventing six-letter variable names, so I did not stop doing this when I got better FORTRAN compilers and modern languages. I did not realize it was weird and made maintaining code almost impossible for years.




So what do you use to key an Order header table if not Order#? ... After all, orders don't have email addresses!


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1401097
Posted Friday, December 28, 2012 9:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
CELKO (12/27/2012)
[quote]but a good SQL programmer never uses IDENTITY for a key


Another fine load of passive aggressive name calling hooie. Stop it or, since you seem incapable, just go away.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401117
Posted Friday, December 28, 2012 9:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
CELKO (12/28/2012)
[quote]Can you give me a scenario where the physical insertion attempt count can have any meaning in a logical data model?


Sure... but first, give us a good example of what you would design a Primary Key to be for a customer table and explain why.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401119
Posted Friday, December 28, 2012 10:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
Sean Lange (12/28/2012)
First of all, SSN have and will be reused. When people die their SSN gets recycled into the available pool.


While it is true that a person can change their SSN, it's a myth that SSNs are recycled. Please see Q20 on the following webpage which is on the official Social Security website.
http://www.socialsecurity.gov/history/hfaq.html

There was one incident many years ago where some secretary had her SS card used as a demo card in wallets and some huge number of people claimed it as their own, but Social Security numbers have not been reused to date.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401121
Posted Friday, December 28, 2012 10:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
CELKO (12/28/2012)
{ISO-11179 rules for data element names} Which can be downloaded where?


http://metadata-standards.org/Document-library/Documents-by-number/WG2-N1551-N1600/WG2N1580_WD_11179-5_Ed3.pdf

But you will sorry. Reading standards is like reading the law; it took me two years on ANSI X3H2 to get comfortable with the language. I would start with some of my stairway stuff on the basics:


And yet you continue to bash people about learning a standard that you've just said they'd be sorry for reading. Stop pushing that garbage. Teach them the right way to do it instead. If you believe that your "stairway" article is more effective, then start posting that instead of bothering people with useless rhetoric about a standard that even took you a long time to understand.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401123
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse