April 2, 2017 at 5:01 pm
jcelko212 32090 - Sunday, April 2, 2017 11:33 AMJeff Moden - Thursday, March 30, 2017 8:24 PMjcelko212 32090 - Thursday, March 30, 2017 1:33 PMA table must have a key, by definition. This key cannot be a generic "id"; that is a belief in Kabbalah magic.In all the time I've known you to spill such nonsense, you've never ever suggested what should be used as the primary key of an "employee" table (for just one of many examples) even though you've been asked dozens of times by me alone. Unless you can come up with a non arbitrary, immutable key to uniquely and certainly identify an employee for the life of an employee that doesn't also violate PII requirements, please stop publishing this falsehood.
Did you ever have a freshman philosophy course? Where they went into Aristotelian logic and the foundations of Western thought. One of our principles is the Law of Identity, which has nothing to do with the Sybase proprietary table property of the same name. The law of identity gets summarized as "A is A" in the popular literature. It actually says that to be something is to be something in particular. To be nothing in particular or anything in general, is to not be at all. So the first question you would ask in the case of a database is, what is the role being played by an entity in a data model.
The reason I mention Kabbalah numbers in my postings is that this is the exact opposite of Western thought and mathematics. This asserts that God has given everything a number, which is written in Hebrew letters (they use the letters for digits, a really bad design that leads to ambiguity). If you know the magic number you gain magical powers over the entity. For example, one of the names of God is 216 letters long. This is essentially what you are asking this question about a universal magic employee or whatever name.
In data modeling were basically the descendants of Aristotle's genus and species model of knowledge, not Hebrew mysticism. This is why for decades, and in all my books, I said you need to find an appropriate standard encoding for the particular data element with which you are working in your particular data model, your particular database. I have then advocated that you look for industry standards, maintained outside your organization whenever possible. The few rare times that you actually need to invent your own encoding schemes. I've also had a good set of rules for how to design and encoding scheme.
The IDENTITY table property in T-SQL is an old UNIX left over. It counts the insertion attempts (not even successes) on one table, on one machine, and one particular release of one particular vendor's product (Microsoft) and has no universality at all. I could install the same schema on two separate machines, and the identity properties would get out of sync. What would force them to stay the same? This makes about as much sense as identifying an automobile by its parking space number in one particular garage assuming that for every space you park it again will get the same parking space number. This is obviously absurd and that's why the industry has a VIN number.
All of that is interesting but, once again, you avoid even making a recommendation as to what the PK for an Employee table should be. Until you can make such a recommendation that will stand through the tests of a true PK, your arguments against using an IDENTITY column are just opinion.
That being said, do you have a recommendation for a PK on an Employee table or not?
--Jeff Moden
April 3, 2017 at 9:00 am
Jeff Moden - Thursday, March 30, 2017 8:24 PM
I'm not sure how I can be clearer about this. There are no Kabbalah numbers in RDBMS. Your request makes no sense because your as asking for a magic, generic, universal Kabbalah number. Do you believe that if you turn over any object in the universe will have such a thing burned in the bottom of it by the Lord God? Apparently. But if you think of building data models then you start looking at the role that something plays within the model not its broad general category. Employees play a role in the data model; they can be a lawful person (which includes corporate entities), a physical person or just a placeholder that will be filled later. In one data model, the employee might be defined by law with the tax number. Whoops, there's lots of taxing entities with their own systems. Some countries maybe may require a citizen identification number.
When I worked for a state prison system decades ago, we identified our inmates with what's called a 10 card; in those days it was a physical paper and ink set of all 10 fingerprints. The US happens to be on the Henry Galton system (other countries use other fingerprinting systems) and when we moved inmates, we took 10 cards to make sure we got the same guy at both ends of the system. I'm told this is now done electronically and you put your hand on scanning plate. I'm expecting in the next few years to see the Toshiba DNA scanning chip to be built into systems that require superhigh security.
But a more casual level of security is quite happy to believe that my email address and the password with an occasional security question is a fine way to validate that I am me. It all has to be within context; no universal Kabbalah numbers here.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 3, 2017 at 4:21 pm
jcelko212 32090 - Sunday, April 2, 2017 11:33 AMJeff Moden - Thursday, March 30, 2017 8:24 PM
I'm not sure how I can be clearer about this. There are no Kabbalah numbers in RDBMS. Your request makes no sense because your as asking for a magic, generic, universal Kabbalah number. Do you believe that if you turn over any object in the universe will have such a thing burned in the bottom of it by the Lord God? Apparently. But if you think of building data models then you start looking at the role that something plays within the model not its broad general category. Employees play a role in the data model; they can be a lawful person (which includes corporate entities), a physical person or just a placeholder that will be filled later. In one data model, the employee might be defined by law with the tax number. Whoops, there's lots of taxing entities with their own systems. Some countries maybe may require a citizen identification number.
When I worked for a state prison system decades ago, we identified our inmates with what's called a 10 card; in those days it was a physical paper and ink set of all 10 fingerprints. The US happens to be on the Henry Galton system (other countries use other fingerprinting systems) and when we moved inmates, we took 10 cards to make sure we got the same guy at both ends of the system. I'm told this is now done electronically and you put your hand on scanning plate. I'm expecting in the next few years to see the Toshiba DNA scanning chip to be built into systems that require superhigh security.
But a more casual level of security is quite happy to believe that my email address and the password with an occasional security question is a fine way to validate that I am me. It all has to be within context; no universal Kabbalah numbers here.
Hmm, OK. But would you assign a sequential employee_id here or not? If not, how do you make sure you have a unique, non-changing id for every employee?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 4, 2017 at 7:30 am
jcelko212 32090 - Sunday, April 2, 2017 11:33 AMJeff Moden - Thursday, March 30, 2017 8:24 PM
I'm not sure how I can be clearer about this. There are no Kabbalah numbers in RDBMS. Your request makes no sense because your as asking for a magic, generic, universal Kabbalah number. Do you believe that if you turn over any object in the universe will have such a thing burned in the bottom of it by the Lord God? Apparently. But if you think of building data models then you start looking at the role that something plays within the model not its broad general category. Employees play a role in the data model; they can be a lawful person (which includes corporate entities), a physical person or just a placeholder that will be filled later. In one data model, the employee might be defined by law with the tax number. Whoops, there's lots of taxing entities with their own systems. Some countries maybe may require a citizen identification number.
When I worked for a state prison system decades ago, we identified our inmates with what's called a 10 card; in those days it was a physical paper and ink set of all 10 fingerprints. The US happens to be on the Henry Galton system (other countries use other fingerprinting systems) and when we moved inmates, we took 10 cards to make sure we got the same guy at both ends of the system. I'm told this is now done electronically and you put your hand on scanning plate. I'm expecting in the next few years to see the Toshiba DNA scanning chip to be built into systems that require superhigh security.
But a more casual level of security is quite happy to believe that my email address and the password with an occasional security question is a fine way to validate that I am me. It all has to be within context; no universal Kabbalah numbers here.
Heh... ever evasive as always. My previous question simply asked what you would use as a proper PK for an employee table and you evaded that question as you do now.
So, let me make it more clear and simple for you... What would you use as a PK for an Employee table that will meet the requirements for a PK?
No more lectures about Kabbalah numbers or the other stuff. You've beat that to death an we get it. No more assumptions about what I think. Get to the point... educate us all and answer the really simple question. With all your years of experience and knowledge of ANSI/ISO, RDBMS usage, and table design, you must have a simple answer.
--Jeff Moden
April 4, 2017 at 11:33 am
jcelko212 32090 - Sunday, April 2, 2017 11:33 AMJeff Moden - Thursday, March 30, 2017 8:24 PM
Your request makes no sense because your as asking for a magic, generic, universal Kabbalah number. ... Some countries maybe may require a citizen identification number.
But a more casual level of security is quite happy to believe that my email address and the password with an occasional security question is a fine way to validate that I am me. It all has to be within context; no universal Kabbalah numbers here.
No, you are demanding a "universal" number, assigned by some other magical entity that will always be unique. But people don't have VINs or any other such "official" number. So systems assign their own unique number. You seem hung up on how that number was generated, but that's irrelevant really as long as it's unique. It's just much easier to use an identity number.
Email address? Seriously? Something that could change every week? That's absurd as an identifier for any real-life system. It may be allowed as external value used to log into the system, but the internal id must be something else.
Federal inmates, for example, have a unique number as well. For example, 00131-177 is for Jeffrey MacDonald. Email would be difficult to use, since many of them aren't allowed access to computers and/or email.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 4, 2017 at 11:59 am
Joe Torre - Tuesday, April 4, 2017 11:46 AMThe answer is 42,
no, today it is 17
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2017 at 12:03 pm
SQLRNNR - Tuesday, April 4, 2017 11:59 AMJoe Torre - Tuesday, April 4, 2017 11:46 AMThe answer is 42,no, today it is 17
Yep, 17 for sure, since it's a prime number. If we're going to Kabbalah, might as well prime Kabbalah.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 4, 2017 at 12:23 pm
This all reminds me of co-workers asking what is the part number. Are they referring to the manufacturer's part number, the supplier's part number, or the one we assign to it in our inventory. All three part numbers refer to the class of the same object.
I do despise the Identity property in T-SQL though. The SEQUENCES objects introduced in ss2012 are awesome. The identity reduces inserts to one at a time else you won't know which identity value goes with which row you've inserted. I prefer to insert batches that have been preassigned sequence numbers by me.
April 4, 2017 at 1:43 pm
Bill Talada - Tuesday, April 4, 2017 12:23 PMThis all reminds me of co-workers asking what is the part number. Are they referring to the manufacturer's part number, the supplier's part number, or the one we assign to it in our inventory. All three part numbers refer to the class of the same object.I do despise the Identity property in T-SQL though. The SEQUENCES objects introduced in ss2012 are awesome. The identity reduces inserts to one at a time else you won't know which identity value goes with which row you've inserted. I prefer to insert batches that have been preassigned sequence numbers by me.
You can still do multiple INSERTs at once and use the OUTPUT clause to get the identity results when you need them.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 4, 2017 at 6:22 pm
Bill Talada - Tuesday, April 4, 2017 12:23 PMThis all reminds me of co-workers asking what is the part number. Are they referring to the manufacturer's part number, the supplier's part number, or the one we assign to it in our inventory. All three part numbers refer to the class of the same object.I do despise the Identity property in T-SQL though. The SEQUENCES objects introduced in ss2012 are awesome. The identity reduces inserts to one at a time else you won't know which identity value goes with which row you've inserted. I prefer to insert batches that have been preassigned sequence numbers by me.
How do you figure that the IDENTITY property reduces inserts to one at a time? The OUTPUT clause takes care of that pretty easily. You're personal preference being understood and with the possible exception of the classic Invoice/Invoice Detail problem when present in a batch of multiple invoices, I see no particular advantages in using SEQUENCE on a single table as compared to IDENTITY.
--Jeff Moden
April 5, 2017 at 12:57 pm
Bill Talada - Tuesday, April 4, 2017 12:23 PMI see no particular advantages in using SEQUENCE on a single table as compared to IDENTITY.
The amount of control you get with sequence is a huge advantage. Since identity is not a column, but a table property, I can only have one of them in a table. There's no particular limit to the number of sequences I can use. Within each sequence I can set any of these clauses
:CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
I can make the next sequence value into a default for a column, but I can also overwrite it with an explicit assignment. I was rather surprised but one of the most useful clauses is cycle. I can in effect "pre-allocate parking spaces" for data elements..
The identity property simply counts physical insertion attempts. .
Please post DDL and follow ANSI/ISO standards when asking for help.
April 5, 2017 at 1:10 pm
Jeff Moden - Tuesday, April 4, 2017 6:22 PMBill Talada - Tuesday, April 4, 2017 12:23 PMI see no particular advantages in using SEQUENCE on a single table as compared to IDENTITY.
The amount of control you get with sequence is a huge advantage. Since identity is not a column, but a table property, I can only have one of them in a table. There's no particular limit to the number of sequences I can use. Within each sequence I can set any of these clauses
:CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
I can make the next sequence value into a default for a column, but I can also overwrite it with an explicit assignment. I was rather surprised but one of the most useful clauses is cycle. I can in effect "pre-allocate parking spaces" for data elements..
The identity property simply counts physical insertion attempts. .
And a sequence simply counts the number of values you're requested for that sequence, i.e., a roughly equivalent type of value. I really don't see why you're so hung on how the value is incremented: the main point is to simply have a unique, ascending value, using row insertion is simply a convenient and automatic mechanism for doing that, so I don't see how that would somehow invalidate the use of the value it generated.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 5, 2017 at 4:40 pm
jcelko212 32090 - Wednesday, April 5, 2017 12:57 PMI can make the next sequence value into a default for a column, but I can also overwrite it with an explicit assignment. I was rather surprised but one of the most useful clauses is cycle. I can in effect "pre-allocate parking spaces" for data elements..The identity property simply counts physical insertion attempts. .
Understood and agreed but 99.9% of the time, all you need it for is to "simply count physical insertion attempts".
Now, considering how much you're against the use of IDENTITY columns because "There are no Kabbalah numbers in RDBMS", what is it that YOU actually use SEQUENCE for?
And why the hell won't you answer the question about what YOU use as a PK for an Employee table? I'm thinking that the reason is that you either don't have an answer or, more likely, you actually DO use some form of auto-incrementing value like an IDENTITY or SEQUENCE and you simply don't want to admit it because it would shoot years of your hooie about IDENTITY columns out of the sky. 😉
--Jeff Moden
April 5, 2017 at 5:49 pm
jcelko212 32090 - Wednesday, April 5, 2017 12:57 PM
>> Now, considering how much you're against the use of IDENTITY columns because "There are no Kabbalah numbers in RDBMS", what is it that YOU actually use SEQUENCE for? <<
I use them for sequences, of course! 🙂 I also like to use the "_seq" postfix for the attribute property (if you don't know that one look it up in the ISO 11179 standards). For example, in Italy invoice numbers are required by law to be sequential. In the United States, we like to have sequential numbers for checks. The sequence is a fairly natural data structure..
One of the things I like about the NEXT operator in CREATE SEQUENCE is that it returns a numeric value that I can plug into any kind of formula I want. And I can have more than one sequence in the same row of the same table; you can't do that with IDENTITY because of the table property. For example, I had one client who liked each particular kind of identifier to be a multiple of some prime.
>> And why the hell won't you answer the question about what YOU use as a PK for an Employee table? I'm thinking that the reason is that you either don't have an answer or, more likely, you actually DO use some form of auto-incrementing value like an IDENTITY or SEQUENCE and you simply don't want to admit it because it would shoot years of your hooie about IDENTITY columns out of the sky. <<
Again, I think it's a dumb question when I need to identify an employee I look for tax ID, company internal things (much as we hate it, sometimes we have to talk to the accounting department) and if I actually have to design and identifier. I want validation and verification built into it. That means check digits. That means a regular expression. That means other information possibly embedded in the identifier. TITY columns out of the sky. 😉
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply