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


how to fetch records from multiple tables


how to fetch records from multiple tables

Author
Message
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59908 Visits: 17947
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 Modens 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)
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37598 Visits: 14411
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
ScottPletcher
ScottPletcher
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18822 Visits: 7402
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


Laugh Laugh Laugh

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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
ScottPletcher
ScottPletcher
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18822 Visits: 7402
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. Whistling




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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206333 Visits: 41955
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206333 Visits: 41955
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206333 Visits: 41955
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206333 Visits: 41955
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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