November 22, 2016 at 6:12 pm
RonKyle (11/22/2016)
Use a natural key in OLTP designs when possible.
What is you natural key for "person" records?
_____________
Code for TallyGenerator
November 22, 2016 at 8:32 pm
Sergiy (11/22/2016)
RonKyle (11/22/2016)
Use a natural key in OLTP designs when possible.
What is you natural key for "person" records?
There isn't one. Anything about a person can change. People change their names. WITSEC assigns new SSNs and they don't work in multiple countries. And don't forget that people lie.
I'm all in favor of using identity column surrogate keys. They're narrow, non-volatile, ever-increasing and unique.
November 22, 2016 at 8:35 pm
David.Poole (11/21/2016)
I've reached the conclusion that people don't realise what a well thought out data model gives them and they aren't interested in knowing.They see SQL Server as a dumb data store and regard constraints as an affront limiting their flexibility. They absolutely don't see that an enforced data model benefits data quality or that a normalised data model represents a concept in a way that gives a fine grained understanding of the domain. Neither do they care that yhe FKs, PKs, check constraints give the query optimiser a boost.
This is because they don't understand it, they don't care enough to learn or both.
November 22, 2016 at 9:00 pm
I'm all in favor of using identity column surrogate keys. They're narrow, non-volatile, ever-increasing and unique.
IDENTITY, by itself, is not guaranteed to be unique. Takes a unique constraint for that. Might be a clustered index or just an index marked as unique.
ATBCharles Kincaid
November 23, 2016 at 12:00 am
We can mask the apparent complexity of a DB with views and stored procedures.
The prejudice against joins was always a mystery to me until Phil Factor nailed it with the phrase along the lines of "Most of developer's beliefs about joins (and RDBMS) don't make sense until you realise that they are talking about MySQL".
That is like condemning all cars on the basis of having driven a Citroen 2CV.yy
November 23, 2016 at 1:51 am
Phil Factor (11/19/2016)
The problem comes when the people providing you the information about the data don't themselves understand it, or know about all of it. Then it all gets sticky.
True. Also, not fully understanding the processes they go through and misinforming you
- Damian
November 23, 2016 at 1:58 am
I de-normalise and generate a star schema when building OLAP cubes
- Damian
November 23, 2016 at 5:22 am
Ed Wagner (11/22/2016)
Sergiy (11/22/2016)
RonKyle (11/22/2016)
Use a natural key in OLTP designs when possible.
What is you natural key for "person" records?
There isn't one. Anything about a person can change. People change their names. WITSEC assigns new SSNs and they don't work in multiple countries. And don't forget that people lie.
I'm all in favor of using identity column surrogate keys. They're narrow, non-volatile, ever-increasing and unique.
That's why I'm curious what are those cases when it's possible to use natural keys in OLTP systems.
_____________
Code for TallyGenerator
November 23, 2016 at 5:31 am
Charles Kincaid (11/22/2016)
...Gary I agree with you almost all of the time. I prefer to not be harsh in criticizing folks. Here in the U.S. we have been through a period of harsh language and I'm about finding gentler ways of driving the point home. Cheers.
This sounds like I missed that I was in an argument or I missed an opportunity to be offended. Not surprising as I have skin thicker than a Rhino's and get angry slower that a Sloth living in Colorado. Hope you are having a great day Charles!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 23, 2016 at 5:32 am
Jeff Moden (11/22/2016)
...There's nothing that says a table can't have more than one unique index on it.
I am always amazed at how many people struggle to believe this.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 23, 2016 at 6:37 am
Ed Wagner (11/22/2016)
David.Poole (11/21/2016)
I've reached the conclusion that people don't realise what a well thought out data model gives them and they aren't interested in knowing.They see SQL Server as a dumb data store and regard constraints as an affront limiting their flexibility. They absolutely don't see that an enforced data model benefits data quality or that a normalised data model represents a concept in a way that gives a fine grained understanding of the domain. Neither do they care that yhe FKs, PKs, check constraints give the query optimiser a boost.
This is because they don't understand it, they don't care enough to learn or both.
I had a similar discussion with my new team member the other day when talking about leveraging SQL Server to store analytical data.
There is a lot of people who don't normalize data, create any real relational models, and utilize any of the other typical RDBMS features that go into supporting those efforts. When it comes to developing a database that supports an application, you commonly see a different approach. The technology professionals will normalize the data, actually think about the relational model, apply FK/PK and constraints to better support the data storage needs of the app.
When it comes to analytics however, especially those creating more of database for reading data they absorb from other data sources, you just end up with a data model that represents a single report per table. There is no relational model, no FK/PK, and no indexes. Just a database to looks like a document store. NoSQL anyone?
What is the true business sell of creating an actual model, conforming all those documents into one model, where they all relate to one another versus just creating a bunch of tables that are used as ad-hoc reports for Excel, Tableu and more? There are some, but when you start talking about the dev time needed to create a model versus just making a single data extract available in a single table on the fly, it's hard to sell them on the time needed for proper models.
November 23, 2016 at 8:21 am
What is you natural key for "person" records?
In most cases there isn't one. I would certainly never use an SSN for this. But that doesn't mean that this is true for all things. I've seen integers (and guids) used when there was a clear and obvious choice for a key.
November 24, 2016 at 11:39 am
RonKyle (11/23/2016)
What is you natural key for "person" records?
In most cases there isn't one. I would certainly never use an SSN for this. But that doesn't mean that this is true for all things. I've seen integers (and guids) used when there was a clear and obvious choice for a key.
I have had to correct systems which used combinations of various things like first name / last name / address (which was easy to fail as my son has the same name as I and lived at the same address), address and birthday (which fails for twins living at the same address), name and phone number :pinch:.
Using a seeded hash of the SSN isn't too bad, architecturally I would suggest an integer key of that table, especially in high cardinality systems.
😎
November 24, 2016 at 10:47 pm
Oh, it can be a total train wreck even if both organizations are using the same software and database platform. Different business workflows, business rules, etc. need to be merged, cross-referenced, etc., even if the underlying database structures etc are mostly the same...
But, yes. "natural keys" kinda suck in practice.
Too many places focus way more on database design for getting data INTO the database, especially when the DB basically mimics the application "structure" but 0 focus on getting the data out as information (aka reporting). One of my previous bosses suggested that it's a good idea to at least pay some lip service for reporting w.r.t. schema designs...
OK, blahblah data warehouse/datamart blahblah... well, we still have to polish more than a few turds to get the data into those shiny castles...
November 25, 2016 at 1:36 am
RonKyle (11/23/2016)
What is you natural key for "person" records?
In most cases there isn't one. I would certainly never use an SSN for this. But that doesn't mean that this is true for all things. I've seen integers (and guids) used when there was a clear and obvious choice for a key.
That was exactly what I was asking about - can you show those cases when a natural key is the best choice?
P.S. When somebody mentioned SSN I always want to remind:
There are several billions people who never were and never will be associated with SSN.
Worth keeping it in mind.
_____________
Code for TallyGenerator
Viewing 15 posts - 31 through 45 (of 89 total)
You must be logged in to reply to this topic. Login to reply