September 18, 2012 at 8:26 am
GSquared (9/18/2012)
Last I heard, Google uses a customized version of MySQL for their primary data storage. They rebuilt the engine and modified the ACIDity of transactions towards "eventually consistent", which makes it technically a NoSQL implementation. But the data storage is still relational.Do you have proof that e-mail isn't the PK? Or are you just surmising it "because they wouldn't be that dumb"? Surmise = assumption. Not safe to do, and definitely not valid for calling someone a liar/troll based on it.
I disagree with Joe just as much as you do on the reasoning behind his decisions. But I wouldn't call him a liar or a troll because "MS and Google aren't that dumb" about e-mails, unless I could back that statement up with at least a blog entry by one of their devs. An actual white-paper would be better. I've seen otherwise smart companies do far too many stupid things to ever use that as proof of anything.
Ahem, white paper, unfortunately it doesn't cover mail, but it does cover Maps, Search and Analytics.
It's an interesting read. I don't think it had roots in MySQL, it certainly doesn't share any recognisable storage, logic or retrieval characteristics of an RDBMS that you'd recognise (it's ATOMic, but not fully relational). Jeez, Google don't even seem to use network equipment[/url] that you'd recognise!
They clearly use natural keys in places (URLs for search) but also use arbitrary strings elsewhere.
Edit: More info here - GMail also uses BigTable
September 18, 2012 at 10:56 am
HowardW (9/18/2012)
GSquared (9/18/2012)
Last I heard, Google uses a customized version of MySQL for their primary data storage. They rebuilt the engine and modified the ACIDity of transactions towards "eventually consistent", which makes it technically a NoSQL implementation. But the data storage is still relational.Do you have proof that e-mail isn't the PK? Or are you just surmising it "because they wouldn't be that dumb"? Surmise = assumption. Not safe to do, and definitely not valid for calling someone a liar/troll based on it.
I disagree with Joe just as much as you do on the reasoning behind his decisions. But I wouldn't call him a liar or a troll because "MS and Google aren't that dumb" about e-mails, unless I could back that statement up with at least a blog entry by one of their devs. An actual white-paper would be better. I've seen otherwise smart companies do far too many stupid things to ever use that as proof of anything.
Ahem, white paper, unfortunately it doesn't cover mail, but it does cover Maps, Search and Analytics.
It's an interesting read. I don't think it had roots in MySQL, it certainly doesn't share any recognisable storage, logic or retrieval characteristics of an RDBMS that you'd recognise (it's ATOMic, but not fully relational). Jeez, Google don't even seem to use network equipment[/url] that you'd recognise!
They clearly use natural keys in places (URLs for search) but also use arbitrary strings elsewhere.
Edit: More info here - GMail also uses BigTable
As I've said already, for blogs, in-live and other social services eg. web-search the use of RDBMS is highly unlikely. There are other database models are much more suitable for these sort of business. We started to discuss Amazon and switched to Google likes. As stated in provided white-paper, Google is using non-RDBMS Bigtable.
Bigtable does not support a full relational data model; instead, it provides clients with a simple data model that supports dynamic control over data layout and format, and allows clients to reason about the locality properties of the data represented in the underlying storage...
A Bigtable is a sparse, distributed, persistent multi-
dimensional sorted map. The map is indexed by a row
key, column key, and a timestamp;
I hardly see how RDBMS concepts, including Natural Keys, are relevant here. Yes they use meaningful strings as a Keys to the data. But, it has nothing to do with RDBMS, as they whouldn't have other entities related by this Key as all data for the Key is in the same place - aka document databases.
I can see it as totally irrelevant to SQL Server forum.
And I stay on what I call J.C. - MS, LinkedIn, Amazon do not use emails as PK in their RDBMS's
I have provided some evidence for Amazon, which allows to change the login email as many time as you want without loosing any related details eg historical transactions. Would you really think that it's still can be PK as advised by J.C., so they just update it whenever user changes it?
September 18, 2012 at 7:54 pm
CELKO (9/18/2012)
I do order some items from different countries including non-US, but order numbers looks the same to me. Have Amazon registered international order numbering standard?
I don't know, but I will guess that it is part of a large mainframe accounting package.
However, your payment will have an IBAN (International Bank Account Number) in it. See ISO 13616 or get a quick read at http://en.wikipedia.org/wiki/International_Bank_Account_Number.
If you get an invoice in Italy, they have to be in sequential order by law.
The key thing in your answer is "I don't know"!
Last time I "checked" Amazon was using Oracle for their core system. I hope you did heard about this RDBMS? Yes, it's not SQL Server, but much closer to it in its nature than non-RDBMS's used by blogging and other "social" web services.
Thank you very much, I do know very well what IBAN is. When you pay for a purchase using a credit or a debit card, IBAN is not used, payment is done via credit card payment system (VISA, Master, Maestro, etc.). But I cannot really think of situation where it would be used in a payment at all?
IBAN of which account required? My one? What is the point? It can only be used to transfer money "to" account not "from" account. Amazon account IBAN? If Amazon banking with US bank, it's highly unlikely that it has one...
I have advice for you: Please read wiki yourself!
You will find that even so IBAN is International Bank Account Number, it is still not implemented by every country. Funny enough it's has very little use in your own country - US! Canada also not using it as far as I'm aware. Not used in Australia and New Zealand, also most of former USSR parts not using it either :w00t:.
And I'm very happy for Italy! They are not scared of numeric sequences... :hehe:
September 18, 2012 at 8:40 pm
I'm curious why they require them to be sequential, and is that for each customer or just in general? Mr. Celko didn't really elaborate on that particular area.
September 19, 2012 at 1:23 am
I'm firmly in the horses for courses camp here, I can see when you can get away with natural/business key as an Identity and also appreciate that at times you have to create a surrogate key that may or may not be exposed to the external world.
ISO currency and Country codes are a prime example of an when the natural key (ISO Code) can be used as an Identity column and Primary Key.
In the Example of a human being, most people are granted client numbers when they register with an organisation, this is generally the next in a sequence of numbers, and so it becomes visible to the external world, and yet is still an Identity under the bonnet.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 19, 2012 at 5:09 am
...
ISO currency and Country codes are a prime example of an when the natural key (ISO Code) can be used as an Identity column and Primary Key.
...
You wouldn't call it Identity column as it is not an IDENTITY in SQL Server language. I take it as a typo.
However, even ISO currency and Country codes may not be safe to use as PK. It's all really depends on the business you are in!
There are few countries in the world which are not completely recognised. Some of them have temporary codes assigned, but some have not. So, you will need to come up with your own "fake" ones if your business need to deal with these countries. In case of using your own Surrogate/Artificial key for PK purposes, you don't need to worry about this kind of staff which you have no control over. Of course your Country table will still have ISO Country Code as an Attribute, which will allow NULLS and on which you may want to create unique index, so when value is provided it's is unique...
So, again: It all depends...
September 19, 2012 at 6:23 am
CELKO (9/18/2012)
And now you've got a salesperson and his manager on the phone to you. They need to insure an expensive (high premiums, low-risk) collectible car, which doesn't have a VIN. They have the potential customer in the office. He's willing to pay an outrageous comprehensive premium, to cover the car. The manager and salesperson are seeing big numbers on their next paycheck. The manager has the authority to fire you because you were an idiot and made VIN a non-nullable column (PK) in the database.
It gets insured as a collectible, not an automobile. "So how many miles a year do you put on your Stutz Bearcat? Is this mostly urban driving? Will your teenage kids be driving it? Do you have child safety seats and other features that are required by law before we can insure your vehicle? Inspection sticker?"
Building a database that mixes inappropriate things together is bad idea. Ever heard the phrase "Automobiles, Squids and Lady Gaga"? Sacrificing the performance to handle 0.0001% of the situations is a bad idea.
One of the best tools we have are industry standards. They allow us to model data without having to do research, without having to maintain and update them in-house. And we can exchange data with the rest of the world, other people can read it and we have standard ways to compute with it.
Joe, did you hear that boom? It was everything I wrote going over your head at about Mach 3, around 85,000 feet up. So, never mind.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2012 at 7:21 am
CELKO (9/18/2012)
And now you've got a salesperson and his manager on the phone to you. They need to insure an expensive (high premiums, low-risk) collectible car, which doesn't have a VIN. They have the potential customer in the office. He's willing to pay an outrageous comprehensive premium, to cover the car. The manager and salesperson are seeing big numbers on their next paycheck. The manager has the authority to fire you because you were an idiot and made VIN a non-nullable column (PK) in the database.
It gets insured as a collectible, not an automobile. "So how many miles a year do you put on your Stutz Bearcat? Is this mostly urban driving? Will your teenage kids be driving it? Do you have child safety seats and other features that are required by law before we can insure your vehicle? Inspection sticker?"
Building a database that mixes inappropriate things together is bad idea. Ever heard the phrase "Automobiles, Squids and Lady Gaga"? Sacrificing the performance to handle 0.0001% of the situations is a bad idea.
One of the best tools we have are industry standards. They allow us to model data without having to do research, without having to maintain and update them in-house. And we can exchange data with the rest of the world, other people can read it and we have standard ways to compute with it.
Actually Mr. Celko, most antique vehicles are covered by applicable law... or they wouldn't be fitted with a legal license plate. For example, in most states (don't know exactly how many), an Inspection is not required if the vehicle is 35 years old or older. So, as of this writing, year model 1977 no longer requires an inspection by state law. Are you going to transfer all records for cars that old from one DB to another or from one table to another just because it goes from 2012 to 2013?
And to answer your earlier point about Identity uniquely identifying a car parking space. You are correct, a parking slot number does uniquely identify a parking slot... not a car. However, That's not the argument (whack yourself with the teaching stick for not listening please). The argument is that if Company X used a sequential numbering scheme for all of its cars (aka a Serial Number) the serial number DOES uniquely identify the vehicle. Parking Slot IDs identify Parking Slots. Sequential IDs on a car identify the car. (AKA, what manufacturers used prior to the 1950's and 1970's when VIN's were required and then formalized in ISO).
September 19, 2012 at 8:23 am
Eugene Elutin (9/19/2012)
...
ISO currency and Country codes are a prime example of an when the natural key (ISO Code) can be used as an Identity column and Primary Key.
...
You wouldn't call it Identity column as it is not an IDENTITY in SQL Server language. I take it as a typo.
However, even ISO currency and Country codes may not be safe to use as PK. It's all really depends on the business you are in!
There are few countries in the world which are not completely recognised. Some of them have temporary codes assigned, but some have not. So, you will need to come up with your own "fake" ones if your business need to deal with these countries. In case of using your own Surrogate/Artificial key for PK purposes, you don't need to worry about this kind of staff which you have no control over. Of course your Country table will still have ISO Country Code as an Attribute, which will allow NULLS and on which you may want to create unique index, so when value is provided it's is unique...
So, again: It all depends...
and thats exactly what I stated, its very much horses for courses, and I can see the argument for using ISO codes as an Identity column as they are unique and thus candidates.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 19, 2012 at 8:26 am
Eugene Elutin (9/19/2012)
...
ISO currency and Country codes are a prime example of an when the natural key (ISO Code) can be used as an Identity column and Primary Key.
...
You wouldn't call it Identity column as it is not an IDENTITY in SQL Server language. I take it as a typo.
I took "Identity" in the original sentence as equivalent to "identifying", i.e. a key value.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2012 at 8:47 am
ScottPletcher (9/19/2012)
Eugene Elutin (9/19/2012)
...
ISO currency and Country codes are a prime example of an when the natural key (ISO Code) can be used as an Identity column and Primary Key.
...
You wouldn't call it Identity column as it is not an IDENTITY in SQL Server language. I take it as a typo.
I took "Identity" in the original sentence as equivalent to "identifying", i.e. a key value.
So, the Natural Keys can be called "Identity" as both has a purpose of "identifying", am I right? If yes, then "Identity" can be used vice versa.
I'm absolutely happy with it!
But. In context of this discussion the IDENTITY is a sequentially increasing abstract numeric value as it's defined in SQL Server concepts.
I just afraid that we scare Joe even more now...
September 19, 2012 at 9:57 am
Eugene Elutin (9/19/2012)
ScottPletcher (9/19/2012)
Eugene Elutin (9/19/2012)
...
ISO currency and Country codes are a prime example of an when the natural key (ISO Code) can be used as an Identity column and Primary Key.
...
You wouldn't call it Identity column as it is not an IDENTITY in SQL Server language. I take it as a typo.
I took "Identity" in the original sentence as equivalent to "identifying", i.e. a key value.
So, the Natural Keys can be called "Identity" as both has a purpose of "identifying", am I right? If yes, then "Identity" can be used vice versa.
I'm absolutely happy with it!
But. In context of this discussion the IDENTITY is a sequentially increasing abstract numeric value as it's defined in SQL Server concepts.
I just afraid that we scare Joe even more now...
I don't think you'll scare him. Maybe offend, but not scare.
He comes across as one of those borderline sociopathy cases that have very thin skin, but assume everyone else has rhino-hide.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 19, 2012 at 10:31 am
Eugene Elutin (9/19/2012)
ScottPletcher (9/19/2012)
Eugene Elutin (9/19/2012)
...
ISO currency and Country codes are a prime example of an when the natural key (ISO Code) can be used as an Identity column and Primary Key.
...
You wouldn't call it Identity column as it is not an IDENTITY in SQL Server language. I take it as a typo.
I took "Identity" in the original sentence as equivalent to "identifying", i.e. a key value.
So, the Natural Keys can be called "Identity" as both has a purpose of "identifying", am I right? If yes, then "Identity" can be used vice versa.
I'm absolutely happy with it!
But. In context of this discussion the IDENTITY is a sequentially increasing abstract numeric value as it's defined in SQL Server concepts.
I just afraid that we scare Joe even more now...
Actually, I'm thinking now you may be right, that the person thought it could be an "identity" in SQL, in which case you were quite right to clarify that the ISO country codes are not, and could not be, a SQL IDENTITY.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2012 at 11:40 am
GSquared (9/19/2012)
Eugene Elutin (9/19/2012)
...I just afraid that we scare Joe even more now...
I don't think you'll scare him. Maybe offend, but not scare.
...
I don't think he is "offendable", he claimed to be abrasive and kind of offend-resistant before :hehe:
I would call his personality slippery, however I'm not sure it 100% corresponds to the word I would use from my mother tongue...
Actually, I don't really care how he designs and develops SQL Server databases for his clients (it's may be even good, that he creates something which will require more fix-work for others in the current economical climate :-)). But I am really unhappy with the fact that he does very often present his crap design ideas on this forum as a state of facts. He constantly uses "We" when he starts his rants. While many of SQL Experts would have very different view on a subjects. But having such recognisable name, may really play very bad with SQL beginners as they may take his delirium for rules to follow.
He advices against use of many SQL Server features which make this product outstanding from others and help to build great enterprise DB solutions in a cost-effective way.
His mix of theoretical aspects of Relational Databases with concrete RDBMS implementations sometimes is very poor.
I don't say that one shouldn't know what is Relational Data Model is about, but I would like to insist, that following the theory is good as long it does work for you in a place & time you are currently is.
"Theory without practice is idle, practice without theory is blind" Qigong concept explains very well how it is.
😉
September 20, 2012 at 11:46 am
Offtopic:
Just noticed for myself, that there is a struggle between Eugene Elutin and CELKO, not only in this topic. So my question is what is the point? Yes CELKO is soetimes too strict, and his anwesrs are based on ideal world situation, but why Eugene is so care about it? Not going to abuse someone! Thanks!
Viewing 15 posts - 76 through 90 (of 125 total)
You must be logged in to reply to this topic. Login to reply