September 17, 2012 at 11:17 am
CELKO (9/17/2012)
Both concepts also assume that all data will be for current-era entities. DUNS numbers only go back to 1963. E-mail for common use, even in tech-heavy countries like the US or Japan, only goes back about 10 years, maybe 15. Human activity that might need to be tracked in a database goes back considerably further than that.
Agreed. I had those problems with a brief consulting job at the Getty Museum. This is the troll's “Yes, but what if..” game that continues to raise objections to anything proposed. The usual techniques are ask for something very specialized (“fingerprints? What about people without hands?”) or overly generalized (“how do you put a RFID on every insect on earth? RFID will never work!”) or absurdity (“What about Martians?”) or never proposing an alternative.
Cool. I suggest you knock of ad hominem, and your response is to call me a troll! I seriously love the logic in your universe!
I'm not talking about an extreme "what if" in order to provoke some "reductio ad absurdem" argument. I'm challenging what I consider a seriously flawed idea, that DUNS numbers can be used as a solid PK in a database of businesses, based on actual, real-life experience in dealing with small businesses. Decades of it! Do I have an alternative? Yes, a locally generated value that's universal to all uses in all databases used by this business. Does it work? Yep. If I had to depend on DUNS numbers, would that work? Nope. Multi-billion-per-year industry working with present-day businesses, and DUNS numbers are not a viable option for us. For the primary reason cited, that not all clients have them or need them.
But I guess that makes me an idiotic troll to you.
I seldom work with companies that went out of business before 1963; do you? Wikipedia says that as of 2011, more than 2.2 billion people – nearly a third of Earth's population — have email. I seldom work with a target audience larger than that, do you?
I have a marketing database that is used by multiple departments here. Of the individuals in it, 2.7-million records do not have e-mail addresses, and 1.9-million do. They all have valuable information for this business. Are you suggesting we should delete the majority of that hard-earned data, and reject the 2/3rds of the population that don't have e-mails, and all the billions of dollars per year in revenue that those people represent, just because they don't have or decline to provide, an e-mail address?
The point is, a natural primary key is something inherent to the entity being described.
Fine. I like things I can measure when I physically have an entity. But these are usually non-key attributes, like weight, color, etc. Keys are a bit different.
For example, if you want to have a table of colors, your PK could consist of a few physical values that describe the way it reflects/refracts/absorbs light.
A color is a non-key attribute; it is a measurement. And there are a ton of color scales. If you are in the printing industry, you use Pantone; computer people use RGB; other industries use teh Land color number; etc.
Yes, there are all kinds of standards for describing colors, none of which are actually uniquely quantifiable. Then there's the standard I suggested, which is based on physical properties of the electromagnetic radiation involved. That one works until the laws of physics change and the basic forces of which the universe is built start working in ways they never have before. I think the day that electromagnetic radiation no longer uses photons to transmit energy via measurable wavelengths at c in a vacuum, my concerns will be different than whether or not I need to modify the PK in a table.
The advantages to DUNS numbers that you cite, apply equally to passport numbers. They can be verified by government offices, they're internationally recognized, etc. But I bet you wouldn't consider them a good idea for being the primary means of identifying a person.
LOL! I carry my passport with me at all times, have a birth certificate card in my wallet, Social Security & Medicaid cards, a Texas Driver License and one bank card.
Awesome! Not sure what that's got to do with anything I said, but "awesome!" Would you use passport numbers as a PK for a relation describing something other than travel and passports? That was the question.
The primary objection is that not every business has one (DUNS), nor does every business need one. What you've done is found a surrogate key that you like better than IDs, and has a few practical advantages (and a few serious disadvantages), you've gotten excited about it, and you've become an advocate of it. It's still a surrogate key. It's still an arbitrarily assigned number. It just feels more exciting to you, that's all.
Doesn't anyone read Dr. Codd any more? DUNS is not a surrogate key by definition. You might call the DUNS an artificial key that happens to be global. And yes, I do get excited about industry standards I can use any place on Earth. I think that is much better than the count of physical insertion attempts to one table, in one database, on one machine in one particular SQL product. I see this as more than a “personal preference” ; I fundamental differences.
Yep. Read it. The problem is that you can't use DUNS in many, many real-world applications. Or you have to enter false data into your database in order to compensate for missing data. I guess it's a new version of NULL. Started as NULL = unknown value, expanded to NULL = unknown value or no value, and now it's Fake Data = unknown value or no value. Why not just let the column be NULL and make it a non-primary atribute? Would accomplish the same thing, with the same mechanics, without having to falsify information in an important column.
Straight up from Codd and normal forms, the whole row has to be dependent on the key, the whole key, and nothing but the key (I'm sure you know the rest of that tired old line), and you're advocating falsifying the key in order to allow data into the database that obviously cannot depend on the key.
So, per Codd, Date, et al, using DUNS numbers means your data isn't even in 3NF, by definition. Seems to be an odd way to design a database, to me.
And you're right that DUNS and VIN are comparable. Highly similar. They have similar drawbacks: DUNS goes back to 1953, VIN to 1954, businesses/vehicles outside that time-dimension will break your data if you need to accommodate them; ..
The troll argument again! Go to extremes!
The Joe again! When you can't say anything intelligent or rational, accuse the other guy of being a troll!
they're arbitrary in that they don't actually describe the object being identified;
Keys have to identify the entity, not describe it. Big difference. Does “the City of New York” (the legal name) describe it? No, it identifies the entity. In fact, using the full legal name has implications about how I am using the data.
Actually, "New York" does describe the legal entity. If your recording data about something else, you should use an appropriate tag/label, like "metro New York area". Should probably also have a time-dimension on something like that, since it can change. If you mean a geographic region, I think lat-long and spatial descriptions would be better as a primary identifier (PK).
And, no, I probably wouldn't use VIN as a primary key for a cars database. Last Saturday (2 days ago), I was at a classic cars show. There were a significant number of vehicles there that predated the VIN standard. I see that show every month (I volunteer at a booth), so I'm more than usually aware of the fact that there are tens of thousands of vehicles in Florida alone that can't be put in your database. You'd have to put NULL in the primary key for them, based on your design = broken design.
So just how do these vehicles get registered in Florida? Get insurance? In some states they are regarded as a special legal classification and they are kept in another database. Mixed serial numbers are a real problem. I worked on the auto tag system for Georgia decades ago
I don't know how they get insurance, et al, in this case. I just know they are cars, they get driven (perhaps just to and from shows for all I know), and that they don't have VINs in some cases. They even have different rules than usual on license plates and registration. Again, I'm not sure how that responds to my statement in a sequitur fashion. There are cars that are legal, but don't have VINs, thus I consider the VIN a weak PK for a collection of data about cars.
And you didn't respond to the basic assertion about e-mail, which is its horrible weakness as a primary key for human beings. I still maintain that using it that way, in anything but a database of e-mail addresses (would be useful for a spam shop), is just incredibly ignorant.
But then, why does it work in e-commerce? Got a better one? I seem to be using other credentials (MS, Google, LinkIn, et al) more and more.
Yep. That's my exact point. E-mail is a weak identifier. Multiple per person, multiple people per e-mail address, over half the population don't have them. And so on.
I also maintain that e-mail doesn't work well in e-commerce. I've built and maintained e-commerce databases, and e-mail as the PK has been a weak point in the ones that used it. Caused more headaches than it solved. Account numbers worked better. (Imagine that! Advocating a concept that has literally centuries of proof-of-concept behind it!)
Does that mean don't store e-mail addresses in those databases? Absolutely not! Heck, in a table of e-mail addresses, it can even make a good primary key! But don't use it as the primary identifier for a person. That just ends up breaking things. Either the database, or the business, or both.
- 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 17, 2012 at 11:27 am
Eugene Elutin (9/17/2012)
And you didn't respond to the basic assertion about e-mail, which is its horrible weakness as a primary key for human beings. I still maintain that using it that way, in anything but a database of e-mail addresses (would be useful for a spam shop), is just incredibly ignorant.
But then, why does it work in e-commerce? Got a better one? I seem to be using other credentials (MS, Google, LinkIn, et al) more and more.
You are lying or trolling here!
Not MS, not Google not LinkId and not, your first example, Amazon - no one except may be you and few very inexperienced RDBMS designers/developers would try to treat EMAIL address as Natural Key suitable for PK purpose.
As I have pointed at least couple times here: You can go onto Amazon and change your login Email easily - it will not create new account for you and all your other details eg. order history will be intact.
Actually, it is possible that MS uses e-mail as a PK for various services. LiveID for example. My hotmail account is tied to my e-mail address (of course), which also gives access to SkyDrive, Bing Points, their various Live services (blog, social, etc.), and so on. It is highly likely that the hotmail address is the PK for those services. Would make sense as an account identifier for the way they are using it.
But that's a specialized case of the e-mail address is the service-identifier.
I can't prove they use e-mail for this, but it's quite likely that they do. Would be reasonable in this use.
Wouldn't surprise me if the various GMail related services work that way, too. Use the e-mail address as the natural key and tie everything to that.
But those are both limited cases. And neither one of them can be expected to uniquely identify a person. They identify a logon, which is an entirely different class of object. After all, my wife can log in to my e-mail, SkyDrive, etc., but she's not the same person as me. (Or I'm seriously more schizophrenic than I think I am. ... than we think we are ... whatever!)
The point is that specific cases shouldn't be generalized to universal rules. Joe advocates DUNS as a universal natural key for businesses, and e-mail as a universal natural key for people. Or did in an early post in this thread. Case-specific applicability for that? Sure. General or universal? No way!
- 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 17, 2012 at 2:56 pm
CELKO (9/17/2012)
Think of a parking garage (disk) and parking slot numbers (identity, rowid, or other physical locator). The slot number does not appear on your insurance from, at the DMV, the dealership ,etc. It locates your car (today), but it does not identify the vehicle. That is what the VIN does.
Depending on the time of day, that sequential insertion number will identify another vehicle or be empty. When I park in another garage, I get another non-deterministic sequential insertion number. But the VIN is always the same.
But many "classic" cars do not have a VIN. And people do own, and drive, classic cars.
And how many people know their VIN numbers? It's not something someone can easily recall to look up data.
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 17, 2012 at 2:58 pm
CELKO (9/17/2012)
2 points here. First, What does an IDENTITY value do other than uniquely identify an entity?
Think of a parking garage (disk) and parking slot numbers (identity, rowid, or other physical locator). The slot number does not appear on your insurance from, at the DMV, the dealership ,etc. It locates your car (today), but it does not identify the vehicle. That is what the VIN does.
Depending on the time of day, that sequential insertion number will identify another vehicle or be empty. When I park in another garage, I get another non-deterministic sequential insertion number. But the VIN is always the same.
Second, anyone who uses a string of "the City of New York" as a PK would be foolish [/quote ]
No, he would be in a legal environment where the other choices are synonyms. And so would “NYC” and the Standard Metro Statistical Report Area number, etc
If your PK is a string value like that, you have to take into account context, capitalization, and variations in the spelling/grammar... most of which are based on geographic and environmental conditions outside the scope of your database.
Yep! Ever work with documents? Forget about errors, weak and strong synonyms are a bitch. This is why we like to design an encoding scheme with solid validation rules for things that we put in a database. That is why SQL is all syntax and not semantics.
Database is hard! That's why we make the big bucks.
So the VIN is a candidate key. Depending on the database it could be a primary key or an alternate key. It really comes down to how the database architects decide to build the physical model from the logical model. It is at this point other factors may come into play in deciding what will be used for the PK.
September 17, 2012 at 2:58 pm
Do yahoo and other email provides allow expired and reclaimed email addresses to be re-cycled?
If any of them did recycle one, and I got the same email address as a prior client and used that to access their data, could the company that used email as the "key" to the data be held liable for disclosing it to someone who wasn't the rightful data owner?
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 17, 2012 at 3:10 pm
ScottPletcher (9/17/2012)
Do yahoo and other email provides allow expired and reclaimed email addresses to be re-cycled?If any of them did recycle one, and I got the same email address as a prior client and used that to access their data, could the company that used email as the "key" to the data be held liable for disclosing it to someone who wasn't the rightful data owner?
Interesting idea. This is even more realistic when you consider how many websites out there still have a "send me my password" link. Somebody can't setup an online account because that email is already in use. Another one of things that provides entirely too much information is that message. So even a totally innocent person thinks, "I must have already setup an account here". Click the link and is suddenly presented with the previous email owner's data. Really scary if this contains stored credit card data. Of course the above scenario is possible even if the email is not the PK.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2012 at 3:58 pm
GSquared (9/17/2012)
Eugene Elutin (9/17/2012)
And you didn't respond to the basic assertion about e-mail, which is its horrible weakness as a primary key for human beings. I still maintain that using it that way, in anything but a database of e-mail addresses (would be useful for a spam shop), is just incredibly ignorant.
But then, why does it work in e-commerce? Got a better one? I seem to be using other credentials (MS, Google, LinkIn, et al) more and more.
You are lying or trolling here!
Not MS, not Google not LinkId and not, your first example, Amazon - no one except may be you and few very inexperienced RDBMS designers/developers would try to treat EMAIL address as Natural Key suitable for PK purpose.
As I have pointed at least couple times here: You can go onto Amazon and change your login Email easily - it will not create new account for you and all your other details eg. order history will be intact.
Actually, it is possible that MS uses e-mail as a PK for various services. LiveID for example. My hotmail account is tied to my e-mail address (of course), which also gives access to SkyDrive, Bing Points, their various Live services (blog, social, etc.), and so on. It is highly likely that the hotmail address is the PK for those services. Would make sense as an account identifier for the way they are using it.
But that's a specialized case of the e-mail address is the service-identifier.
I can't prove they use e-mail for this, but it's quite likely that they do. Would be reasonable in this use.
...
It is possible, but they don't. Yes, they maintain email account uniqueness but they don't make it PK. The main reason would be - it is highly inconvenient to reference such PK from related entities. Also, keeping it out of PK business would leave them space for re-branding of domain.
Same goes for Gmail, with a caveat, Google may be smart enough not to use RDBMS for this sort of system.
Actually, we need to understand what we are discussing here is only related to Relational Database Systems. And in case of use IDENTITY, MS SQL Server RDBMS.
Enterprise level blogging, email and other "live" & "social" services very likely run on non-relational data models and systems.
September 17, 2012 at 4:35 pm
Eugene Elutin (9/17/2012)
GSquared (9/17/2012)
Eugene Elutin (9/17/2012)
And you didn't respond to the basic assertion about e-mail, which is its horrible weakness as a primary key for human beings. I still maintain that using it that way, in anything but a database of e-mail addresses (would be useful for a spam shop), is just incredibly ignorant.
But then, why does it work in e-commerce? Got a better one? I seem to be using other credentials (MS, Google, LinkIn, et al) more and more.
You are lying or trolling here!
Not MS, not Google not LinkId and not, your first example, Amazon - no one except may be you and few very inexperienced RDBMS designers/developers would try to treat EMAIL address as Natural Key suitable for PK purpose.
As I have pointed at least couple times here: You can go onto Amazon and change your login Email easily - it will not create new account for you and all your other details eg. order history will be intact.
Actually, it is possible that MS uses e-mail as a PK for various services. LiveID for example. My hotmail account is tied to my e-mail address (of course), which also gives access to SkyDrive, Bing Points, their various Live services (blog, social, etc.), and so on. It is highly likely that the hotmail address is the PK for those services. Would make sense as an account identifier for the way they are using it.
But that's a specialized case of the e-mail address is the service-identifier.
I can't prove they use e-mail for this, but it's quite likely that they do. Would be reasonable in this use.
...
It is possible, but they don't. Yes, they maintain email account uniqueness but they don't make it PK. The main reason would be - it is highly inconvenient to reference such PK from related entities. Also, keeping it out of PK business would leave them space for re-branding of domain.
Same goes for Gmail, with a caveat, Google may be smart enough not to use RDBMS for this sort of system.
Actually, we need to understand what we are discussing here is only related to Relational Database Systems. And in case of use IDENTITY, MS SQL Server RDBMS.
Enterprise level blogging, email and other "live" & "social" services very likely run on non-relational data models and systems.
Oracle has sequences, which can be -- and very often are -- used for the same function as IDENTITY.
SQL added sequences, which is useful because of the added functionality vs IDENTITY alone.
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 17, 2012 at 5:29 pm
I can give you one other, very nice example for which you can try to come up with nice Natural Key: Order entity/table. Yes, just a simple and very common part of e-commerce database table of Customer Orders.
And that is as silly as asking for a generic, magic “person number”; What are we ordering? How is it marketed? Is there an industry standard for orders? That is often the case with government agencies.
No one asked you for generic and magic "person number" here. The question was about what kind of Natural Key can be used as PK in Person entity of limited personal details such as name and title where Person can be of any age, background and origins.
What are we ordering?
Is it really matter?
Ok, lets have a look one very small and very specialised internet retailer called Amazon.
What you can order there? How is it in a real-life? Have you ever shopped there? I hope you did.
So, you know what that they sell. Yes, they started from books, but now...
You can buy virtually everything there.
I give you example of their Order Number: 202-23123230-2321286.
Could you please help me to find something natural in it as I can only see this combination of digits (formatted) resembles some kind of composite key. They all numeric, regardless of product. 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?
September 18, 2012 at 7:42 am
CELKO (9/17/2012)
2 points here. First, What does an IDENTITY value do other than uniquely identify an entity?Think of a parking garage (disk) and parking slot numbers (identity, rowid, or other physical locator). The slot number does not appear on your insurance from, at the DMV, the dealership ,etc. It locates your car (today), but it does not identify the vehicle. That is what the VIN does.
Depending on the time of day, that sequential insertion number will identify another vehicle or be empty. When I park in another garage, I get another non-deterministic sequential insertion number. But the VIN is always the same.
Second, anyone who uses a string of "the City of New York" as a PK would be foolish [/quote ]
No, he would be in a legal environment where the other choices are synonyms. And so would “NYC” and the Standard Metro Statistical Report Area number, etc
If your PK is a string value like that, you have to take into account context, capitalization, and variations in the spelling/grammar... most of which are based on geographic and environmental conditions outside the scope of your database.
Yep! Ever work with documents? Forget about errors, weak and strong synonyms are a bitch. This is why we like to design an encoding scheme with solid validation rules for things that we put in a database. That is why SQL is all syntax and not semantics.
Database is hard! That's why we make the big bucks.
That's wonderful! It's so brillant. (Typo intentional. See if you can find out why.)
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.
Our job as database architects, is to anticipate the ways the data will actually be used by a business. We aren't here to make businesses comply with database models and standards that we think are exciting. We're here to enhance the profitability of the business by designing and implementing databases that lower costs, increase efficiency, improve management decisions, improve marketing effectiveness, and make record-keeping more cost-effective.
You just cost your employer a big sale. Probably more than one. The usual marketing cost of annoying a customer is estimated as 25 more customers of a similar nature, due to word-of-mouth and other reputation-ripple effects. In other words, your insistence on VINs, because of being excited about a non-binding standard for personal reasons, just violated the whole purpose of hiring you in the first place. Sounds like justifable cause for firing you, doesn't it?
My database, on the other hand, will record VINs as a datum, because I recognize the need to comply with reality being higher than the need to comply with non-universal standards. I'll have other ways in it to identify vintage vehicles, probably based on research into vintage-car culture. At a guess, serial numbers on the engine, frame, and related components, might be the way to go. But I'd confirm that with actual car collectors before I implemented it. The salespeople at the company I work for just got all the sales your guys missed out on. My boss got a big bonus because of that. Profitability is up. Everyone gets raises, including me.
But I forgot, using reality to justify violation of the standards you personally find exciting makes me a troll. So, total end result:
You get fired
I get a raise
You get to feel good about yourself because you can call me a troll, which protects your ego from actual self-examination
I'm called a troll by someone I have very little respect for because I think he's an idiot, which leaves my ego unbruised
Edit to fix quote tags in original.
- 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 18, 2012 at 7:48 am
Eugene Elutin (9/17/2012)
GSquared (9/17/2012)
Eugene Elutin (9/17/2012)
And you didn't respond to the basic assertion about e-mail, which is its horrible weakness as a primary key for human beings. I still maintain that using it that way, in anything but a database of e-mail addresses (would be useful for a spam shop), is just incredibly ignorant.
But then, why does it work in e-commerce? Got a better one? I seem to be using other credentials (MS, Google, LinkIn, et al) more and more.
You are lying or trolling here!
Not MS, not Google not LinkId and not, your first example, Amazon - no one except may be you and few very inexperienced RDBMS designers/developers would try to treat EMAIL address as Natural Key suitable for PK purpose.
As I have pointed at least couple times here: You can go onto Amazon and change your login Email easily - it will not create new account for you and all your other details eg. order history will be intact.
Actually, it is possible that MS uses e-mail as a PK for various services. LiveID for example. My hotmail account is tied to my e-mail address (of course), which also gives access to SkyDrive, Bing Points, their various Live services (blog, social, etc.), and so on. It is highly likely that the hotmail address is the PK for those services. Would make sense as an account identifier for the way they are using it.
But that's a specialized case of the e-mail address is the service-identifier.
I can't prove they use e-mail for this, but it's quite likely that they do. Would be reasonable in this use.
...
It is possible, but they don't. Yes, they maintain email account uniqueness but they don't make it PK. The main reason would be - it is highly inconvenient to reference such PK from related entities. Also, keeping it out of PK business would leave them space for re-branding of domain.
Same goes for Gmail, with a caveat, Google may be smart enough not to use RDBMS for this sort of system.
Actually, we need to understand what we are discussing here is only related to Relational Database Systems. And in case of use IDENTITY, MS SQL Server RDBMS.
Enterprise level blogging, email and other "live" & "social" services very likely run on non-relational data models and systems.
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.
- 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 18, 2012 at 7:52 am
i just tripped over this exact lack of planning by the database architect when switching from one insurance agency to another. From Geico to Progressive: I saved 30% or more on car insurance)
The new company (Progressive) are set up so that a policy can have 4 cars maximum. if you have more cars than that, they have to create a second policy, and copy all your personal information from the first policy to the second.
so anyone with more cars than that, say, folks with larger families, more cars, car collectors, etc. have to jump through extra hoops, and have to pay for their policies as two seperate accoutns/two seperate checks each time they come due.
that also affects the idea of a "multi car discount", since policy #1 for me has four cars, and policy#2 has one car (so no discount!)
also affects how i log into their website, since i need seperate logins for each policy, instead of being unified as a single login with multiple policies.
Lowell
September 18, 2012 at 7:59 am
Lowell (9/18/2012)
i just tripped over this exact lack of planning by the database architect when switching from one insurance agency to another. From Geico to Progressive: I saved 30% or more on car insurance)The new company (Progressive) are set up so that a policy can have 4 cars maximum. if you have more cars than that, they have to create a second policy, and copy all your personal information from the first policy to the second.
so anyone with more cars than that, say, folks with larger families, more cars, car collectors, etc. have to jump through extra hoops, and have to pay for their policies as two seperate accoutns/two seperate checks each time they come due.
that also affects the idea of a "multi car discount", since policy #1 for me has four cars, and policy#2 has one car (so no discount!)
also affects how i log into their website, since i need seperate logins for each policy, instead of being unified as a single login with multiple policies.
Sounds more like a normalization failure than a PK failure. But the point on poor architecture stands either way. Interesting issue, that's for sure.
- 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 18, 2012 at 8:09 am
Lowell (9/18/2012)
i just tripped over this exact lack of planning by the database architect when switching from one insurance agency to another. From Geico to Progressive: I saved 30% or more on car insurance)The new company (Progressive) are set up so that a policy can have 4 cars maximum. if you have more cars than that, they have to create a second policy, and copy all your personal information from the first policy to the second.
so anyone with more cars than that, say, folks with larger families, more cars, car collectors, etc. have to jump through extra hoops, and have to pay for their policies as two seperate accoutns/two seperate checks each time they come due.
that also affects the idea of a "multi car discount", since policy #1 for me has four cars, and policy#2 has one car (so no discount!)
also affects how i log into their website, since i need seperate logins for each policy, instead of being unified as a single login with multiple policies.
Unbelievable a company could be that short-sighted. [Haven't they heard of Jay Leno??]
I know a family with six kids and NINE vehicles (they have one extra one for all-in-one travel). Progressive would be out of the running there.
Flo needs to get on this NOW!
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 18, 2012 at 8:19 am
CELKO (9/17/2012)
2 points here. First, What does an IDENTITY value do other than uniquely identify an entity?
Think of a parking garage (disk) and parking slot numbers (identity, rowid, or other physical locator). The slot number does not appear on your insurance from, at the DMV, the dealership ,etc. It locates your car (today), but it does not identify the vehicle. That is what the VIN does.
Depending on the time of day, that sequential insertion number will identify another vehicle or be empty. When I park in another garage, I get another non-deterministic sequential insertion number. But the VIN is always the same.
...
Database is hard! That's why we make the big bucks.
Think of a parking garage and parking slot numbers?
The vehicle is not an attribute of the parking slot and the parking slot is not an attribute of the vehicle. Both are part of a transaction. Does the slot number (identity) matters? It depends. If you have valet parking or another vehicle retrieving system, you need a way to know where's a certain vehicle instead of searching for a specific brand, model, color, plate and VIN. In other words, the slot number identifies the slot, not the car.
If you want to hear another horror story.
I worked on a database for a system that controls sales promoters for pharmaceutical laboratories (not real example: Bayer). This promoters visit doctors to promote the medicines and to get doctor's opinion. They should capture all the doctor's information including the professional license. When I had to support the database, I found out that many of them didn't have a license or a valid license, I even found the same doctor captured twice with different license. The problem was that most of the promoters didn't ask for the license and just entered random numbers. Even with a correct validation on to avoid duplicates and correct length, format, etc, people will pass through them. The only way I had to truly identify a row was by the "magical" identity number (doctorId).
Maybe you (and Chuck Norris) could have managed things different, but most mortals wouldn't.
Viewing 15 posts - 61 through 75 (of 125 total)
You must be logged in to reply to this topic. Login to reply