|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Identity keys in a One-to-Zero-or-Many relationshipCREATE TABLE CustomerTypes ( PKCustomerType INT IDENTITY (1,1) NOT NULL, CustomerTypeDesc VARCHAR(30) ) GO INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail') INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale') INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate') CREATE TABLE Customers ( PKCustomer INT IDENTITY (1,1) NOT NULL, CustomerID VARCHAR(20), FKCustomerType INT ) GO INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1) INSERT Customers Values ('CUST002', 3) INSERT Customers Values ('CUST003', 2) How should I handle a case where I don't want to specify a customer type for whatever reason? Perhaps it is unknown or not applicable in some cases. I could add a 'None or N/A' record in the CustomerTypes table for that purpose, but when validating the data in an INSERT stored procedure, for example, I would like to have a value to use as default in the case of a missing of invalid field value being passed for FKCustomerType. If it weren't an identity field in the CustomerTypes table, I could just create a CustomerType record with a PKCustomerType value of 0 and always use that value in such cases. With it being an identity field, however, I can't count on a specific value for that purpose. It seems I have two options: 1) Create the CustomerTypes table without PKCustomerType being an identity field...insert the 'None or N/A' record, assigning it a PKCustomerType value of, say, 0...alter the table to make PKCustomerType and identity field (1,1) 2) Merely use NULL in Customers where CustomerType isn't specified. Is there a better way? If not, any insight on why one approach would be better than the other? I'm leaning toward option 2 at this point. > 2) Merely use NULL in Customers where CustomerType isn't specified. If CustomerType is not specified because it is unknown, then this may be a valid approach. However, you may want to give users the option of saying Unknown, and have it be a value in the CustomerTypes table. Then if you later want to differentiate between unknown and unspecified, you can add an option. There is no reason to hard-code the default value in the parameter to the stored procedure. Have the application generate its dropdown from the CustomerTypes table, and you are done. A Aaron Bertrand [SQL Server MVP] wrote:
> > 2) Merely use NULL in Customers where CustomerType isn't specified. That's part of my quandry. I *would* generate the drop-down list from> > If CustomerType is not specified because it is unknown, then this may be a > valid approach. > > However, you may want to give users the option of saying Unknown, and have > it be a value in the CustomerTypes table. Then if you later want to > differentiate between unknown and unspecified, you can add an option. > > There is no reason to hard-code the default value in the parameter to the > stored procedure. Have the application generate its dropdown from the > CustomerTypes table, and you are done. > the CustomerTypes table, but how would I establish a default selection when an identity key is used? > That's part of my quandry. I *would* generate the drop-down list from Have a column that tells you whether or not this value is the default. If > the CustomerTypes table, but how would I establish a default selection > when an identity key is used? you are manually entering "none" as the name/description value, surely you can change default=0 to default=1 in the extra column. Now, the app can pull the list of potential values, and select the value where default=1 (until the user overrides it). Aaron Bertrand [SQL Server MVP] wrote:
> > That's part of my quandry. I *would* generate the drop-down list from Hmm. That would be one way to manage it. However, adding an additional> > the CustomerTypes table, but how would I establish a default selection > > when an identity key is used? > > Have a column that tells you whether or not this value is the default. If > you are manually entering "none" as the name/description value, surely you > can change default=0 to default=1 in the extra column. Now, the app can > pull the list of potential values, and select the value where default=1 > (until the user overrides it). column to track a piece of information that will be the same for all but one row doesn't seem like the optimal solution either. You've given me an idea, though. I think what I'm going to do is enforce RI and provide a system setup screen in the application whereby the system administrator can specify the default selection for all such drop-down lists. Thanks! Rich Richard Carpenter wrote:
> Aaron Bertrand [SQL Server MVP] wrote: Also, how would you go about enforcing the rule that only one row in> > > That's part of my quandry. I *would* generate the drop-down list from > > > the CustomerTypes table, but how would I establish a default selection > > > when an identity key is used? > > > > Have a column that tells you whether or not this value is the default. If > > you are manually entering "none" as the name/description value, surely you > > can change default=0 to default=1 in the extra column. Now, the app can > > pull the list of potential values, and select the value where default=1 > > (until the user overrides it). > that table may have a value of 1 for that extra field? Thanks. Rich > Also, how would you go about enforcing the rule that only one row in Assuming that you disallow direct updates / inserts to the table, then in > that table may have a value of 1 for that extra field? the stored procedure that sets the default row, it first sets the value to 0 for all rows. If you need it to be an active constraint, you could easily use a trigger, but like most things, there are trade-offs. A >> Considering the following: << Let's fix what you posted so that it follows the most basic RDBMSdesign principles. For example, why do you have no keys? Why did you think that IDENTITY is every used? Why did you use PK- prefixes in violation of ISO-11179 rules? CREATE TABLE CustomerTypes (customer_type INTEGER NOT NULL PRIMARY KEY, customer_type_desc VARCHAR(30) NOT NULL); I see from the use of IDENTITY that you did not bother with designing an encoding scheme. You might want to learn how to do that CREATE TABLE Customers (customer_id VARCHAR(20) NOT NULL PRIMARY KEY, (customer_type INTEGER NOT NULL REFERENCES CustomerTypes (customer_type) ON UPDATE CASCADE, .. ); VARCHAR(20) is a bit long, but by the definition of an identifier, this has to be your key. Frankly, I would look for a DUNS number or some indusrty standard code. Stop putting prefixes that tell you **how** a data element is used in a table. The name of a data element is supposed to tell you **what** it is. >> How should I handle a case where I don't want to specify a customer type for whatever reason? Perhaps it is unknown or not applicable in some cases. << Then you need codes for those situations if they are logicallydifferent. For example, the ICD codes for disease have '000.000' which means "undiagnosed" and '999.999' which means "we did all the test and still don't know!" -- very different kinds of missing data! VERY IMPORTANT to distinguish them!! Matter of life and death, in fact. >> I could add a 'None or N/A' record [sic] in the CustomerTypes table for that purpose, but when validating the data in an INSERT stored procedure, for example, I would like to have a value to use as default in the case of a missing of invalid field [sic] value being passed for FKCustomerType [sic]. << Now we are getting to your REAL problem. Let's get back to the basicsof an RDBMS. Rows are not records; fields are not columns; tables are not files; there is no sequential access or ordering in an RDBMS, so faking a record number or the lines on a piece of paper with a proprietary IDENTITY property is dead wrong. You do not have the right mindset, and all you are going to get on a Newsgroup is a few kludges to help you fake it for a long period of time before the collapse. >> If it weren't an identity field [sic] in the CustomerTypes table, I could just create a CustomerType record [sic] with a PKCustomerType value of 0 and always use that value in such cases. With it being an identity field [sic], however, I can't count on a specific value for that purpose. << Surprise! Surprise! Surprise! See how non-relational, non-verifiable,non-portable proprietary extensions screw up things? >> It seems I have two options: << Yes; do it right or kludge it :)>> 1) Create the CustomerTypes table without PKCustomerType [sic] being an identity field [sic] ...insert the 'None or N/A' record, assigning it a PKCustomerType [sic] value of, say, 0...alter the table to makePKCustomerType and identity field [sic] (1,1) << Almost! Stop using IDENTITY. This is an RDBMS and not a sequentialfile system. >> 2) Merely use NULL in Customers where CustomerType isn't specified. << Maybe; do you need to know anything about the missing values? Or justthat it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR SMARTIES or DATA & DATABASES and then read the chapters on scales & measurements, and how to design encoding schemes. The research pattern is simple: 1) Look for industry standards (Google it!) 2) Look for company standards (see the accounting department for help) 3) In the remaining 5% of the cases where you have to invent something, pick a type of encoding and follow the rules for good design. My guess in this example is a hierachy or vector code because customers break down into tax/tax exempt, domestic/foreign and so forth within retailers and wholesalers. > design principles. For example, why do you have no keys? Why did you Why do you think SURROGATE KEYS are never used? It would appear you are one > think that IDENTITY is every used? Why did you use PK- prefixes in of the only people in the industry who do not use them. Perhaps its an implementation experience thing compared to theory. > Almost! Stop using IDENTITY. This is an RDBMS and not a sequential Your bias and misunderstanding of how IDENTITY works is embarrasing - READ > file system. THE MANUAL! > that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR Only if you want theory, for practical advice get a SQL Server specific > SMARTIES or DATA & DATABASES and then read the chapters on scales & book. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1151601604.581696.66800@75g2000cwc.googlegroups.com... >>> Considering the following: << > > Let's fix what you posted so that it follows the most basic RDBMS > design principles. For example, why do you have no keys? Why did you > think that IDENTITY is every used? Why did you use PK- prefixes in > violation of ISO-11179 rules? > > CREATE TABLE CustomerTypes > (customer_type INTEGER NOT NULL PRIMARY KEY, > customer_type_desc VARCHAR(30) NOT NULL); > > I see from the use of IDENTITY that you did not bother with designing > an encoding scheme. You might want to learn how to do that > > CREATE TABLE Customers > (customer_id VARCHAR(20) NOT NULL PRIMARY KEY, > (customer_type INTEGER NOT NULL > REFERENCES CustomerTypes (customer_type) > ON UPDATE CASCADE, > .. ); > > VARCHAR(20) is a bit long, but by the definition of an identifier, this > has to be your key. Frankly, I would look for a DUNS number or some > indusrty standard code. > > Stop putting prefixes that tell you **how** a data element is used in a > table. The name of a data element is supposed to tell you **what** it > is. > >>> How should I handle a case where I don't want to specify a customer type >>> for whatever reason? Perhaps it is unknown or not applicable in some >>> cases. << > > Then you need codes for those situations if they are logically > different. For example, the ICD codes for disease have '000.000' which > means "undiagnosed" and '999.999' which means "we did all the test and > still don't know!" -- very different kinds of missing data! VERY > IMPORTANT to distinguish them!! Matter of life and death, in fact. > >>> I could add a 'None or N/A' record [sic] in the CustomerTypes table for >>> that purpose, but when validating the data in an INSERT stored >>> procedure, for example, I would like to have a value to use as default >>> in the case of a missing of invalid field [sic] value being passed for >>> FKCustomerType [sic]. << > > Now we are getting to your REAL problem. Let's get back to the basics > of an RDBMS. Rows are not records; fields are not columns; tables are > not files; there is no sequential access or ordering in an RDBMS, so > faking a record number or the lines on a piece of paper with a > proprietary IDENTITY property is dead wrong. You do not have the right > mindset, and all you are going to get on a Newsgroup is a few kludges > to help you fake it for a long period of time before the collapse. > >>> If it weren't an identity field [sic] in the CustomerTypes table, I >>> could just create a CustomerType record [sic] with a PKCustomerType >>> value of 0 and always use that value in such cases. With it being an >>> identity field [sic], however, I can't count on a specific value for >>> that purpose. << > > Surprise! Surprise! Surprise! See how non-relational, non-verifiable, > non-portable proprietary extensions screw up things? > >>> It seems I have two options: << > > Yes; do it right or kludge it :) > >>> 1) Create the CustomerTypes table without PKCustomerType [sic] being an >>> identity field [sic] ...insert the 'None or N/A' record, assigning it a >>> PKCustomerType [sic] value of, say, 0...alter the table to >>> makePKCustomerType and identity field [sic] (1,1) << > > Almost! Stop using IDENTITY. This is an RDBMS and not a sequential > file system. > >>> 2) Merely use NULL in Customers where CustomerType isn't specified. << > > Maybe; do you need to know anything about the missing values? Or just > that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR > SMARTIES or DATA & DATABASES and then read the chapters on scales & > measurements, and how to design encoding schemes. The research pattern > is simple: > > 1) Look for industry standards (Google it!) > > 2) Look for company standards (see the accounting department for help) > > 3) In the remaining 5% of the cases where you have to invent something, > pick a type of encoding and follow the rules for good design. My guess > in this example is a hierachy or vector code because customers break > down into tax/tax exempt, domestic/foreign and so forth within > retailers and wholesalers. > Good gravy! I'll try to respond to your individual points without
taking too much offense to your abrasive tone... --CELKO-- wrote: > >> Considering the following: << Really, don't trouble yourself. It was a simple example I threw> > Let's fix what you posted so that it follows the most basic RDBMS > design principles. together to try and illustrate my question. Nothing more. I wasn't shooting for theoretical precision. I didn't (and still don't, as I will explain in a moment) think it that necessary in this case. Show quote > For example, why do you have no keys? Why did you It is what it is. If my particular (hypothetical, I might add) business> think that IDENTITY is every used? Why did you use PK- prefixes in > violation of ISO-11179 rules? > > CREATE TABLE CustomerTypes > (customer_type INTEGER NOT NULL PRIMARY KEY, > customer_type_desc VARCHAR(30) NOT NULL); > > I see from the use of IDENTITY that you did not bother with designing > an encoding scheme. You might want to learn how to do that > > CREATE TABLE Customers > (customer_id VARCHAR(20) NOT NULL PRIMARY KEY, > (customer_type INTEGER NOT NULL > REFERENCES CustomerTypes (customer_type) > ON UPDATE CASCADE, > .. ); > > VARCHAR(20) is a bit long, but by the definition of an identifier, this > has to be your key. Frankly, I would look for a DUNS number or some > indusrty standard code. process requires the use of specific proprietary customer types, then why complicate it? > Stop putting prefixes that tell you **how** a data element is used in a I really don't see the significance of the distinction. The fact that> table. The name of a data element is supposed to tell you **what** it > is. the name of the field starts with PK *does* tell me what it is. I really think this one is a matter of personal preference. > >> How should I handle a case where I don't want to specify a customer type for whatever reason? Perhaps it is unknown or not applicable in some cases. << A bit dramatic, but I hear what you're saying. However, my question> > Then you need codes for those situations if they are logically > different. For example, the ICD codes for disease have '000.000' which > means "undiagnosed" and '999.999' which means "we did all the test and > still don't know!" -- very different kinds of missing data! VERY > IMPORTANT to distinguish them!! Matter of life and death, in fact. really doesn't have anything to do with what values are available to the user, but how to handle it when the user doesn't feel inclined to specify a fact that is not strictly required. On the application's data entry screen, the user may only need to worry about some of the available fields. There is no reason to force them to touch every one when not all are relevant in every case. My question is, how to best handle that foreign key [column] value in that case. > >> I could add a 'None or N/A' record [sic] in the CustomerTypes table for that purpose, but when validating the data in an INSERT stored procedure, for example, I would like to have a value to use as default in the case of a missing of invalid field [sic] value being passed for FKCustomerType [sic]. << Yeah, I'm guilty of referring to them in the very manner that makes a> > Now we are getting to your REAL problem. Let's get back to the basics > of an RDBMS. Rows are not records; fields are not columns; tables are > not files; there is no sequential access or ordering in an RDBMS, so > faking a record number or the lines on a piece of paper with a > proprietary IDENTITY property is dead wrong. You do not have the right > mindset, and all you are going to get on a Newsgroup is a few kludges > to help you fake it for a long period of time before the collapse. certain type of person grit their teeth. I think that since we both know exactly what I mean, then it really isn't an issue worth allowing to cloud the matter. Wouldn't you agree? > >> If it weren't an identity field [sic] in the CustomerTypes table, I could just create a CustomerType record [sic] with a PKCustomerType value of 0 and always use that value in such cases. With it being an identity field [sic], however, I can't count on a specific value for that purpose. << Hence my original question. Again, I really don't understand why it has> > Surprise! Surprise! Surprise! See how non-relational, non-verifiable, > non-portable proprietary extensions screw up things? > to be this difficult. How would *you* go about it, if forcing the user to touch every field on the data entry form is *not* acceptable? > >> It seems I have two options: << There is nothing sequential about the requirements I've established> > Yes; do it right or kludge it :) > > >> 1) Create the CustomerTypes table without PKCustomerType [sic] being an identity field [sic] ...insert the 'None or N/A' record, assigning it a PKCustomerType [sic] value of, say, 0...alter the table to makePKCustomerType and identity field [sic] (1,1) << > > Almost! Stop using IDENTITY. This is an RDBMS and not a sequential > file system. here. I don't care if the key is sequentially numbered, random alpha-numeric or caveman hieroglyphics. All I require is that it be guaranteed to be unique and generated automatically. Show quote > >> 2) Merely use NULL in Customers where CustomerType isn't specified. << Honestly, that just seemed like a whole lot of unnecessary nit-picking> > Maybe; do you need to know anything about the missing values? Or just > that it is missing? Get a copy of SQL PROGRAMMING STYLE, SQL FOR > SMARTIES or DATA & DATABASES and then read the chapters on scales & > measurements, and how to design encoding schemes. The research pattern > is simple: > > 1) Look for industry standards (Google it!) > > 2) Look for company standards (see the accounting department for help) > > 3) In the remaining 5% of the cases where you have to invent something, > pick a type of encoding and follow the rules for good design. My guess > in this example is a hierachy or vector code because customers break > down into tax/tax exempt, domestic/foreign and so forth within > retailers and wholesalers. and tangent traversal than was really required here. I can understand if you feel in your infinite wisdom that it is better to suggest to me where else I might direct my search to find the answer than give it to me directly, but to put so much effort into avoiding answering my question while at the same time trying to educate me (or, arguably more accurate, indoctrinate me according to your own personal opinions) in related regards does make me wonder who's best interest you have at heart here. >> I'll try to respond to your individual points without taking too much offense to your abrasive tone... << Good, that is the way to handle it.>> Really, don't trouble yourself. It was a simple example I threw together to try and illustrate my question. Nothing more. I wasn't shooting for theoretical precision... I didn't (and still don't, as I will explain in a moment) think it that necessary in this case. << What you missed was PRACTICAL precision. Do you think it took anylonger for me to re-type and correct your code than it took you to write it? Nope. A professional should be doing good work as a reflex, not as a super-human effort. You are not at the point where you can write clean SQL without effort nor do youy know about data design. >> It is what it is. If my particular (hypothetical, I might add) business process requires the use of specific proprietary customer types, then why complicate it? << I treated this as a real problem. "Entia non sunt multiplicandapraeter necessitatem." (No more things should be presumed to exist than are absolutely necessary) -- William Occam (c. 1280-1349). >> I really don't see the significance of the distinction. The fact that the name of the field [sic] starts with PK *does* tell me what it is. << NO! That is how it is used in that table. If it appears in anothertable where it is a foreign key, are you going to re-name it? What if it appears twice in a table? What is the fundamental rule of data element names? One and only one name per data element. >> I really think this one is a matter of personal preference. << No. It is a matter of ISO-11179 Standards and the last 20 years ofdata modeling. Do you also invent your own private language when you write English? Probably not. >> A bit dramatic, but I hear what you're saying. However, my question really doesn't have anything to do with what values are available to the user, but how to handle it when the user doesn't feel inclined to specify a fact that is not strictly required. << LOL! I have a mental picture of a snotty french waiter doing dataentry anouncing, "I do not feel inclined to allow that fact to exist! Pooh!" A fact is required for a consistent model; values may vary. >> On the application's data entry screen, the user may only need to worry about some of the available fields. There is no reason to force them to touch every one when not all are relevant in every case. My question is, how to best handle that foreign key [column] value in that case. << How the front end handles entry is not a data base question. It isfree to provide values via some business rules and mechanisms as long as the inputs do not violate constraints. But defaults and constraints are a data base question. > No, I don't. Google thru this newsgroup and see how many stupid> > >> I could add a 'None or N/A' record [sic] in the CustomerTypes table for that purpose, but when validating the data in an INSERT stored procedure, for example, I would like to have a value to use as default in the case of a missing of invalid field [sic] value being passed for FKCustomerType [sic]. << >> Yeah, I'm guilty of referring to them in the very manner that makes a certain type of person grit their teeth. I think that since we both know exactly what I mean, then it really isn't an issue worth allowing to cloud the matter. Wouldn't you agree? << questions are posted because someone is still building file systems in SQL. This place is full of cursors, bit flags, attribute splitting, temp tables to mimic scratch tape files, contigous and sequential storage assumptions, etc. If this were an automobile newsgroup and someone kept referring to the "legs" on his car instead of "wheels" because he used to ride a horse, would you think that he was -- be nice -- a bit out of step? Probably not able to figure out how to do an oil change? Etc. >> How would *you* go about it, if forcing the user to touch every field on the data entry form is *not* acceptable? << Default values in the schema (NULL is the default DEFAULT, but try toavoid it), and some business rules in the front end (this company is a wholey-owned subsidary of company X, so it inherits some values from its parent, etc.) >> There is nothing sequential about the requirements I've established here. I don't care if the key is sequentially numbered, random alpha-numeric or caveman hieroglyphics.<< If you were a professional, you would care. You would be worried aboutverification and validation, industry standards, maintaining the system, etc. but instead ... >> All I require is that it be guaranteed to be unique and generated automatically. << all you are after is a quicky, magical, no-brains kludge to get overthe hump. >> Honestly, that just seemed like a whole lot of unnecessary nit-picking and tangent traversal than was really required here. << Sorry, I mistook you for someone who cares about his work. I want youto be a better RDBMS programmer than you want yourself to be. >> I can understand if you feel in your infinite wisdom that it is better to suggest to me where else I might direct my search to find the answer than give it to me directly, but to put so much effort into avoiding answering my question while at the same time trying to educate me (or, arguably more accurate, indoctrinate me according to your own personal opinions) in related regards does make me wonder who's best interest you have at heart here. << First of all, I am the "Standards & Best Practices ober alles" guy. Iactually have very few pure opinions that are not backed up by ANSI, ISO, a major authority like Dr. Codd or a few years of research of some kind. You asked a "wheels on a horse" kind of question, so it looked like you needed a real answer and not a kludge. --CELKO-- wrote:
> Ya know, in your two replies and well over 1000 words this is as close> >> How would *you* go about it, if forcing the user to touch every field on the data entry form is *not* acceptable? << > > Default values in the schema (NULL is the default DEFAULT, but try to > avoid it), and some business rules in the front end (this company is a > wholey-owned subsidary of company X, so it inherits some values from > its parent, etc.) > as you've come to actually answering my question. Ya know what else? I've decided the quality of the answer does not come close to justifying the crap you sling at people - well, at me anyway. I've not spent enough time around here to have a feel on whether it's personal or not. It is plain to see that you are extremely knowledgeable in the area of database design standards and theory. The trouble is, such prowess in that respect must have come at the terrible expense of any sort of social skills, as I have never in my life seen anyone so full of themself and so disdainful of those around them who don't worship the same pagan god of referential integrity. I simply asked how a certain scenario should be handled. I honestly *wanted* you to correct me where I may have been going about it wrong. What is wrong with giving me what you feel is the correct design approach and telling me why? Instead, you have to pick at everything about my post in the most obnoxioiusly critical way possible (embellishing on more than one point in a blatant act of grandstanding, I might add). Do you approach *every* minor situation with such stick-up-the-rear-endedness? You seem to have some sort of delusional superiority complex. I do appreciate the time you've given me to this point as I understand that you were probably actually trying to be helpful in your own caustic sort of way, but quite frankly, you seem to bring out the worst in me, so I'd appreciate it if you could just ignore me from this point forward, and I'll return the favor. I'll be more than happy to waltz into your killfile, as this has been little more than a big waste of both of our time. Regards, Rich > How should I handle a case where I don't want to specify a customer It should be NULL because its not specified, if you want to give it a value > type for whatever reason? Perhaps it is unknown or not applicable in you really ought to have a CustomerType of 'Not Specified'. Show quote > CREATE TABLE CustomerTypes You probably need to have a slight rethink on the constraints.....> ( > PKCustomerType INT IDENTITY (1,1) NOT NULL, > CustomerTypeDesc VARCHAR(30) > ) > GO > > INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail') > INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale') > INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate') > > CREATE TABLE Customers > ( > PKCustomer INT IDENTITY (1,1) NOT NULL, > CustomerID VARCHAR(20), > FKCustomerType INT > ) > GO > > INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1) > INSERT Customers Values ('CUST002', 3) > INSERT Customers Values ('CUST003', 2) CREATE TABLE CustomerTypes ( id INT IDENTITY (1,1) NOT NULL constraint sk_customertypes unique clustered, CustomerTypeDesc VARCHAR(30) constraint pk_customertype primary key nonclustered ) INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail') INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale') INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate') CREATE TABLE Customers ( id INT IDENTITY (1,1) NOT NULL constraint sk_customer unique clustered, CustomerID VARCHAR(20) not null constraint pk_customer primary key nonclustered, CustomerType_id INT NULL references CustomerTypes( id ) ) INSERT Customers (CustomerID, CustomerType_id) Values ('CUST001', 1) INSERT Customers (CustomerID, CustomerType_id) Values ('CUST003', NULL) Tony. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "Richard Carpenter" <rumble***@hotmail.com> wrote in message news:1151594340.887702.165300@b68g2000cwa.googlegroups.com... > Considering the following: > > CREATE TABLE CustomerTypes > ( > PKCustomerType INT IDENTITY (1,1) NOT NULL, > CustomerTypeDesc VARCHAR(30) > ) > GO > > INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail') > INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale') > INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate') > > CREATE TABLE Customers > ( > PKCustomer INT IDENTITY (1,1) NOT NULL, > CustomerID VARCHAR(20), > FKCustomerType INT > ) > GO > > INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1) > INSERT Customers Values ('CUST002', 3) > INSERT Customers Values ('CUST003', 2) > > How should I handle a case where I don't want to specify a customer > type for whatever reason? Perhaps it is unknown or not applicable in > some cases. I could add a 'None or N/A' record in the CustomerTypes > table for that purpose, but when validating the data in an INSERT > stored procedure, for example, I would like to have a value to use as > default in the case of a missing of invalid field value being passed > for FKCustomerType. If it weren't an identity field in the > CustomerTypes table, I could just create a CustomerType record with a > PKCustomerType value of 0 and always use that value in such cases. With > it being an identity field, however, I can't count on a specific value > for that purpose. It seems I have two options: > > 1) Create the CustomerTypes table without PKCustomerType being an > identity field...insert the 'None or N/A' record, assigning it a > PKCustomerType value of, say, 0...alter the table to make > PKCustomerType and identity field (1,1) > > 2) Merely use NULL in Customers where CustomerType isn't specified. > > Is there a better way? If not, any insight on why one approach would be > better than the other? I'm leaning toward option 2 at this point. > Why not use a referential integrity constraint?
Unknown and Inapplicable are distinct reasons attributed for missing data. And therefore, if your business demands that distinction, it makes sense to use separate values to represent them in a table. -- Anith Just to add, inapplicable attributes are best addressed using an entity
super-type/sub-type relationship. However, commonly people use to kludge them with NULLs as an easy workaround. -- Anith Anith Sen wrote:
> Why not use a referential integrity constraint? That would be my typical thinking as well, but RI doesn't work if the> > Unknown and Inapplicable are distinct reasons attributed for missing data. > And therefore, if your business demands that distinction, it makes sense to > use separate values to represent them in a table. > value is not required unless a default constraint is specified. With an identity key, the default value for the foreign key column cannot be specified in the table definition. On second thought, a better illustration of my question would be a name suffix (Jr., Sr. III, etc.). A person won't necessarily *have* a suffix in their name, yet forcing the user to select "None" from a list is perhaps a bit cumbersome from a UI standpoint. If that suffix column in the Customers table is a foreign key referencing the primary key column in a reference table, and that primary key column is an identity type key, then it would seem that the only workable foreign key value would be NULL. > suffix (Jr., Sr. III, etc.). A person won't necessarily *have* a suffix Why does the user have to actively select "none"? This can be the default, > in their name, yet forcing the user to select "None" from a list is > perhaps a bit cumbersome from a UI standpoint. pre-loaded choice, and used *unless* the user actively specified something different. Aaron Bertrand [SQL Server MVP] wrote:
> > suffix (Jr., Sr. III, etc.). A person won't necessarily *have* a suffix Right. However, how do you establish a default value when the primary> > in their name, yet forcing the user to select "None" from a list is > > perhaps a bit cumbersome from a UI standpoint. > > Why does the user have to actively select "none"? This can be the default, > pre-loaded choice, and used *unless* the user actively specified something > different. key on which it is based is an identity column? If I were generating my own keys - character values, for example - I might establish the key value for 'Unspecified' as something like '[_NONE_]' in a NameSuffixes table. In that case, I could design the data access layer of the application to always use that key value for unspecified suffixes. This would never change, not even from one implementation to the next. Obviously, in the case of identity columns, you have little control over what values are assigned beyond the first one. All that said, would it be a better design decision to allow for null values in the foreign key column or should the tables be designed differently? Rich > Right. However, how do you establish a default value when the primary Well, who says you have to use an IDENTITY column? This doesn't sound like > key on which it is based is an identity column? something that grows infinitely, but rather a defined and finite set. So, set the values up manually. 0 = none, 1 = jr., 2 = sr., etc. When you need to add a suffix to the available list, which should happen rarely, instead of just blindly inserting the value, you take the current max(id) and add 1. You can also add a column called [default] which tells you which value is the default. A It sounds like order is an entity in his business model so perhaps another
column called display_order..... -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%236o2qq$mGHA.4212@TK2MSFTNGP04.phx.gbl... >> Right. However, how do you establish a default value when the primary >> key on which it is based is an identity column? > > Well, who says you have to use an IDENTITY column? This doesn't sound > like something that grows infinitely, but rather a defined and finite set. > > So, set the values up manually. 0 = none, 1 = jr., 2 = sr., etc. When > you need to add a suffix to the available list, which should happen > rarely, instead of just blindly inserting the value, you take the current > max(id) and add 1. > > You can also add a column called [default] which tells you which value is > the default. > > A > Aaron Bertrand [SQL Server MVP] wrote:
> > Right. However, how do you establish a default value when the primary I like identity columns for obvious reasons - mostly because they> > key on which it is based is an identity column? > > Well, who says you have to use an IDENTITY column? This doesn't sound like > something that grows infinitely, but rather a defined and finite set. > > So, set the values up manually. 0 = none, 1 = jr., 2 = sr., etc. When you > need to add a suffix to the available list, which should happen rarely, > instead of just blindly inserting the value, you take the current max(id) > and add 1. handle the key generation for me. Also, and please correct me if I'm wrong here, I've always thought they served to minimize concurrency issues where new records are concerned. > You can also add a column called [default] which tells you which value is Hmm. That's a couple of people now who have suggested adding a column> the default. just to denote default status for one row. Is that consistent with the basic rules of normalization? > I like identity columns for obvious reasons - mostly because they Well, you seem to be waffling between caring what the value is (not a good > handle the key generation for me. Also, and please correct me if I'm > wrong here, I've always thought they served to minimize concurrency > issues where new records are concerned. property of identity) and doing less work (a good property of identity). So, it really is up to you. > Hmm. That's a couple of people now who have suggested adding a column The other way you could look at it is, you are denoting non-default status > just to denote default status for one row. Is that consistent with the > basic rules of normalization? for all but one row. I try to make a design that makes sense, is easy to work with, and is scalable. I can't say that any of my top ten priorities in any project I've ever worked on have included what form of some textbook definition of normalization I am complying with. A Aaron Bertrand [SQL Server MVP] wrote:
Show quote > > I like identity columns for obvious reasons - mostly because they Thanks. That sounds like very practical advice.> > handle the key generation for me. Also, and please correct me if I'm > > wrong here, I've always thought they served to minimize concurrency > > issues where new records are concerned. > > Well, you seem to be waffling between caring what the value is (not a good > property of identity) and doing less work (a good property of identity). > So, it really is up to you. > > > Hmm. That's a couple of people now who have suggested adding a column > > just to denote default status for one row. Is that consistent with the > > basic rules of normalization? > > The other way you could look at it is, you are denoting non-default status > for all but one row. > > I try to make a design that makes sense, is easy to work with, and is > scalable. I can't say that any of my top ten priorities in any project I've > ever worked on have included what form of some textbook definition of > normalization I am complying with. >
Show quote
"Richard Carpenter" <rumble***@hotmail.com> wrote in message If you add some keys to the CustomerTypes table you will be able to identify news:1151594340.887702.165300@b68g2000cwa.googlegroups.com... > Considering the following: > > CREATE TABLE CustomerTypes > ( > PKCustomerType INT IDENTITY (1,1) NOT NULL, > CustomerTypeDesc VARCHAR(30) > ) > GO > > INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail') > INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale') > INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate') > > CREATE TABLE Customers > ( > PKCustomer INT IDENTITY (1,1) NOT NULL, > CustomerID VARCHAR(20), > FKCustomerType INT > ) > GO > > INSERT Customers (CustomerID, FKCustomerType) Values ('CUST001', 1) > INSERT Customers Values ('CUST002', 3) > INSERT Customers Values ('CUST003', 2) > > How should I handle a case where I don't want to specify a customer > type for whatever reason? Perhaps it is unknown or not applicable in > some cases. I could add a 'None or N/A' record in the CustomerTypes > table for that purpose, but when validating the data in an INSERT > stored procedure, for example, I would like to have a value to use as > default in the case of a missing of invalid field value being passed > for FKCustomerType. If it weren't an identity field in the > CustomerTypes table, I could just create a CustomerType record with a > PKCustomerType value of 0 and always use that value in such cases. With > it being an identity field, however, I can't count on a specific value > for that purpose. It seems I have two options: > > 1) Create the CustomerTypes table without PKCustomerType being an > identity field...insert the 'None or N/A' record, assigning it a > PKCustomerType value of, say, 0...alter the table to make > PKCustomerType and identity field (1,1) > > 2) Merely use NULL in Customers where CustomerType isn't specified. > > Is there a better way? If not, any insight on why one approach would be > better than the other? I'm leaning toward option 2 at this point. > the "Unknown" value by its logical key instead of the surrogate. The "Inapplicable" case would probably be better handled by decomposing the Customers table. CREATE TABLE CustomerTypes ( PKCustomerType INT IDENTITY (1,1) NOT NULL CONSTRAINT PK_CustomerTypes PRIMARY KEY, CustomerTypeDesc VARCHAR(30) NOT NULL CONSTRAINT AK1_CustomerTypes UNIQUE ); GO INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail'); INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale'); INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Corporate'); INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Unknown'); -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx --  |
|||||||||||||||||||||||
Other interesting topics