|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
What's a good SQL server naming conventionHi folks,
I wonder if any of you in all your wisdom happen to know the URL of a published web article on a really decent SQL server naming convention I've obviously googled it, but have come across nothing that has particularly grabbed me as thorough or especially coherent. Regards, and thanks in advance, CharlesA Hi Charles,
Try this link http://labmice.techtarget.com/articles/computernaming.htm My advice would be to atleast have the 'tla' of SQL within the server name, but i guess it really depends on how many servers you have within your organisation too. Also remember that SQL has the ability to support instances which will also require an additional naming convention (sorry to add more pain :) ). Because I administer over 400 SQL servers, I generally use a location/countrycode + SQL + dept code (e.g. HR) but not in all cases. HTH Immy Show quote "CharlesA" <Charl***@discussions.microsoft.com> wrote in message news:B6A5CD91-FAAA-400D-AB29-F2A4887FCBFD@microsoft.com... > Hi folks, > I wonder if any of you in all your wisdom happen to know the URL of a > published web article on a really decent SQL server naming convention > > I've obviously googled it, but have come across nothing that has > particularly grabbed me as thorough or especially coherent. > > Regards, and thanks in advance, > CharlesA Immy,
Thanks for the reply, however, I must have made myself unclear I don't mean the naming of the SQL Server itself (or the machine it's on) I mean a naming convetion for the objects inside a database on the sql server...tables, views, sprocs variale names etc Thanks and regards CharlesA As far as methods (procedures, functions, etc.) go, I find the
Entity-Action-Mode rule very intuitive. E.g.: Customers_Insert Customers_Select_byLastName .... ML --- http://milambda.blogspot.com/ markc***@hotmail.com wrote:
I've read that page practically every time someone posts it. There are some niggles I have with it... 1) using prefixes for grouping, I'd disagree with. I've generally used schemas (well, owners under 2000) to perform my logical grouping. 2) My own preference for constraints (NB, this is based on the concept that quickly finding the constraint is what you value, rather than what the constraint is all about): PK_<table name> - there's only one PK in the table. FK_<from table>_<to table> - if we're going to the PK of the to table, and the column(s) in the from table have the same names, nothing more to add FK_<from table>_<to table>_By<from column purpose> - generally used when the from table contains more than one FK to the same target table. FK_<from table>_<to table>_By<from column purpose>_For<to column purpose> - when going to another key in the to table (e.g. a unique constraint) DF_<table name>_<column name> - default constraint UQ_<table name>_<column name>s - for a uniqueness constraint on a single column UQ_<table name>_<uniqueness purpose> - for a multi-column uniqueness constraint CK_<table name>_<column name>_<purpose> - Check constraint on a single column CK_<table name>_<purpose> - Check constraint on multiple columns IX_<table name>_<column name> - for single column indexes IX_<table name>_<purpose> - for multi column indexes. all of these can include (between prefix and the first table name) the schema name and a further prefix. Although not necessary, this can be very beneficial if you have tables in more than one schema with the same name. Essentially, if you have two identically named constraints, DMO tends to get confused when asked to script a table (under 2000, at least). Some would argue against using prefixes. But under the 2000 tools, there's no visual distinction between different types of constraints, so being able to spot the correct type quicklk helps. Having the table name second means that any error messages, no matter how truncated, will at least lead you to the correct table. If the error message isn't truncated, you know which areas of the troublesome data to investigate first. Although having column names in e.g. FK names may be of value to some, the general case of FK to foreign table PK, using foreign table PK column names in FK table, then you'll already know everything you need to. Let the flamage commence... Damien I hate to do this but...
SQL Programming Style by Joe Celko. The one caviat is that Joe is a SQL Standards guru who always advocates following ANSI standards and avoiding implementation-specific extensions. So, anything that is specific to SQL Server will get short shrift from him. Having said that, the book has some very good tips on naming conventions as well as other elements of SQL Style. I would read it and take what seems to work for you, but don't feel you have to adopt everything Joe says. -- Show quoteGeoff N. Hiten Senior Database Administrator Microsoft SQL Server MVP "CharlesA" <Charl***@discussions.microsoft.com> wrote in message news:B6A5CD91-FAAA-400D-AB29-F2A4887FCBFD@microsoft.com... > Hi folks, > I wonder if any of you in all your wisdom happen to know the URL of a > published web article on a really decent SQL server naming convention > > I've obviously googled it, but have come across nothing that has > particularly grabbed me as thorough or especially coherent. > > Regards, and thanks in advance, > CharlesA Get a copy of SQL PROGRAMMING STYLE. I give a set of conventions based
on ISO-11179 standards and readability studies that we did back in the 1970-1980's when Software Engineering was the hot topic du jour. The basic idea of ISO-11179 that you name a data element for what it is in the data model. It never changes names from table to table. You never name it for the location *where* it is used, so those silly, redundant "tbl_" prefixes are out. SQL only has one data structure in the first place. You never name it for how it is used, so those " PK_", "VW_" etc. prefixes are out. Unless "VW_" means Volkswagen and not VIEW. This kind of crappy naming comes from not knowing that you never mix data and metadata in a schema. You never have a column is too vague to stand alone. There is no magical, universal "id" -- it begs the question as to what the heck does it identify?!! If you are simply numbering the rows with IDENTITY, then you do not have a Relational Model at all; you are mimicking a sequential magnetic tape file in SQL. Likewise "date" , "type", "code" are too vague. This is only your interpretation of iso-11179.
Out in the real world you need to consider how you implement such a standard naming convention, it needs to assist the developer and support engineer in order to reduce maintanance and development costs. I agree with table names, usually its best not to prefix tbl_ or t etc... but its often necessary to prefix them with the component of the system being modeled if that will assit on a large product that may have 100's of tables. Also, I consider it good practice to prefix constraints pk_ (primary key), uq_ or uk_ (unique key), sk_ (surrogate key); this helps debug problems quicker. Also, prefixing views with vw_ is also good practice because it helps the developer or maintainer know that the object they are using in the FROM clause isn't a base table and contains additional logic and filtering - critical when doing performance problem diagnosis. The amount of time I have wasted going to clients who have performance problems and then having to extract and work out the 'real' query being executed, i suppose it gives me more work (that i would rather not have to do). Using the column name 'id' is not a problem either, it is always contained within the object being modelled, you should always use table aliases on queries especially ones involving multiple tables - it takes the guess work out of what column comes from where, another must when doing performance tuning / problem diagnosis. create table mailing_list ( id int not null identity constraint sk_mailing_list unique clustered, name varchar(50) not null constraint pk_mailing_list primary key nonclustered ) Above you can clearly see that id is mailing_list.id and name is mailing_list.name; this makes for a clear uniform schema naming and allows the DBA or developer to easily create any maintanence scripts. I would suggest you get out and do some real development in a real company, you should be a junior in the team and you will be able to learn real world development skills from problems us developers face day to day. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1137264524.173507.61020@z14g2000cwz.googlegroups.com... > Get a copy of SQL PROGRAMMING STYLE. I give a set of conventions based > on ISO-11179 standards and readability studies that we did back in the > 1970-1980's when Software Engineering was the hot topic du jour. > > The basic idea of ISO-11179 that you name a data element for what it is > in the data model. It never changes names from table to table. > > You never name it for the location *where* it is used, so those silly, > redundant "tbl_" prefixes are out. SQL only has one data structure in > the first place. > > You never name it for how it is used, so those " PK_", "VW_" etc. > prefixes are out. Unless "VW_" means Volkswagen and not VIEW. > > This kind of crappy naming comes from not knowing that you never mix > data and metadata in a schema. > > You never have a column is too vague to stand alone. There is no > magical, universal "id" -- it begs the question as to what the heck > does it identify?!! If you are simply numbering the rows with > IDENTITY, then you do not have a Relational Model at all; you are > mimicking a sequential magnetic tape file in SQL. > > Likewise "date" , "type", "code" are too vague. > >> Also, I consider it good practice to prefix constraints pk_ (primary key), uq_ or uk_ (unique key), sk_ (surrogate key); this helps debugproblems quicker << Well, once more Tony is smarter than all of ISO, and every book on data modeling. You still do not understand logical models, physical models and implementation issues. >> Using the column name 'id' is not a problem either, it is always contained within the object being modelled ..<<Wrong again. You start with a data dictionary. Each data element is defined, its validation and verification rules are given and it gets a unique name. Building tables comes later. This is why a data dictionary can be shared across RDBMS projects and can be used to generate DDL. So the magical "id" in one table is the same data element everywhere it appeasr in Tony's World or what? Should it be "pk_id" in several tables and perhaps "fk_id" in other tables? >> I would suggest you get out and do some real development in a real company, you should be a junior in the team and you will be able tolearn real world development skills from problems us developers face day to day. << LOL! How many decades of IT work have you done? 10? 20? 30? 30+? You will probably be a developer all your career, since you seem unable to advance to abstract concepts like RDBMS and data mdeling, learn standards, or do research. You just put out code as fast as you can to get the boss off your back. You write SQL as if it were an OO language! --CELKO-- Please post DDL in a human-readable format and not a machine-generated one. This way people do not have to guess what the keys, constraints, DRI, datatypes, etc. in your schema are. Sample data is also a good idea, along with clear specifications. *** Sent via Developersdex http://www.developersdex.com *** > Well, once more Tony is smarter than all of ISO, and every book on data I just live in the real world dealing with the real problems of development > modeling. You still do not understand logical models, physical models > and implementation issues. and implementation and managability of a system including a database. Just like the ANSI SQL the ISO standard has its failures, not as many as ANSI SQL granted, but lets face it - your rantings are just your interpretation of the standard. > Wrong again. You start with a data dictionary. Each data element is Yes, you do start with a data dictionary, but then you have to implement it! > defined, its validation and verification rules are given and it gets a > unique name. Building tables comes later. This is why a data > dictionary can be shared across RDBMS projects and can be used to > generate DDL. This is where your lack of real grass roots development lets you down, you keep missing the implementation phase of the database design and try and directly implement the logical model. > So the magical "id" in one table is the same data element everywhere it Do not put words in my mouth to try and bolster your argument.> appeasr in Tony's World or what? Should it be "pk_id" in several tables > and perhaps "fk_id" in other tables? Perhaps if you read down and looked at the example I gave it would be more clear to you, I'll repeat it here... create table mailing_list ( id int not null identity constraint sk_mailing_list unique clustered, name varchar(50) not null constraint pk_mailing_list primary key nonclustered ) For foreign keys it would be fk_[originating table]_[foreign table]. > LOL! How many decades of IT work have you done? 10? 20? 30? 30+? You I'm currently in my 20th year as a developer, the past 7 year have been more > will probably be a developer all your career, since you seem unable to > advance to abstract concepts like RDBMS and data mdeling, learn > standards, or do research. You just put out code as fast as you can to > get the boss off your back. You write SQL as if it were an OO language! as a consultant role in which i am doing really well just for info. I severed an apprenticeship and also did the theory, I didn't just learn what I know in the class room and from books and other peoples posts - i went out and problem solved, learn't off other programmers and consultants. I do research at the point I need it so that I am up-to-date with the current thinking and whats coming along, unlike yourself, I don't have a standard set of posts that haven't changed in 6 years. Talking research, its about time you got a real programming job instead of continually 'doing research', experience and knowledge is gained through working challenges and not through reading books and forums, your answers are poor, your attitude is dam right unprofessional and arrogant and quite frankly it gives IT a bad name. SQL should model more like OO and then it would be a dam site easier to solve business problems, perhaps then we wouldn't have all these properitary features you hate so much. The world does not work in sets, it works in objects! An individual is an object and not a set! I write good simple and maintainable, oh, scalable too code that can be easily picked up by junior coders; I must be doing something right because a good proportion of my client based is existing clients and repeat work with relationships going back a number of years - can you say then same? Doubt it with your attitude and approach, i bet they can't wait to get you out the door, that is if anybody will hire you in the first place! Show quote "--CELKO--" <remove.jcelko***@earthlink.net> wrote in message news:ucbQcrgGGHA.1100@TK2MSFTNGP10.phx.gbl... >>> Also, I consider it good practice to prefix constraints pk_ (primary > key), uq_ or uk_ (unique key), sk_ (surrogate key); this helps debug > problems quicker << > > Well, once more Tony is smarter than all of ISO, and every book on data > modeling. You still do not understand logical models, physical models > and implementation issues. > >>> Using the column name 'id' is not a problem either, it is always > contained within the object being modelled ..<< > > Wrong again. You start with a data dictionary. Each data element is > defined, its validation and verification rules are given and it gets a > unique name. Building tables comes later. This is why a data > dictionary can be shared across RDBMS projects and can be used to > generate DDL. > > So the magical "id" in one table is the same data element everywhere it > appeasr in Tony's World or what? Should it be "pk_id" in several tables > and perhaps "fk_id" in other tables? > >>> I would suggest you get out and do some real development in a real > company, you should be a junior in the team and you will be able to > learn real world development skills from problems us developers face day > to day. << > > LOL! How many decades of IT work have you done? 10? 20? 30? 30+? You > will probably be a developer all your career, since you seem unable to > advance to abstract concepts like RDBMS and data mdeling, learn > standards, or do research. You just put out code as fast as you can to > get the boss off your back. You write SQL as if it were an OO language! > > --CELKO-- > Please post DDL in a human-readable format and not a machine-generated > one. This way people do not have to guess what the keys, constraints, > DRI, datatypes, etc. in your schema are. Sample data is also a good > idea, along with clear specifications. > > > *** Sent via Developersdex http://www.developersdex.com *** While I must say the Rogerson/Celko bouts are entertaining to read, I
don't think the SQL newsgroups are the appropriate forum for these personal flame wars. Maybe you guys should take this "offline" - go have a beer together and settle your differences like men. Or maybe you should write a book together; Joe can handle the logical design half and Tony can do the physical implementation half (sounds like a match made in Heaven...or not). Show quote >>Well, once more Tony is smarter than all of ISO, and every book on data >>modeling. You still do not understand logical models, physical models >>and implementation issues. >> >> > >I just live in the real world dealing with the real problems of development >and implementation and managability of a system including a database. > >Just like the ANSI SQL the ISO standard has its failures, not as many as >ANSI SQL granted, but lets face it - your rantings are just your >interpretation of the standard. > > > >>Wrong again. You start with a data dictionary. Each data element is >>defined, its validation and verification rules are given and it gets a >>unique name. Building tables comes later. This is why a data >>dictionary can be shared across RDBMS projects and can be used to >>generate DDL. >> >> > >Yes, you do start with a data dictionary, but then you have to implement it! >This is where your lack of real grass roots development lets you down, you >keep missing the implementation phase of the database design and try and >directly implement the logical model. > > > >>So the magical "id" in one table is the same data element everywhere it >>appeasr in Tony's World or what? Should it be "pk_id" in several tables >>and perhaps "fk_id" in other tables? >> >> > >Do not put words in my mouth to try and bolster your argument. > >Perhaps if you read down and looked at the example I gave it would be more >clear to you, I'll repeat it here... > >create table mailing_list ( > id int not null identity constraint sk_mailing_list unique clustered, > > name varchar(50) not null constraint pk_mailing_list primary key >nonclustered >) > >For foreign keys it would be fk_[originating table]_[foreign table]. > > > >>LOL! How many decades of IT work have you done? 10? 20? 30? 30+? You >>will probably be a developer all your career, since you seem unable to >>advance to abstract concepts like RDBMS and data mdeling, learn >>standards, or do research. You just put out code as fast as you can to >>get the boss off your back. You write SQL as if it were an OO language! >> >> > >I'm currently in my 20th year as a developer, the past 7 year have been more >as a consultant role in which i am doing really well just for info. > >I severed an apprenticeship and also did the theory, I didn't just learn >what I know in the class room and from books and other peoples posts - i >went out and problem solved, learn't off other programmers and consultants. > >I do research at the point I need it so that I am up-to-date with the >current thinking and whats coming along, unlike yourself, I don't have a >standard set of posts that haven't changed in 6 years. Talking research, its >about time you got a real programming job instead of continually 'doing >research', experience and knowledge is gained through working challenges and >not through reading books and forums, your answers are poor, your attitude >is dam right unprofessional and arrogant and quite frankly it gives IT a bad >name. > >SQL should model more like OO and then it would be a dam site easier to >solve business problems, perhaps then we wouldn't have all these properitary >features you hate so much. The world does not work in sets, it works in >objects! An individual is an object and not a set! > >I write good simple and maintainable, oh, scalable too code that can be >easily picked up by junior coders; I must be doing something right because a >good proportion of my client based is existing clients and repeat work with >relationships going back a number of years - can you say then same? Doubt it >with your attitude and approach, i bet they can't wait to get you out the >door, that is if anybody will hire you in the first place! > > > Mike Hodgson skrev:
> While I must say the Rogerson/Celko bouts are entertaining to read, I My thoughts exactly, Mike.> don't think the SQL newsgroups are the appropriate forum for these > personal flame wars. Maybe you guys should take this "offline" - go > have a beer together and settle your differences like men. Or maybe you > should write a book together; Joe can handle the logical design half and > Tony can do the physical implementation half (sounds like a match made > in Heaven...or not). > > -- > *mike hodgson* > blog: http://sqlnerd.blogspot.com > Must say CELKO's experience is amazing though, imagine asking a thing like: > How many decades of IT work have you done? 10? 20? 30? 30+? Geez, the man's a vampire or something, 30+ decades!!! Makes my pityful15 years in IT seem like nothing basically :) /impslayer, aka Birger Johansson Hi Birger,
Really, with celko enough is enough, i've been using this news group for around 6 years now and finally a few months ago I snapped, also, looks like a few others have had enough too. I'm just sick and tired at the way this guy behaves, especially with juniors/beginners with simple SQL problems. As for experience, although I have 19+ years experience now I only consider the first 10 years as foundation work; the other 9 are only really relevant to many anymore; things move just too quickly. I know people with only 5 years that run rings round me on parts of the SQL product now, mind you it is a really big product; and when push comes to shove, its what you've done in those 15, 19, 30+ years - writing books doesn't make you an expert; only using it out in the field does. Lastly, if you don't like my posts - don't read them; you have a choice - a new poster doesn't have a choice and will read celko's, imagine how you would feel if somebody called you a crap programmer and go back and learn fundementals. Show quote "impslayer" <impsla***@hotmail.com> wrote in message news:1137397904.999002.257750@g49g2000cwa.googlegroups.com... > > Mike Hodgson skrev: > >> While I must say the Rogerson/Celko bouts are entertaining to read, I >> don't think the SQL newsgroups are the appropriate forum for these >> personal flame wars. Maybe you guys should take this "offline" - go >> have a beer together and settle your differences like men. Or maybe you >> should write a book together; Joe can handle the logical design half and >> Tony can do the physical implementation half (sounds like a match made >> in Heaven...or not). >> >> -- >> *mike hodgson* >> blog: http://sqlnerd.blogspot.com >> > > My thoughts exactly, Mike. > > Must say CELKO's experience is amazing though, imagine asking a thing > like: > >> How many decades of IT work have you done? 10? 20? 30? 30+? > > Geez, the man's a vampire or something, 30+ decades!!! Makes my pityful > 15 years in IT seem like nothing basically :) > > /impslayer, aka Birger Johansson > Tony Rogerson skrev:
Show quote > Hi Birger, Tony,> > Really, with celko enough is enough, i've been using this news group for > around 6 years now and finally a few months ago I snapped, also, looks like > a few others have had enough too. > > I'm just sick and tired at the way this guy behaves, especially with > juniors/beginners with simple SQL problems. > > As for experience, although I have 19+ years experience now I only consider > the first 10 years as foundation work; the other 9 are only really relevant > to many anymore; things move just too quickly. I know people with only 5 > years that run rings round me on parts of the SQL product now, mind you it > is a really big product; and when push comes to shove, its what you've done > in those 15, 19, 30+ years - writing books doesn't make you an expert; only > using it out in the field does. > > Lastly, if you don't like my posts - don't read them; you have a choice - a > new poster doesn't have a choice and will read celko's, imagine how you > would feel if somebody called you a crap programmer and go back and learn > fundementals. I'll definitely keep on reading your posts, just hoping that you don't get completely stuck in that usenet 'brawl'. About the experience, it was 30+ DECADES I was impressed by, or amused rather... /impslayer, aka Birger Johansson Tony Rogerson wrote:
>Really, with celko enough is enough, i've been using this news group for I did notice the subtle change in your posts recently. ;)>around 6 years now and finally a few months ago I snapped, also, looks like >a few others have had enough too. > > (I have been participating in the newsgroups for some time, mostly reading threads for a few years but answering questions in the last year or so too.) >I'm just sick and tired at the way this guy behaves, especially with I know Joe can be fairly "in your face", and defending the new-comers to >juniors/beginners with simple SQL problems. > > SQL & these newsgroups is one thing, but when you're the target you should be the bigger man and rise above the "I'm better than you" name calling (just justify your opinion and move on). You're both adults and in the top of your respective fields. Everyone respects that and values your opinions; just don't start acting like children. >As for experience, although I have 19+ years experience now I only consider I know how you feel. While I don't have quite the experience you have, >the first 10 years as foundation work; the other 9 are only really relevant >to many anymore; things move just too quickly. I know people with only 5 >years that run rings round me on parts of the SQL product now, mind you it >is a really big product; and when push comes to shove, its what you've done >in those 15, 19, 30+ years - writing books doesn't make you an expert; only >using it out in the field does. > > I have clocked up my fair share of hours (about 14 years full-time in software development - the last 11 in SQL, dev initially then DBA) and having a large depth of knowledge in an area or product is a great foundation for understanding new technology but it doesn't make you an expert in that technology until you study it & work with it. (I'm humbled when someone who's been working with SQL for only a year or 2 shows me something I didn't already know or comes up with some solution that I hadn't considered before - but it brings me back down to reality.) While I'm not saying Joe is not an expert - he's one of the foremost authorities on relational theory today - I do understand your view point that theory is one thing and it's entirely different to solving real-world problems by getting your hands dirty in the technology/code. The solutions to real-world issues should be heavily influenced by the theory, but the theory needs to be flexible to adapt to real-world situations that don't fit perfectly into the idealistic world the theory is built from (isn't that roughly how theoretical physics works?). >Lastly, if you don't like my posts - don't read them; you have a choice - a Don't get me wrong, I respect your opinions (as I do Joe's) and will keep reading your posts because I find they're of great professional value (as are all the regular experts like Kalen, Itzik, Adam, Aaron, Andrew, Tibor, Erland, Uri, Hugo, etc.). I just prefer reading the posts with SQL content (the old posts) rather than personal insults (the new posts).>new poster doesn't have a choice and will read celko's, imagine how you >would feel if somebody called you a crap programmer and go back and learn >fundementals. > > That's my 2c. Hi Mike,
It just isn't going to happen, so long as he has the rude and condesending attitude to many posting on here then I will continue to highlight his deficiencies. I've come across a few too many people like him in the industry to let this one go, it gives us all a bad name; there is absolutely no need for the attitude he has. It certainly puts people off coming back and posting again, i've had many emails on this matter. Tony. Show quote "Mike Hodgson" <mike.hodgson@mallesons.nospam.com> wrote in message news:O7oYUWlGGHA.1180@TK2MSFTNGP09.phx.gbl... > While I must say the Rogerson/Celko bouts are entertaining to read, I > don't think the SQL newsgroups are the appropriate forum for these > personal flame wars. Maybe you guys should take this "offline" - go > have a beer together and settle your differences like men. Or maybe you > should write a book together; Joe can handle the logical design half and > Tony can do the physical implementation half (sounds like a match made > in Heaven...or not). > > -- > *mike hodgson* > blog: http://sqlnerd.blogspot.com > > > > Tony Rogerson wrote: > >>>Well, once more Tony is smarter than all of ISO, and every book on data >>>modeling. You still do not understand logical models, physical models >>>and implementation issues. >>> >>> >> >>I just live in the real world dealing with the real problems of >>development >>and implementation and managability of a system including a database. >> >>Just like the ANSI SQL the ISO standard has its failures, not as many as >>ANSI SQL granted, but lets face it - your rantings are just your >>interpretation of the standard. >> >> >> >>>Wrong again. You start with a data dictionary. Each data element is >>>defined, its validation and verification rules are given and it gets a >>>unique name. Building tables comes later. This is why a data >>>dictionary can be shared across RDBMS projects and can be used to >>>generate DDL. >>> >>> >> >>Yes, you do start with a data dictionary, but then you have to implement >>it! >>This is where your lack of real grass roots development lets you down, you >>keep missing the implementation phase of the database design and try and >>directly implement the logical model. >> >> >> >>>So the magical "id" in one table is the same data element everywhere it >>>appeasr in Tony's World or what? Should it be "pk_id" in several tables >>>and perhaps "fk_id" in other tables? >>> >>> >> >>Do not put words in my mouth to try and bolster your argument. >> >>Perhaps if you read down and looked at the example I gave it would be more >>clear to you, I'll repeat it here... >> >>create table mailing_list ( >> id int not null identity constraint sk_mailing_list unique >> clustered, >> >> name varchar(50) not null constraint pk_mailing_list primary key >>nonclustered >>) >> >>For foreign keys it would be fk_[originating table]_[foreign table]. >> >> >> >>>LOL! How many decades of IT work have you done? 10? 20? 30? 30+? You >>>will probably be a developer all your career, since you seem unable to >>>advance to abstract concepts like RDBMS and data mdeling, learn >>>standards, or do research. You just put out code as fast as you can to >>>get the boss off your back. You write SQL as if it were an OO language! >>> >>> >> >>I'm currently in my 20th year as a developer, the past 7 year have been >>more >>as a consultant role in which i am doing really well just for info. >> >>I severed an apprenticeship and also did the theory, I didn't just learn >>what I know in the class room and from books and other peoples posts - i >>went out and problem solved, learn't off other programmers and >>consultants. >> >>I do research at the point I need it so that I am up-to-date with the >>current thinking and whats coming along, unlike yourself, I don't have a >>standard set of posts that haven't changed in 6 years. Talking research, >>its >>about time you got a real programming job instead of continually 'doing >>research', experience and knowledge is gained through working challenges >>and >>not through reading books and forums, your answers are poor, your attitude >>is dam right unprofessional and arrogant and quite frankly it gives IT a >>bad >>name. >> >>SQL should model more like OO and then it would be a dam site easier to >>solve business problems, perhaps then we wouldn't have all these >>properitary >>features you hate so much. The world does not work in sets, it works in >>objects! An individual is an object and not a set! >> >>I write good simple and maintainable, oh, scalable too code that can be >>easily picked up by junior coders; I must be doing something right because >>a >>good proportion of my client based is existing clients and repeat work >>with >>relationships going back a number of years - can you say then same? Doubt >>it >>with your attitude and approach, i bet they can't wait to get you out the >>door, that is if anybody will hire you in the first place! >> >> >> > Hi Tony,
I've read this thread very thorough and I totally agree with your statements concerning the practice in development. To be a developer in practice is quite more different from the perspective of technocrats. I think some people are so high in their ivory tower that they will not be able to apprehend the reality. >it gives us all a bad name; there is absolutely no need for the attitude he Just my 2 cent...>has. ACK!!! -- Gruß, Uwe Ricken MCP for SQL Server 2000 Database Implementation GNS GmbH, Frankfurt am Main http://www.gns-online.de http://www.memberadmin.de http://www.conferenceadmin.de ____________________________________________________ dbdev: http://www.dbdev.org APP: http://www.AccessProfiPool.de FAQ: http://www.donkarl.com/AccessFAQ.htm >> SQL should model more like OO and then it would be a dam site easier to solve business problems, perhaps then we wouldn't have all these properitary features you hate so much. << Many years ago, the INCITS H2 Database Standards Committee(nee ANSIX3H2 Database Standards Committee) had a meeting in Rapid City, South Dakota. We had Mount Rushmore and Bjarne Stroustrup as special attractions. Mr. Stroustrup did his slide show about Bell Labs inventing C++ and OO programming for us and we got to ask questions. One of the questions was how we should put OO stuff into SQL. His answer was that Bells Labs, with all their talent, had tried four different approaches to this problem and come the conclusion that you should not do it. OO was great for programming but deadly for data. I have watched people try to force OO models into SQL and it falls apart in about a year. Every typo becomes a new attribute or class, queries that would have been so easy in a relational model are now multi-table monster outer joins, redundancy grows at an exponential rates, constraints are virtually impossible to write so you can kiss data integrity goodbye, etc. Do you feel that Stroustrup has no real knowledge of OO concepts and implementations? Or that you are smarter than Bell labs in its glory days? >> The world does not work in sets, it works in objects! An individual is an object and not a set! << The idea of RM is to model and handle data as a set and not asindividual data elements. What you are talking about sounds like good old sequential file processing. Or when you want a report, do invoke a method in each individual customer account object, one at a time, to get their billing amount? Set processing sounds a lot faster and easier to code to me. I think you said it yourself, this was 'many years ago', a siginificant
amount has changed since then. Technology is better, there are better build languages to create such things now. > Do you feel that Stroustrup has no real knowledge of OO concepts and Did I state that or even suggest it - no; again, stop chaning/mis quoting me > implementations? Or that you are smarter than Bell labs in its glory > days? to suite your argument, its a bad habbit of yours. It still does not change the fact that the real world deals in objects and SQL is a set based language, the two are difficult to interface, i think in your ramble you state that yourself. I do both SQL and OO programming using .NET so I can see the argument from both sides, and yes, as a developer and database designer I've faced the problem on many occaisions. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1137434753.868045.8550@g43g2000cwa.googlegroups.com... >>> SQL should model more like OO and then it would be a dam site easier to >>> solve business problems, perhaps then we wouldn't have all these >>> properitary features you hate so much. << > > Many years ago, the INCITS H2 Database Standards Committee(nee ANSI > X3H2 Database Standards Committee) had a meeting in Rapid City, South > Dakota. We had Mount Rushmore and Bjarne Stroustrup as special > attractions. Mr. Stroustrup did his slide show about Bell Labs > inventing C++ and OO programming for us and we got to ask questions. > > One of the questions was how we should put OO stuff into SQL. His > answer was that Bells Labs, with all their talent, had tried four > different approaches to this problem and come the conclusion that you > should not do it. OO was great for programming but deadly for data. > > I have watched people try to force OO models into SQL and it falls > apart in about a year. Every typo becomes a new attribute or class, > queries that would have been so easy in a relational model are now > multi-table monster outer joins, redundancy grows at an exponential > rates, constraints are virtually impossible to write so you can kiss > data integrity goodbye, etc. > > Do you feel that Stroustrup has no real knowledge of OO concepts and > implementations? Or that you are smarter than Bell labs in its glory > days? > >>> The world does not work in sets, it works in objects! An individual is >>> an object and not a set! << > > The idea of RM is to model and handle data as a set and not as > individual data elements. What you are talking about sounds like good > old sequential file processing. > > Or when you want a report, do invoke a method in each individual > customer account object, one at a time, to get their billing amount? > Set processing sounds a lot faster and easier to code to me. > Tony Rogerson wrote: [snip]
Show quote > Using the column name 'id' is not a problem either, it is always contained Personally, I don't like columns named "id". In your example I would> within the object being modelled, you should always use table aliases on > queries especially ones involving multiple tables - it takes the guess work > out of what column comes from where, another must when doing performance > tuning / problem diagnosis. > > create table mailing_list ( > id int not null identity constraint sk_mailing_list unique clustered, > > name varchar(50) not null constraint pk_mailing_list primary key > nonclustered > ) > > Above you can clearly see that id is mailing_list.id and name is > mailing_list.name; this makes for a clear uniform schema naming and allows > the DBA or developer to easily create any maintanence scripts. [snip] prefer to use MailingListID or something like that. This way, if I use the same column in a different table, and it has the same meaning, I can use the same name. Likewise, if the column has the same distinct name in two tables, I can be sure it means the same thing. So in my database, a foreign key would be between two columns with the same name. In your database, aforeign keys would be between two columns with a different name (with the column of the referenced table being called "id"). I think it is easier to make mistakes if you "reuse" a column name like id. Also, in the referencing table, you would still have to come up with a name for the foreign key column(s). You suggest the use of table aliases / full qualification. That will work fine for individual queries. But what do you do in views definitions when you want to include the "id" columns of two joined tables? Another question: how would you name the column "???" in the following table? (note that this table has a compound primary key, and column ??? is supposed to contain an index number which will uniquely number each row with the same id) CREATE TABLE MailingListItems (id int not null CONSTRAINT FK_MailingListItems_MailingList REFERENCES mailing_list(id) ,??? smallint not null ,... ,CONSTRAINT PK_MailingListItems PRIMARY KEY (id, ???) ) Or would you not name the first column "id" in the example above? Gert-Jan Hi Gert-Jan,
Some examples of how I would code this... create table mailing_list ( id int not null identity constraint sk_mailing_list unique clustered, name varchar(50) not null constraint pk_mailing_list primary key nonclustered ) create table xr_mailing_list_individual ( individual_id int not null references individual ( id ), mailing_list_id int not null references mailing_list ( id ) ) In a view definition select mailing_list_id = ml.id from xr_mailing_list_individual xr inner join mailing_list ml on ml.id = xr.mailing_list_id (I know I could use xr.mailing_list_id and probably would code it that way, but the above explains how i use it). In procs... create proc mailing_list_ins @mailing_list_name varchar(50) as begin insert mailing_list ( name ) values( @mailing_list_name ) select mailing_list_id = scope_identity() end I used to code multi case identifiers (and still do in non sql programming, like vb.net), but for SQL i always use lower case for identifiers and upper case for statements (i was being lazy above), basically, that helps any problems with collation and case sensitivity. Neither of us are wrong, its a question of styles. Show quote "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message news:43CC021B.E5C3EA31@toomuchspamalready.nl... > Tony Rogerson wrote: [snip] >> Using the column name 'id' is not a problem either, it is always >> contained >> within the object being modelled, you should always use table aliases on >> queries especially ones involving multiple tables - it takes the guess >> work >> out of what column comes from where, another must when doing performance >> tuning / problem diagnosis. >> >> create table mailing_list ( >> id int not null identity constraint sk_mailing_list unique >> clustered, >> >> name varchar(50) not null constraint pk_mailing_list primary key >> nonclustered >> ) >> >> Above you can clearly see that id is mailing_list.id and name is >> mailing_list.name; this makes for a clear uniform schema naming and >> allows >> the DBA or developer to easily create any maintanence scripts. [snip] > > Personally, I don't like columns named "id". In your example I would > prefer to use MailingListID or something like that. This way, if I use > the same column in a different table, and it has the same meaning, I can > use the same name. Likewise, if the column has the same distinct name in > two tables, I can be sure it means the same thing. > So in my database, a foreign key would be between two columns with the > same name. In your database, aforeign keys would be between two columns > with a different name (with the column of the referenced table being > called "id"). I think it is easier to make mistakes if you "reuse" a > column name like id. Also, in the referencing table, you would still > have to come up with a name for the foreign key column(s). > > You suggest the use of table aliases / full qualification. That will > work fine for individual queries. But what do you do in views > definitions when you want to include the "id" columns of two joined > tables? > > Another question: how would you name the column "???" in the following > table? (note that this table has a compound primary key, and column ??? > is supposed to contain an index number which will uniquely number each > row with the same id) > > CREATE TABLE MailingListItems > (id int not null CONSTRAINT FK_MailingListItems_MailingList > REFERENCES mailing_list(id) > ,??? smallint not null > ,... > ,CONSTRAINT PK_MailingListItems PRIMARY KEY (id, ???) > ) > > Or would you not name the first column "id" in the example above? > > Gert-Jan Tony,
I didn't realize I used different casing (and tend to avoid using underscores in column names and table names), and I agree we just have different styles on that one. This difference is not very important to me, although it can be confusing when it comes to the name of a foreign key constraint (would the referenced table in "fk_real_estate_order_part" be "order_part" or "part"?). Anyway, I see the logic in your naming convention (with respect to "id"), and the rule you use in the view definition. But it just makes my opinion stronger that it would be better to rename the id column in table mailing_list to mailing_list_id. It seems the only place where you do not use mailing_list_id is in the mailing_list table itself! IMO, the advantage of the brevity of "id" (i.e. less typing) doesn't pay off in the long run. I definitely like it more descriptive. A more descriptive identifier becomes useful even in a simple multi-table join. And much more useful in multi-table joins where the same table is joined multiple times and other tables are joined as well (in other words, when you start to get confused with all the aliases that are used). By the way, your table xr_mailing_list_individual does not have a Primary Key. I assume it is (individual_id,mailing_list_id) or (mailing_list_id,individual_id). Why do you use a prefix in the table name xr_mailing_list_individual? I mean, why do you use a prefix for any table's name? Would you use a prefix for a table that contains data and just one foreign key (such as a OrderLines table)? Just interested, Gert-Jan Tony Rogerson wrote: Show quote > > Hi Gert-Jan, > > Some examples of how I would code this... > > create table mailing_list ( > id int not null identity constraint sk_mailing_list unique clustered, > > name varchar(50) not null constraint pk_mailing_list primary key > nonclustered > ) > > create table xr_mailing_list_individual ( > individual_id int not null references individual ( id ), > mailing_list_id int not null references mailing_list ( id ) > ) > > In a view definition > > select mailing_list_id = ml.id > from xr_mailing_list_individual xr > inner join mailing_list ml on ml.id = xr.mailing_list_id > > (I know I could use xr.mailing_list_id and probably would code it that way, > but the above explains how i use it). > > In procs... > > create proc mailing_list_ins > @mailing_list_name varchar(50) > as > begin > insert mailing_list ( name ) values( @mailing_list_name ) > > select mailing_list_id = scope_identity() > > end > > I used to code multi case identifiers (and still do in non sql programming, > like vb.net), but for SQL i always use lower case for identifiers and upper > case for statements (i was being lazy above), basically, that helps any > problems with collation and case sensitivity. > > Neither of us are wrong, its a question of styles. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message > news:43CC021B.E5C3EA31@toomuchspamalready.nl... > > Tony Rogerson wrote: [snip] > >> Using the column name 'id' is not a problem either, it is always > >> contained > >> within the object being modelled, you should always use table aliases on > >> queries especially ones involving multiple tables - it takes the guess > >> work > >> out of what column comes from where, another must when doing performance > >> tuning / problem diagnosis. > >> > >> create table mailing_list ( > >> id int not null identity constraint sk_mailing_list unique > >> clustered, > >> > >> name varchar(50) not null constraint pk_mailing_list primary key > >> nonclustered > >> ) > >> > >> Above you can clearly see that id is mailing_list.id and name is > >> mailing_list.name; this makes for a clear uniform schema naming and > >> allows > >> the DBA or developer to easily create any maintanence scripts. [snip] > > > > Personally, I don't like columns named "id". In your example I would > > prefer to use MailingListID or something like that. This way, if I use > > the same column in a different table, and it has the same meaning, I can > > use the same name. Likewise, if the column has the same distinct name in > > two tables, I can be sure it means the same thing. > > So in my database, a foreign key would be between two columns with the > > same name. In your database, aforeign keys would be between two columns > > with a different name (with the column of the referenced table being > > called "id"). I think it is easier to make mistakes if you "reuse" a > > column name like id. Also, in the referencing table, you would still > > have to come up with a name for the foreign key column(s). > > > > You suggest the use of table aliases / full qualification. That will > > work fine for individual queries. But what do you do in views > > definitions when you want to include the "id" columns of two joined > > tables? > > > > Another question: how would you name the column "???" in the following > > table? (note that this table has a compound primary key, and column ??? > > is supposed to contain an index number which will uniquely number each > > row with the same id) > > > > CREATE TABLE MailingListItems > > (id int not null CONSTRAINT FK_MailingListItems_MailingList > > REFERENCES mailing_list(id) > > ,??? smallint not null > > ,... > > ,CONSTRAINT PK_MailingListItems PRIMARY KEY (id, ???) > > ) > > > > Or would you not name the first column "id" in the example above? > > > > Gert-Jan The naming of the foreign key constraint is just to give a hint when
debugging so I don't see it as a problem; better that then leaving SQL to generate a name for you. 'id' is within the scope of the table so there is no point duplicating its name; again, styles; neither of us is wrong. I prefix cross reference tables with xr_ because it groups them and its clear that its a cross reference table rather than a table that contains any meta data. The xr_ was given as a sample so don't worry that the pk_ is missing; anyway, in that particular table its a cross reference so in essence might not have a natural primary key. Tony. Show quote "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message news:43CC1C36.98B7001@toomuchspamalready.nl... > Tony, > > I didn't realize I used different casing (and tend to avoid using > underscores in column names and table names), and I agree we just have > different styles on that one. This difference is not very important to > me, although it can be confusing when it comes to the name of a foreign > key constraint (would the referenced table in > "fk_real_estate_order_part" be "order_part" or "part"?). > > Anyway, I see the logic in your naming convention (with respect to > "id"), and the rule you use in the view definition. But it just makes my > opinion stronger that it would be better to rename the id column in > table mailing_list to mailing_list_id. It seems the only place where you > do not use mailing_list_id is in the mailing_list table itself! > IMO, the advantage of the brevity of "id" (i.e. less typing) doesn't pay > off in the long run. I definitely like it more descriptive. A more > descriptive identifier becomes useful even in a simple multi-table join. > And much more useful in multi-table joins where the same table is joined > multiple times and other tables are joined as well (in other words, when > you start to get confused with all the aliases that are used). > > By the way, your table xr_mailing_list_individual does not have a > Primary Key. I assume it is (individual_id,mailing_list_id) or > (mailing_list_id,individual_id). > > Why do you use a prefix in the table name xr_mailing_list_individual? I > mean, why do you use a prefix for any table's name? Would you use a > prefix for a table that contains data and just one foreign key (such as > a OrderLines table)? > > Just interested, > Gert-Jan > > > Tony Rogerson wrote: >> >> Hi Gert-Jan, >> >> Some examples of how I would code this... >> >> create table mailing_list ( >> id int not null identity constraint sk_mailing_list unique >> clustered, >> >> name varchar(50) not null constraint pk_mailing_list primary key >> nonclustered >> ) >> >> create table xr_mailing_list_individual ( >> individual_id int not null references individual ( id ), >> mailing_list_id int not null references mailing_list ( id ) >> ) >> >> In a view definition >> >> select mailing_list_id = ml.id >> from xr_mailing_list_individual xr >> inner join mailing_list ml on ml.id = xr.mailing_list_id >> >> (I know I could use xr.mailing_list_id and probably would code it that >> way, >> but the above explains how i use it). >> >> In procs... >> >> create proc mailing_list_ins >> @mailing_list_name varchar(50) >> as >> begin >> insert mailing_list ( name ) values( @mailing_list_name ) >> >> select mailing_list_id = scope_identity() >> >> end >> >> I used to code multi case identifiers (and still do in non sql >> programming, >> like vb.net), but for SQL i always use lower case for identifiers and >> upper >> case for statements (i was being lazy above), basically, that helps any >> problems with collation and case sensitivity. >> >> Neither of us are wrong, its a question of styles. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlserverfaq.com - free video tutorials >> >> "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message >> news:43CC021B.E5C3EA31@toomuchspamalready.nl... >> > Tony Rogerson wrote: [snip] >> >> Using the column name 'id' is not a problem either, it is always >> >> contained >> >> within the object being modelled, you should always use table aliases >> >> on >> >> queries especially ones involving multiple tables - it takes the guess >> >> work >> >> out of what column comes from where, another must when doing >> >> performance >> >> tuning / problem diagnosis. >> >> >> >> create table mailing_list ( >> >> id int not null identity constraint sk_mailing_list unique >> >> clustered, >> >> >> >> name varchar(50) not null constraint pk_mailing_list primary key >> >> nonclustered >> >> ) >> >> >> >> Above you can clearly see that id is mailing_list.id and name is >> >> mailing_list.name; this makes for a clear uniform schema naming and >> >> allows >> >> the DBA or developer to easily create any maintanence scripts. [snip] >> > >> > Personally, I don't like columns named "id". In your example I would >> > prefer to use MailingListID or something like that. This way, if I use >> > the same column in a different table, and it has the same meaning, I >> > can >> > use the same name. Likewise, if the column has the same distinct name >> > in >> > two tables, I can be sure it means the same thing. >> > So in my database, a foreign key would be between two columns with the >> > same name. In your database, aforeign keys would be between two columns >> > with a different name (with the column of the referenced table being >> > called "id"). I think it is easier to make mistakes if you "reuse" a >> > column name like id. Also, in the referencing table, you would still >> > have to come up with a name for the foreign key column(s). >> > >> > You suggest the use of table aliases / full qualification. That will >> > work fine for individual queries. But what do you do in views >> > definitions when you want to include the "id" columns of two joined >> > tables? >> > >> > Another question: how would you name the column "???" in the following >> > table? (note that this table has a compound primary key, and column ??? >> > is supposed to contain an index number which will uniquely number each >> > row with the same id) >> > >> > CREATE TABLE MailingListItems >> > (id int not null CONSTRAINT FK_MailingListItems_MailingList >> > REFERENCES mailing_list(id) >> > ,??? smallint not null >> > ,... >> > ,CONSTRAINT PK_MailingListItems PRIMARY KEY (id, ???) >> > ) >> > >> > Or would you not name the first column "id" in the example above? >> > >> > Gert-Jan Sorry for my late response, I been away... See below
Tony Rogerson wrote: [snip] > 'id' is within the scope of the table so there is no point duplicating its I don't like to think in "right or wrong". But if I must: you are wrong> name; again, styles; neither of us is wrong. that there is no point in duplicating its name. This way, the same item will have the same name throughout the database. With a simple one table query, this doesn't make much of a difference, but it make a lot of difference in multi-table queries, (complex) view definitions, etc. I see a little convenience in doing it the way you describe, but hardly any value. I disagree with the priority you give the design rules. IMO, using the same name for the same item throughout the same database is very important. You do not follow that design principle (or give it a low priority), and I can only guess why that is. You have not yet mentioned any advantage of abbreviating the "id". > I prefix cross reference tables with xr_ because it groups them and its When does a table qualifies as a cross reference table? Is this a> clear that its a cross reference table rather than a table that contains any > meta data. property that is defined design time that will never change, not even if one or more columns are added? Will a new (or "the next") developer that has to add a table be able to correctly assess which type of table he will be adding? Because if the qualification can change (over time), then you have a problem. You would either have to rename the table (and change all existing queries), or end up with convention-violating table. And if a (lazy) developer has to choose between these two options, then I know which he will choose. So, your xr_ tables will be listed together in a tool like Enterprise Manager. What value does that add? Will it help you do your job better, faster or cheaper? Or is it simply a habit? > The xr_ was given as a sample so don't worry that the pk_ is missing; Okay, fair enough. So you do define Primary Key constraints on alltables, whether they are cross reference tables or not!? > anyway, in that particular table its a cross reference so in essence might In your earlier example I was supprised to see that the referencing> not have a natural primary key. table related to the Unique constraint in the referenced table instead of the Primary Key. Are you saying you would only define a Primary Key if it happened to be based on a natural key? (and create Unique constraints for all other keys?) You are right that I have a very different opinion about a lot of your naming conventions. I am especially interested to hear why you use these rules. So far, I have not seen many arguments. IOW, what are the advantages of using your naming conventions? What do these rules try to achieve? Which design principles are implemented if you use these rules? Gert-Jan Ok Gert-Jan, lets spin it on the head, what advantages does your convention
give? Using 'id' in the table saves a significant amount of typing, it also gets you into a good habit of aliasing your tables and using that table alias on all your column references - that saves time trying to work out which table the column belongs to when you are developing/performance tuning. Perhaps, the correct way to do this would be, given my convetion mailing_list.id or your convention MailingListId it shoudl be wrote <basetable>_MailingListId so you know full well where the column is? Mixing conventions is bad in my opinion as well, it leads to problems if you have a case sensitive database so MailinglistId (I've spelt it wrong already - typing too quickly) can be eaisly type wrongly. A cross reference table is a cross reference table, if you need to add columns that have some meaning to that cross reference, for instance a start and end date then fair enough - thats not a problem, it still does not change the fact that its cross referencing entity A with entity B - its the way to do efficient one or many to many relationships. Not all tables have a natural primary key, so by definition if a natural primary key exists i will make that the PRIMARY KEY constraint and have a UNIQUE constraint that is the surrogate key; if no natural primary key exists (like a message board) then I will just use a surrogate key or my own rolled key based on the IDENTITY property and will make that the PRIMARY KEY. Again, lets spin it on the head, what advantages does your convention give? In summary, the convention I use gives these benefits (not an exhaustive list)... a) saves typing therefore reducing the complexity of queries b) diciplines you to using table aliases which helps somebody who is new to the design understand what columns come from where - they don't have to go and do a sp_help on all the tables in the FROM clause. c) works with case sensitive databases without any extra effort Show quote "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message news:43CEC637.A36BC3FF@toomuchspamalready.nl... > Sorry for my late response, I been away... See below > > Tony Rogerson wrote: > [snip] >> 'id' is within the scope of the table so there is no point duplicating >> its >> name; again, styles; neither of us is wrong. > > I don't like to think in "right or wrong". But if I must: you are wrong > that there is no point in duplicating its name. This way, the same item > will have the same name throughout the database. With a simple one table > query, this doesn't make much of a difference, but it make a lot of > difference in multi-table queries, (complex) view definitions, etc. > > I see a little convenience in doing it the way you describe, but hardly > any value. I disagree with the priority you give the design rules. IMO, > using the same name for the same item throughout the same database is > very important. You do not follow that design principle (or give it a > low priority), and I can only guess why that is. You have not yet > mentioned any advantage of abbreviating the "id". > >> I prefix cross reference tables with xr_ because it groups them and its >> clear that its a cross reference table rather than a table that contains >> any >> meta data. > > When does a table qualifies as a cross reference table? Is this a > property that is defined design time that will never change, not even if > one or more columns are added? Will a new (or "the next") developer that > has to add a table be able to correctly assess which type of table he > will be adding? > > Because if the qualification can change (over time), then you have a > problem. You would either have to rename the table (and change all > existing queries), or end up with convention-violating table. And if a > (lazy) developer has to choose between these two options, then I know > which he will choose. > > So, your xr_ tables will be listed together in a tool like Enterprise > Manager. What value does that add? Will it help you do your job better, > faster or cheaper? Or is it simply a habit? > >> The xr_ was given as a sample so don't worry that the pk_ is missing; > > Okay, fair enough. So you do define Primary Key constraints on all > tables, whether they are cross reference tables or not!? > >> anyway, in that particular table its a cross reference so in essence >> might >> not have a natural primary key. > > In your earlier example I was supprised to see that the referencing > table related to the Unique constraint in the referenced table instead > of the Primary Key. Are you saying you would only define a Primary Key > if it happened to be based on a natural key? (and create Unique > constraints for all other keys?) > > You are right that I have a very different opinion about a lot of your > naming conventions. I am especially interested to hear why you use these > rules. So far, I have not seen many arguments. IOW, what are the > advantages of using your naming conventions? What do these rules try to > achieve? Which design principles are implemented if you use these rules? > > Gert-Jan I think I have a complete picture of your conventions now, and why you
like them. I don't agree with most points, but it was definitely an interesting discussion. See inline for the answers to your questions. Cheers, Gert-Jan Tony Rogerson wrote: > When using the same name when the item means the same thing has the> Ok Gert-Jan, lets spin it on the head, what advantages does your convention > give? advantage that if you see a column with that name, then you can be sure that it is the same item, with the same meaning and same data definition. This makes it harder to make mistakes, especially in applications that use a predefined query or view, because there is no possible misinterpretation of the column name. Note that the result of a query will never include the table alias or table name. The casing I use in the name (for example OrderID) is just a habit. I don't see any real benefit when compared to using words connected by underscores (like order_id). I usually work with standard installations of SQL-Server, which means Upper/Lower case differences do not cause problems. But you are right that that is a disadvantage of camelcase. > Using 'id' in the table saves a significant amount of typing, it also gets I have used something like <table abbreviation>_<columnname>, for> you into a good habit of aliasing your tables and using that table alias on > all your column references - that saves time trying to work out which table > the column belongs to when you are developing/performance tuning. > > Perhaps, the correct way to do this would be, given my convetion > mailing_list.id or your convention MailingListId it shoudl be wrote > <basetable>_MailingListId so you know full well where the column is? example ord_OrderNumber. Although it has advantages (in most queries you will not need table alises) I am not advocating it. I am definitely not advocating the duplication of the full table name in each column name, if only for the insane amount of extra typing it would require. One of the reasons against this convention is, that changes or redesign can cause a column to end up in a different table. These changes may have been done to increase performance. You don't want to change all your queries because of that. Also, it will not eliminate the use of table aliases, because if the same table is joined multiple times, you will still need to alias. I agree that it is a good practice to use a table alias in each selection list column (if the query selects from more than one table). I still disagree that abbreviating mailing_list_id to id is a good thing. I still think this is a bad rule, as I have explained before. > Mixing conventions is bad in my opinion as well, it leads to problems if you If you are actually using a case sensitive database, then mistyping an> have a case sensitive database so MailinglistId (I've spelt it wrong > already - typing too quickly) can be eaisly type wrongly. object's name will show up immediately. If there is a "MailingListID", there is no chance that there is also a "MailingListId", so the query will immediately throw an error. It is only a problem if you have a case insensitive database (e.g. Development) that is converted to a case sensitive database later on (e.g. Production). The only remaining risk of misspelling is when defining (or changing) the table definition. Correcting such mistakes require a lot of work if they are discovered (too) late. The only way to eliminate this problem is to do what you have done: completely avoid upper case (or lower case) characters in object names. You definitely have you a valid argument here. The disadvantage is, that you have to use underscores to keep the names readable. Using underscores means akward (and extra) typing. If you never use a case insensitive database, there is no reason to avoid mixed case naming. Using underscores in object names also 'complicates' other use of underscores. For example, if I define a Default constraint or Check constraint, then I use the table name and column name in the constraint name, separated by an underscore. For example DF_OrderItems_UnitPrice for a default on UnitPrice in the OrderItems table. IMO, this constraint name is much easier to read and harder to misinterpret than DF_order_items_unit_price. But overall I agree that your convention with respect to casing is a good one. > A cross reference table is a cross reference table, if you need to add I don't have an example at hand, but I am fairly sure that you will get> columns that have some meaning to that cross reference, for instance a start > and end date then fair enough - thats not a problem, it still does not > change the fact that its cross referencing entity A with entity B - its the > way to do efficient one or many to many relationships. into trouble when tables are added and existing tables are changed. And because I see no real benefits, I would not use this rule. > Not all tables have a natural primary key, so by definition if a natural I will always create the Primary Key on the column(s) that I want the> primary key exists i will make that the PRIMARY KEY constraint and have a > UNIQUE constraint that is the surrogate key; if no natural primary key > exists (like a message board) then I will just use a surrogate key or my own > rolled key based on the IDENTITY property and will make that the PRIMARY > KEY. > > Again, lets spin it on the head, what advantages does your convention give? Foreign Keys to refer to. So if there is a natural key and I have added a surrogate key, then that surrogate key will be defined as Primary Key. All alternate keys will have Unique constraints. Although there are some (insignificant) advantages in this rule, I use it out of habit. The one possible advantage worth mentioning is, that you will never have to wander which key to refer to when you define a Foreign Key. All Foreign Key constraints will refer to the Primary Key, and not to "one of the" Unique constraints. In your schema, if you make a mistake and choose the 'wrong' Unique constraint, it can have negative performance consequences in multi-table joins. Changing (redesigning) the foreign key relation after that requires a lot of work. > In summary, the convention I use gives these benefits (not an exhaustive It saves typing, but does not reduce the complexity of queries at all.> list)... > > a) saves typing therefore reducing the complexity of queries And in many multi-table joins it will force the use of an (other unnecessary) column alias, which adds a bit of typing. > b) diciplines you to using table aliases which helps somebody who is new This is true for the columns that might be present in several tables,> to the design understand what columns come from where - they don't have to > go and do a sp_help on all the tables in the FROM clause. but not for the other columns. This would also be true for "my" naming convention, for example when you join a table to its referencing table. IMO, mentioning a table alias for each column is a good thing, but the "id" versus "MailingListID" convention makes not difference in this respect. > c) works with case sensitive databases without any extra effort Very true.Show quote Tony and Gert-Jan,
Thanks for the discussion. Some valid points on both sides give me some things to consider in my own naming conventions. These are the kinds of discussions that I think we can all benefit from. Show quote "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message news:43D001C3.95C7B5CD@toomuchspamalready.nl... > I think I have a complete picture of your conventions now, and why you > like them. I don't agree with most points, but it was definitely an > interesting discussion. See inline for the answers to your questions. > > Cheers, > Gert-Jan > > > Tony Rogerson wrote: > > > > Ok Gert-Jan, lets spin it on the head, what advantages does your convention > > give? > > When using the same name when the item means the same thing has the > advantage that if you see a column with that name, then you can be sure > that it is the same item, with the same meaning and same data > definition. This makes it harder to make mistakes, especially in > applications that use a predefined query or view, because there is no > possible misinterpretation of the column name. Note that the result of a > query will never include the table alias or table name. > > The casing I use in the name (for example OrderID) is just a habit. I > don't see any real benefit when compared to using words connected by > underscores (like order_id). I usually work with standard installations > of SQL-Server, which means Upper/Lower case differences do not cause > problems. But you are right that that is a disadvantage of camelcase. > > > Using 'id' in the table saves a significant amount of typing, it also gets > > you into a good habit of aliasing your tables and using that table alias on > > all your column references - that saves time trying to work out which table > > the column belongs to when you are developing/performance tuning. > > > > Perhaps, the correct way to do this would be, given my convetion > > mailing_list.id or your convention MailingListId it shoudl be wrote > > <basetable>_MailingListId so you know full well where the column is? > > I have used something like <table abbreviation>_<columnname>, for > example ord_OrderNumber. Although it has advantages (in most queries you > will not need table alises) I am not advocating it. I am definitely not > advocating the duplication of the full table name in each column name, > if only for the insane amount of extra typing it would require. One of > the reasons against this convention is, that changes or redesign can > cause a column to end up in a different table. These changes may have > been done to increase performance. You don't want to change all your > queries because of that. Also, it will not eliminate the use of table > aliases, because if the same table is joined multiple times, you will > still need to alias. > > I agree that it is a good practice to use a table alias in each > selection list column (if the query selects from more than one table). I > still disagree that abbreviating mailing_list_id to id is a good thing. > I still think this is a bad rule, as I have explained before. > > > Mixing conventions is bad in my opinion as well, it leads to problems if you > > have a case sensitive database so MailinglistId (I've spelt it wrong > > already - typing too quickly) can be eaisly type wrongly. > > If you are actually using a case sensitive database, then mistyping an > object's name will show up immediately. If there is a "MailingListID", > there is no chance that there is also a "MailingListId", so the query > will immediately throw an error. > It is only a problem if you have a case insensitive database (e.g. > Development) that is converted to a case sensitive database later on > (e.g. Production). The only remaining risk of misspelling is when > defining (or changing) the table definition. Correcting such mistakes > require a lot of work if they are discovered (too) late. > > The only way to eliminate this problem is to do what you have done: > completely avoid upper case (or lower case) characters in object names. > You definitely have you a valid argument here. The disadvantage is, that > you have to use underscores to keep the names readable. Using > underscores means akward (and extra) typing. If you never use a case > insensitive database, there is no reason to avoid mixed case naming. > > Using underscores in object names also 'complicates' other use of > underscores. For example, if I define a Default constraint or Check > constraint, then I use the table name and column name in the constraint > name, separated by an underscore. For example DF_OrderItems_UnitPrice > for a default on UnitPrice in the OrderItems table. IMO, this constraint > name is much easier to read and harder to misinterpret than > DF_order_items_unit_price. > > But overall I agree that your convention with respect to casing is a > good one. > > > A cross reference table is a cross reference table, if you need to add > > columns that have some meaning to that cross reference, for instance a start > > and end date then fair enough - thats not a problem, it still does not > > change the fact that its cross referencing entity A with entity B - its the > > way to do efficient one or many to many relationships. > > I don't have an example at hand, but I am fairly sure that you will get > into trouble when tables are added and existing tables are changed. And > because I see no real benefits, I would not use this rule. > > > Not all tables have a natural primary key, so by definition if a natural > > primary key exists i will make that the PRIMARY KEY constraint and have a > > UNIQUE constraint that is the surrogate key; if no natural primary key > > exists (like a message board) then I will just use a surrogate key or my own > > rolled key based on the IDENTITY property and will make that the PRIMARY > > KEY. > > > > Again, lets spin it on the head, what advantages does your convention give? > > I will always create the Primary Key on the column(s) that I want the > Foreign Keys to refer to. So if there is a natural key and I have added > a surrogate key, then that surrogate key will be defined as Primary Key. > All alternate keys will have Unique constraints. > > Although there are some (insignificant) advantages in this rule, I use > it out of habit. The one possible advantage worth mentioning is, that > you will never have to wander which key to refer to when you define a > Foreign Key. All Foreign Key constraints will refer to the Primary Key, > and not to "one of the" Unique constraints. In your schema, if you make > a mistake and choose the 'wrong' Unique constraint, it can have negative > performance consequences in multi-table joins. Changing (redesigning) > the foreign key relation after that requires a lot of work. > > > In summary, the convention I use gives these benefits (not an exhaustive > > list)... > > > > a) saves typing therefore reducing the complexity of queries > > It saves typing, but does not reduce the complexity of queries at all. > > And in many multi-table joins it will force the use of an (other > unnecessary) column alias, which adds a bit of typing. > > > b) diciplines you to using table aliases which helps somebody who is new > > to the design understand what columns come from where - they don't have to > > go and do a sp_help on all the tables in the FROM clause. > > This is true for the columns that might be present in several tables, > but not for the other columns. This would also be true for "my" naming > convention, for example when you join a table to its referencing table. > IMO, mentioning a table alias for each column is a good thing, but the > "id" versus "MailingListID" convention makes not difference in this > respect. > > > c) works with case sensitive databases without any extra effort > > Very true. > > > -- > > Tony Rogerson > > SQL Server MVP > > http://sqlserverfaq.com - free video tutorials >> Another question: how would you name the column "???" in the following table? (note that this table has a compound primary key, and column ??? is supposed to contain an index number which will uniquely number each row with the same id) << Get a copy of SQL PROGRAMMING STYLE and look at the definitions of theISO-11179 style postfixes I give. I would guess you want something like this: CREATE TABLE MailingGroups (list_id INTEGER NOT NULL REFERENCES MailingLists(list_id) list_seq INTEGER NOT NULL, PRIMARY KEY (list_id, list_seq), ..); And we might like to have an UPDATE that closes gaps in the sequence numbers. I am assuming that you want a sequence, since I do not know what an index number would be. I would tend to use a name for each mailing group like "Big Donors", "Cheapskates", etc. so I can figure out what they are easier than a number. Of course you would never use that vague, general "id" as a data element name. Unlike Tony, I know that some things are a matter of ISO/ANSI Standards and not the personal opinions of each developer as tehy are writing code. --CELKO-- wrote:
>Of course you would never use that vague, general "id" as a data ISO Standards are great and very valuable, but don't forget they are a means to an end, not the end itself. If they don't fit the problem space exactly or solve an implementation issue efficiently then there should be enough flexibility to allow a little "poetic license" in solving the problem at hand. I don't think the paying customer is going to be stoked that their DB schema & DML code is ANSI (or ISO) compliant and 3NF or BCNF (or more) if it's performing like a slug. That may not necessarily be the fault of the relational model (it may be an implementation issue with the specific RDBMS, grossly uneven data distribution, or something else) but nonetheless it still needs to be "fixed". (I'm thinking more of the recent query plan optimisation threads than the column naming conventions threads.)>element name. Unlike Tony, I know that some things are a matter of >ISO/ANSI Standards and not the personal opinions of each developer as >tehy are writing code. > > >> ISO Standards are great and very valuable, but don't forget they are a means to an end, not the end itself. << Documentation, maintainability, interfacing to systems, etc. areuniversal goals, not options. >> If they don't fit the problem space exactly or solve an implementation issue efficiently then there should be enough flexibility to allow a little "poetic license" in solving the problem at hand. << Ah yes, the agile extreme cowboy coder syndrome!>> I don't think the paying customer is going to be stoked that their DB schema & DML code is ANSI (or ISO) compliant and 3NF or BCNF (or more) if it's performing like a slug. << Only until the first SOX audit. ISO-9000 audit, etc. I get called inwhen things go wrong, so I have seen a lot of bad stuff over the years. Or the first disaster. I was lucky in that I started my career in Defense, Aerospace and Medical, where sloppy, non-standard code can literally kill people. You had a large budget, lots of time and "right code" was far, far more important than "fast code", so you got to train yourself to write good code first, then you pick up speed in a few years. I have no idea why people today seem to think that quality and speed do not mix. Look at a professional athlete. The kids today start by learning speed first, they never research anything like standards, the customers industry, new coding tricks, etc. What I see all the time is SQL written as if it were a 3GL. A few of them might pick up the traits of quality programmng later. But how do you learn to do quality coding when you never stay in one place to see if it works? When you have no orderly process in place to guarantee quality? When you cannot be held accuntable for your work? --CELKO-- wrote:
>>>ISO Standards are great and very valuable, but don't forget they are a means to an end, not the end itself. << Documentation of the implemented system. Maintainability of the >>> >>> >Documentation, maintainability, interfacing to systems, etc. are >universal goals, not options. > > implemented system. Integration of the implemented system to other implemented systems. These are the goals. The standards tell us how to achieve those goals but are not the goals themselves (except to the standards writers) - the goals of the customer are an implemented system to solve their business problem(s) (and the associated documentation, etc. of that system). >>> If they don't fit the problem space exactly or solve an implementation issue efficiently then there should be enough flexibility to allow a little "poetic license" in solving the problem at hand. << I'm not talking about cowboy coding - I've seen my fair share of that. >>> >>> >Ah yes, the agile extreme cowboy coder syndrome! > > What I'm talking about is designing and writing a system to comply to standards as much as possible and then *optimising* that system if necessary. It may perform just fine without any extra optimisation or deviation from standards, but not every solution does. >I have no idea They do mix, absolutely. No argument there. In fact, quality code >why people today seem to think that quality and speed do not mix. Look >at a professional athlete. > > usually implies speed (or at least efficiency). However, "quality" does not imply 100% conformance to standards. To expand on your example of a professional athlete, good runners tend to have quality technique (accepted good running posture for example - an accepted "standard") but they all have their own individual styles (that their trainers work on exploiting) and some very fast runners don't conform *exactly* to that standard even though their style is based on the accepted standard. But does that mean they're not quality athletes? Does that fact that they lean too far forwards down straights (according to the accepted standard) mean they are amateurs or beginners (or "kids")? >The kids today start by learning speed first, they never research You're preaching to the converted here. I've seen plenty of crap SQL >anything like standards, the customers industry, new coding tricks, >etc. What I see all the time is SQL written as if it were a 3GL. > > code that was obviously written by a procedural programmer (and not a very good one at that) who doesn't know what RDBMS stands for and only saw a set-based language a couple weeks before writing the code. I also appreciate the need to have at least a basic understanding the relevant industry before blindly embarking on a coding exercise. But I'm not talking about crap code written by amateur programmers - I'm talking about experienced programmers, seasoned industry professionals, that have written quality code that mostly conforms to standards but has deviated slightly in some situations to solve specific problems (like occasionally denormalising relationships to dramatically improve performance of a specific set of reports, or implementing some proprietary vendor-specific language feature that performs better in the given circumstance than the equivalent standards compliant language feature implementation in that vendor's product (assuming the likelihood of the code ever being ported to a different vendor product is remote)). >A few of them might pick up the traits of quality programmng later. All this talk of "quality" has made my mind drift off to thoughts of >But how do you learn to do quality coding when you never stay in one >place to see if it works? When you have no orderly process in place to >guarantee quality? When you cannot be held accuntable for your work? > > _Zen and the Art of Motorcycle Maintenance_. (Sorry, must be time for me to get up from my keyboard and have a break.) Judging by your ideas around the IF ELSE thread then god help the company
you go in and help! Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1137465374.560010.291650@g47g2000cwa.googlegroups.com... >>> ISO Standards are great and very valuable, but don't forget they are a >>> means to an end, not the end itself. << > > Documentation, maintainability, interfacing to systems, etc. are > universal goals, not options. > >>> If they don't fit the problem space exactly or solve an implementation >>> issue efficiently then there should be enough flexibility to allow a >>> little "poetic license" in solving the problem at hand. << > > Ah yes, the agile extreme cowboy coder syndrome! > >>> I don't think the paying customer is going to be stoked that their DB >>> schema & DML code is ANSI (or ISO) compliant and 3NF or BCNF (or more) >>> if it's performing like a slug. << > > Only until the first SOX audit. ISO-9000 audit, etc. I get called in > when things go wrong, so I have seen a lot of bad stuff over the years. > Or the first disaster. > > I was lucky in that I started my career in Defense, Aerospace and > Medical, where sloppy, non-standard code can literally kill people. > You had a large budget, lots of time and "right code" was far, far > more important than "fast code", so you got to train yourself to write > good code first, then you pick up speed in a few years. I have no idea > why people today seem to think that quality and speed do not mix. Look > at a professional athlete. > > The kids today start by learning speed first, they never research > anything like standards, the customers industry, new coding tricks, > etc. What I see all the time is SQL written as if it were a 3GL. > > A few of them might pick up the traits of quality programmng later. > But how do you learn to do quality coding when you never stay in one > place to see if it works? When you have no orderly process in place to > guarantee quality? When you cannot be held accuntable for your work? > This is where the *Niceness of Celko* surfaces - he leaves room for
improvement which any follower can take advantage of. Providing the company still exists and is still open to outsourcing. ;) ML --- http://milambda.blogspot.com/ "Give not that which is holy unto the dogs, neither cast ye your pearls
before swine, lest they trample them under their feet, and turn again and rend you." Matthew 7:6 Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1137264524.173507.61020@z14g2000cwz.googlegroups.com... > Get a copy of SQL PROGRAMMING STYLE. I give a set of conventions based > on ISO-11179 standards and readability studies that we did back in the > 1970-1980's when Software Engineering was the hot topic du jour. > > The basic idea of ISO-11179 that you name a data element for what it is > in the data model. It never changes names from table to table. > > You never name it for the location *where* it is used, so those silly, > redundant "tbl_" prefixes are out. SQL only has one data structure in > the first place. > > You never name it for how it is used, so those " PK_", "VW_" etc. > prefixes are out. Unless "VW_" means Volkswagen and not VIEW. > > This kind of crappy naming comes from not knowing that you never mix > data and metadata in a schema. > > You never have a column is too vague to stand alone. There is no > magical, universal "id" -- it begs the question as to what the heck > does it identify?!! If you are simply numbering the rows with > IDENTITY, then you do not have a Relational Model at all; you are > mimicking a sequential magnetic tape file in SQL. > > Likewise "date" , "type", "code" are too vague. > | |||||||||||||||||||||||
