InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 22:59:19 GMT20http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxMight sound like it, but only to someone who doesn't understand the language...Mon, 14 Jun 2010 10:07:40 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]David.Poole (6/10/2010)[/b][hr]There seems to be an assumption that there is a single field acting as the PK. This isn't necessarily true.As to calling things ID because its easier for developers to write boiler plate code, that sounds like a violation of software engineering principles. We want to aim for loose coupling and strong cohesion.[/quote]Calling things TableNameID in one special set of circumstances because developers utilize bad development practices also sounds like a violation of software engineering principles. If it makes it easier to write boiler plate code, that is a side effect as opposed to driving reason for the naming scheme.Thu, 10 Jun 2010 22:14:59 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxThere seems to be an assumption that there is a single field acting as the PK. This isn't necessarily true.As to calling things ID because its easier for developers to write boiler plate code, that sounds like a violation of software engineering principles. We want to aim for loose coupling and strong cohesion.Thu, 10 Jun 2010 14:36:24 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]Thomas-282729 (6/9/2010)[/b][hr][quote][b]RichB (6/9/2010)[/b][hr][quote][b]Thomas-282729 (6/8/2010)[/b][hr]Again, what you are suggesting is a poorly written query where the developer uses the wrong alias (A.Id instead of B.Id). How does that improve clarity in the scenario where say "EmployeeId" exists in five or ten tables? It will be just as easy to mistakenly write A.EmployeeId instead of B.EmployeeId as it would writing A.Id vs B.Id. [/quote]Well, in this case the difference between getting a.EmployeeId instead of b.EmployeeId is likely to be less significant than gettinga.ID when you wanted b.ID where a.ID comes from Employee a ([employee]Id, name, stuff) and b.ID comes from ProjectStaff([project]Id, employeeid, stuff)Also - that bug is almost impossible to find, whereas with properly named columns its pretty straightforward.[/quote]How do you figure it is easier to find? Suppose the PK is named EmployeeId. It is just as easy to write A.EmployeeId instead of B.EmployeeId in the join as it would to write A.Id vs B.Id. In fact, I would say that having the PK be named "Id" makes it easier to see which column is the PK and which column is the FK. That would make it easier to find situations where the developer accidentally joined the column to itself (e.g. A.EmployeeId = A.EmployeeId).Of course, the developer used a better alias, then it would be even easier to see a problem.[/quote]Fortunately, in almost every company I've worked for, there are designated database developers who write the stored procedures and views. On the application and reporting side, no one is writing SQL queries, and in fact they don't even have access to the tables, only exec permission on the procedures. Therefore, arguments about what column names would save the most keystrokes to enter or what column names would be the most understandable to a casual user are not taken into consideration when designing the data model. There are cases where application developers will request that result columns from a stored procedure be aliased in a certain way to make them more understandable to them personally, and I don't have so much of a problem with that, so long as the underlying tables themselves are properly normalized and conform to a standard naming convention.Thu, 10 Jun 2010 09:17:15 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]RichB (6/9/2010)[/b][hr][quote][b]Thomas-282729 (6/8/2010)[/b][hr]Again, what you are suggesting is a poorly written query where the developer uses the wrong alias (A.Id instead of B.Id). How does that improve clarity in the scenario where say "EmployeeId" exists in five or ten tables? It will be just as easy to mistakenly write A.EmployeeId instead of B.EmployeeId as it would writing A.Id vs B.Id. [/quote]Well, in this case the difference between getting a.EmployeeId instead of b.EmployeeId is likely to be less significant than gettinga.ID when you wanted b.ID where a.ID comes from Employee a ([employee]Id, name, stuff) and b.ID comes from ProjectStaff([project]Id, employeeid, stuff)Also - that bug is almost impossible to find, whereas with properly named columns its pretty straightforward.[/quote]How do you figure it is easier to find? Suppose the PK is named EmployeeId. It is just as easy to write A.EmployeeId instead of B.EmployeeId in the join as it would to write A.Id vs B.Id. In fact, I would say that having the PK be named "Id" makes it easier to see which column is the PK and which column is the FK. That would make it easier to find situations where the developer accidentally joined the column to itself (e.g. A.EmployeeId = A.EmployeeId).Of course, the developer used a better alias, then it would be even easier to see a problem.Wed, 09 Jun 2010 20:46:05 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]Stefan Krzywicki (6/9/2010)[/b][hr]Consider the difference between[code="sql"]SELECT E.EmployeeID, E.LastName, E.FirstName, D.DepartmentID, D.Department,P.Project, P.ProjectID, PD.ProjectDetailID, PD.ProjectDetail PD.CommentsFROM Employee EINNER JOIN Department DON E.DepartmentID = D.DepartmentIDINER JOIN ProjectInterim PRION PRI.EmployeeID = E.EmployeeIDINNER JOIN Project PON P.ProjectID = PRI.ProjectIDINNER JOIN ProjectDetail PDON PD.ProjectID = P.ProjectID[/CODE]AND[Code="sql"]SELECT E.ID, E.LastName, E.FirstName, D.ID, D.Department, P.Project, P.ID, PD.ID,PD.ProjectDetail PD.CommentsFROM Employee EINNER JOIN Department DON E.AssignmentID = D.IDINER JOIN ProjectInterim PRION PRI.ResourceID = E.IDINNER JOIN Project PON P.ID = PRI.TaskedIDINNER JOIN ProjectDetail PDON PD.HeaderID = P.ID[/code]When you have multiple joins and name the foreign key something other than the name it has in the parent table, it can get very confusing very quickly. Sometimes when writing it, definitely when bugging it. And if you didn't write it in the first place and there's no extra documentation, forget it.[/quote]IMO, the second one (even though it clearly is different than the first query) is easier to read (and write) as the comparison to a primary key stands out compared to the comparison to a foreign key. I.e., it is easier to pick out the foreign key. In addition, your choice of column names in the Select clause is unrealistic. The developer will have think about and alias the column names to prevent duplicates which is another advantage IMO. As I read your response, there are actually two issues here. The first, is the developer themselves writing the wrong query because they chose poor aliases. IMO, there is nothing to be done about that. Even if you used the table name in the primary key, if that column name exists in many places, it will be just as easy for that developer to use the wrong alias. In the first example, the developer could have just as easily written "PD.ProjectId = PRI.ProjectId" in the last ON clause. In my experience, choosing the wrong alias is almost never the problem with queries even on systems that are using "Id" as the surrogate key name. The problem is almost always with the choice of tables, unneeded joins, accidental cross joins and such. If I were the DBA or development manager sending the second query back to the developer because it was incorrect, I'd suggest they use better aliases and pay more attention. This is no different than a developer declaring variables "a" and "b" and using the wrong one in their code.The second problem is debugging someone else's code where they chose poor table aliases. For that, you need a tool like Red-Gate's SQL Refactor (No, I do not work for them). I seem to remember there is a website somewhere that will do something similar. This is basically the same problem as inheriting code that used poor variable names. The solution is to refactor the variable names into better names. Wed, 09 Jun 2010 09:46:43 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxConsider the difference between[code="sql"]SELECT E.EmployeeID, E.LastName, E.FirstName, D.DepartmentID, D.Department,P.Project, P.ProjectID, PD.ProjectDetailID, PD.ProjectDetail PD.CommentsFROM Employee EINNER JOIN Department DON E.DepartmentID = D.DepartmentIDINER JOIN ProjectInterim PRION PRI.EmployeeID = E.EmployeeIDINNER JOIN Project PON P.ProjectID = PRI.ProjectIDINNER JOIN ProjectDetail PDON PD.ProjectID = P.ProjectID[/CODE]AND[Code="sql"]SELECT E.ID, E.LastName, E.FirstName, D.ID, D.Department, P.Project, P.ID, PD.ID,PD.ProjectDetail PD.CommentsFROM Employee EINNER JOIN Department DON E.AssignmentID = D.IDINER JOIN ProjectInterim PRION PRI.ResourceID = E.IDINNER JOIN Project PON P.ID = PRI.TaskedIDINNER JOIN ProjectDetail PDON PD.HeaderID = P.ID[/code]When you have multiple joins and name the foreign key something other than the name it has in the parent table, it can get very confusing very quickly. Sometimes when writing it, definitely when bugging it. And if you didn't write it in the first place and there's no extra documentation, forget it.Wed, 09 Jun 2010 06:41:30 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]Thomas-282729 (6/8/2010)[/b][hr]Again, what you are suggesting is a poorly written query where the developer uses the wrong alias (A.Id instead of B.Id). How does that improve clarity in the scenario where say "EmployeeId" exists in five or ten tables? It will be just as easy to mistakenly write A.EmployeeId instead of B.EmployeeId as it would writing A.Id vs B.Id. [/quote]Well, in this case the difference between getting a.EmployeeId instead of b.EmployeeId is likely to be less significant than gettinga.ID when you wanted b.ID where a.ID comes from Employee a ([employee]Id, name, stuff) and b.ID comes from ProjectStaff([project]Id, employeeid, stuff)Also - that bug is almost impossible to find, whereas with properly named columns its pretty straightforward.Wed, 09 Jun 2010 05:16:32 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]Stefan Krzywicki (6/8/2010)[/b][hr]Why stop at the primary key column? Because ID is likely the only column that's going to be in every table. You're very unlikely to have lots of "Phone" columns or "MaritalStatus" columns. If you do, you're likely designing badly.[/quote]Don't be so ridiculous. You know perfectly well that the correct design would be to use:MaritalStatus.ID, MaritalStatus.Nameand Phone.ID, Phone.Number I think you are deliberately trolling the totally on the ball and hugely sensible patterns of Thomas.:hehe:Wed, 09 Jun 2010 05:11:00 GMTRichBhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote]To simply state "That is the problem of the developer" sets up an adversarial relationship.[/quote]On the contrary, using "TableNameId" for the PK column is harder for the developer not easier IMO. I'm coming at this from both the DBA perspective and the developer perspective as I've done and do both. Consistency is the developer's friend. It is much easier to code against a table with a consistent naming approach then to have one naming approach for surrogate PKs and another for all other columns. So you use "E" for an alias so you have "E.Id" instead of E.EmployeeId". Why is the first form more difficult than say "E.Name" instead of "E.EmployeeName"? Why not prefix the table name on all columns instead of the just a surrogate primary key? I have no issue with table aliases. I use them all the time; even single letter ones. However, if the problem is that developers confuse themselves by choosing poor table aliases, that is problem with the developer not the database schema. If the developer cannot remember what "E.Name" represents because they forgot what they aliases as "E", that is a developer problem.[quote]I disagree. DRI is nice when it exists, but nothing beats a database diagram and notes from the designer explaining what the fields are and what the relationships are.[/quote]If DRI does not exist there are much, much, much larger issues at hand. Having DRI is absolutely crucial to even a passable database design. While a schema diagram is also useful, that kind of documentation may or may not be current with the actual database schema. Having notes from the designer about what the fields mean is entirely orthogonal the issue of column naming. What you are describing is a database dictionary and I entirely agree that such a document is useful. Still, I would find an entry for "Employee, Id" to be as clear as "Employee, EmployeeId". [quote]Simply naming the field ID can lead to confusion in a complex query where you have 5 tables each with a column named ID. Developers are going to use aliases whether you like it or not and they're going to end up with bad queries if you've created a confusing structure.[/quote]Again, what you are suggesting is a poorly written query where the developer uses the wrong alias (A.Id instead of B.Id). How does that improve clarity in the scenario where say "EmployeeId" exists in five or ten tables? It will be just as easy to mistakenly write A.EmployeeId instead of B.EmployeeId as it would writing A.Id vs B.Id. Developers should be writing code for other people to read. If they use a bunch of poorly chosen aliases which makes it harder for other developers to read their code, that is a separate, albeit important, issue. Code reviews and automated syntax checkers would help in that situation.The root issue seems to be that you want the database designer to account for the possibility of developers writing queries with many tables and obtuse aliases. If that is justification, I'm not sure why that justification doesn't apply to all the other columns. In general, IMO, this issue is a pedantic one but does go to the root of consistent rules for naming. Putting the name of the entity to which an attribute belongs in its name is redundant IMO whether that column is the PK or just another column. It seems silly to make an exception for a PK. I've managed databases and written code against both styles and I found that simply using "Id" for a surrogate PK was more flexible, simpler and easier to code against than to have the table name prefixed.Tue, 08 Jun 2010 15:55:17 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]David.Poole (6/8/2010)[/b][hr]Self-documenting code is another thing that REALLY annoys me. When there are things like nDoc, JavaDoc, SQLDoc etc the idea that I have to check everything out of source control, fire up an IDE and start trawling through code to find out how things work is anathema. Same with management studio. Why would I start trawling through the schema looking for DRI constraints when a web based internal WIKI can outline what I need in a fraction of the time?First question from the users of a data warehouse is usually "Do you have a schema diagram for this", closely followed by some variant of "Do you have a data dictionary of what is available"? If I said, "no, why don't you inspect the self-documenting code" I'd soon find I wouldn't be their first choice to work on their prestige projects.[/quote]Absolutely! Another problem with trying to figure out what's going on from the code and the schema, aside from the huge time sink that it is, is that this tells you how it is, not how it should be.Tue, 08 Jun 2010 10:57:04 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxNon-database aware developers have a different perspective to DB aware developers on a particular problem.I would not advocate having "ManagerEmployeeID", ManagerID is clearly more appropriate. I used to use id, name and description when I was mainly a developer but I now avoid it like plague because experience has taught me that it becomes confusing in larger schemas, particularly where 1:1 relationships exist.The argument for using ID,name etc is that adding aliases into the query gets around that, but why have to put in aliases in every query you write rather that in the table once?Self-documenting code is another thing that REALLY annoys me. When there are things like nDoc, JavaDoc, SQLDoc etc the idea that I have to check everything out of source control, fire up an IDE and start trawling through code to find out how things work is anathema. Same with management studio. Why would I start trawling through the schema looking for DRI constraints when a web based internal WIKI can outline what I need in a fraction of the time?First question from the users of a data warehouse is usually "Do you have a schema diagram for this", closely followed by some variant of "Do you have a data dictionary of what is available"? If I said, "no, why don't you inspect the self-documenting code" I'd soon find I wouldn't be their first choice to work on their prestige projects.Tue, 08 Jun 2010 10:50:22 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]Thomas-282729 (6/8/2010)[/b][hr][quote][b]Stefan Krzywicki (6/8/2010)[/b][hr]While it may be "more logical", it isn't really within your power to ensure the developers code things your way. One of the main reasons for aliases is to keep from having to type full table names over and over. And the only place you're going to want to preface the field with the table name in most cases is the Primary Key of the column so it will make sense in joins and when used as a foreign key.[/quote]Again, that is the problem of the developer. There is no end of havoc that can be wrought by a bad developer. I'm not against using table aliases. I just do not want to bend over backwards with redundant column names to defend against the practice of using **poor** aliases.[/quote]To simply state "That is the problem of the developer" sets up an adversarial relationship. If you use decent practices that make the information easier to scan, you'll have less trouble in dealing with developers. They're not the enemy, they're the consumers of your data or those who allow others to consume it. Without developers, your database is useless. There's also no end of havoc that can be wrought by a bad DBA or database designer.There's nothing "poor" about an alias of E for a table named Employee, especially if the columns are well named. I hardly think naming a column EmployeeID or EmpID instead of ID is "bending over backward". It makes it clearer to read the database and easier to use.[quote][b]Thomas-282729 (6/8/2010)[/b][hr][quote][b]Stefan Krzywicki (6/8/2010)[/b][hr]Except that in your example there, you alias the tables, but then don't use them and have apparently named one of your tables a reserved word "Order". This makes the statement far less clear than your previous statement.[/quote]Having aliases and not using them was a typo. I've since fixed it. There is nothing wrong with having a table named Order and you are not required to enclose it in square brackets. My whole point is that people are complaining about bad use of aliases when simply not using an alias would make the query far clearer. Again, there seems to be this notion that we should use clunky column names to counter sloppy code. There are many tools that can be used to refactor a query so that it is far more readable including a replacement of aliases with better names so that going down the route of bad column names is not necessary.[/quote]We're talking about clarity and your example was anything but. By not using aliases, the query can quickly become difficult to read, especially if you have long table names, many columns and some table or column names that are reserved words. You might not HAVE to have a table named Order in brackets, but if you're going for clarity then leaving them off is a step in the wrong direction. Naming the table something other than Order is a step toward clarity.[quote][b]Thomas-282729 (6/8/2010)[/b][hr][quote][b]Stefan Krzywicki (6/8/2010)[/b][hr]The foreign key should have the same name in the referring table as it does in the data's parent table. EmployeeID should be EmployeeID everywhere in the database for clarity. All the other instances you mention should be another field descriptor or evident from context of the table in which it is a foreign key. If you start renaming fields like this, other people are going to be very confused when working with this database, especially if you haven't left good documentation and someone new inherits this.[/quote]By far the best documentation is the DRI. In most cases, it will be the case that the foreign key happens to have the entity to which it references (e.g. CustomerId in the Order table). If there is any question about what column in the parent table is referenced, a developer can simply inspect a database diagram. I've walked into plenty of database WTFs and I never rely on the column names representing the relationship to which they appear. I've built and developed against both naming schemes and frankly it is simpler to code against a system that does not prefix the table name in the Id column of the primary key(e.g. Id instead of PersonId, FooId, BarId). [/quote]I disagree. DRI is nice when it exists, but nothing beats a database diagram and notes from the designer explaining what the fields are and what the relationships are. If you've never inherited a database where the relationships are just implied instead of explicitly declared, you're very lucky. When you come upon a database where the creator didn't know how to declare references and decided to name the Employee ID - ID and then referenced it as EmployeeID, ManagerID, etc... with no documentation left behind, you're well on your way to a complete redesign with lots of business user meetings.It may be simpler for you to code against a system that doesn't prefix the table name in the Id column of the primary key, but your experiences are not universal ones. Simply naming the field ID can lead to confusion in a complex query where you have 5 tables each with a column named ID. Developers are going to use aliases whether you like it or not and they're going to end up with bad queries if you've created a confusing structure.[quote]The name of the column should represent what it is, not the context in which it belongs. Why stop at the primary key column? Why not PersonName, PersonPhone, PersonAddress and so on? What do you do in a self-join? EmployeeId and ManagerEmployeeId in the same table? How is it that a PK of PersonId adds clarity and is good but a column named PersonName does not and is bad?IMO, for the same reason that PersonName is a poor choice for a column name in the Person table, so is PersonId in the same table.[/quote]Why stop at the primary key column? Because ID is likely the only column that's going to be in every table. You're very unlikely to have lots of "Phone" columns or "MaritalStatus" columns. If you do, you're likely designing badly.Self-Joins are a simple exception, there's rarely a rule that fits 100% of the time, but EmployeeID and EmpParentID are clear.PersonName might be a good column name if you have lots of tables with "Name" columns, though I'd more likely have FirstName, LastName columns. It isn't always about the compiler, it is about the humans that read the table schemas to decide what to do and who use them in interacting with the database. You could name everything random strings and the compiler wouldn't have any problems with it. Heck, you can actually name everything with unicode pipe symbols and the compiler doesn't have any problem with it. You have to take the users into account.Tue, 08 Jun 2010 10:02:08 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote]While it may be "more logical", it isn't really within your power to ensure the developers code things your way. One of the main reasons for aliases is to keep from having to type full table names over and over. And the only place you're going to want to preface the field with the table name in most cases is the Primary Key of the column so it will make sense in joins and when used as a foreign key.[/quote]Again, that is the problem of the developer. There is no end of havoc that can be wrought by a bad developer. I'm not against using table aliases. I just do not want to bend over backwards with redundant column names to defend against the practice of using **poor** aliases.[quote]Except that in your example there, you alias the tables, but then don't use them and have apparently named one of your tables a reserved word "Order". This makes the statement far less clear than your previous statement.[/quote]Having aliases and not using them was a typo. I've since fixed it. There is nothing wrong with having a table named Order and you are not required to enclose it in square brackets. My whole point is that people are complaining about bad use of aliases when simply not using an alias would make the query far clearer. Again, there seems to be this notion that we should use clunky column names to counter sloppy code. There are many tools that can be used to refactor a query so that it is far more readable including a replacement of aliases with better names so that going down the route of bad column names is not necessary.[quote]The foreign key should have the same name in the referring table as it does in the data's parent table. EmployeeID should be EmployeeID everywhere in the database for clarity. All the other instances you mention should be another field descriptor or evident from context of the table in which it is a foreign key. If you start renaming fields like this, other people are going to be very confused when working with this database, especially if you haven't left good documentation and someone new inherits this.[/quote]By far the best documentation is the DRI. In most cases, it will be the case that the foreign key happens to have the entity to which it references (e.g. CustomerId in the Order table). If there is any question about what column in the parent table is referenced, a developer can simply inspect a database diagram. I've walked into plenty of database WTFs and I never rely on the column names representing the relationship to which they appear. I've built and developed against both naming schemes and frankly it is simpler to code against a system that does not prefix the table name in the Id column of the primary key(e.g. Id instead of PersonId, FooId, BarId). The name of the column should represent what it is, not the context in which it belongs. Why stop at the primary key column? Why not PersonName, PersonPhone, PersonAddress and so on? What do you do in a self-join? EmployeeId and ManagerEmployeeId in the same table? How is it that a PK of PersonId adds clarity and is good but a column named PersonName does not and is bad?IMO, for the same reason that PersonName is a poor choice for a column name in the Person table, so is PersonId in the same table.Tue, 08 Jun 2010 09:29:25 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]Thomas-282729 (6/7/2010)[/b][hr]You must supply the table name/alias and therefore the context to understand their meaning. "CustomerName" on a table called "Customer" is redundant as we would reference the column as "Customer.CustomerName". It is far more logical to have "Customer.Name" The problem comes when developers choose poor aliases:[code="sql"]Select X.Id, Y.Name, Z.DescriptionFrom Order As X Join Customer As Y On ... Join Product As Z On...[/code][/quote]While it may be "more logical", it isn't really within your power to ensure the developers code things your way. One of the main reasons for aliases is to keep from having to type full table names over and over. And the only place you're going to want to preface the field with the table name in most cases is the Primary Key of the column so it will make sense in joins and when used as a foreign key.[quote]If instead, they used the table name the context would be obvious: [code="sql"]Select Order.Id, Customer.Name, Product.DescriptionFrom Order As X Join Customer As Y On ... Join Product As Z On...[/code][/quote]Except that in your example there, you alias the tables, but then don't use them and have apparently named one of your tables a reserved word "Order". This makes the statement far less clear than your previous statement.[quote]Furthermore, what happens when a given foreign key column has multiple meanings? For an employee we might have Id, ManagerId, HumanResourceRepId, UnionRepId. Is the OP really suggesting EmployeeId, ManagerEmployeeId, HumanResourceRepEmployeeId and UnionRepEmployeeId?! Again, this redundant. The columns should be named for what they represent to the entiy in which they are contained with the (IMO very safe) assumption that you know the context or entity to which they belong in order to know what they mean. [/quote]The foreign key should have the same name in the referring table as it does in the data's parent table. EmployeeID should be EmployeeID everywhere in the database for clarity. All the other instances you mention should be another field descriptor or evident from context of the table in which it is a foreign key. If you start renaming fields like this, other people are going to be very confused when working with this database, especially if you haven't left good documentation and someone new inherits this.Tue, 08 Jun 2010 06:24:02 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxTo some extent I agree.There is no need to have CustomerName in the Customer table but with columns such as ID I would never rely just on that. I would call it CustomerID if it was the PK for that table.Then, when it comes to FKsOrders.CustomerID makes sense and all columns which reference CustomerID have the same name everywhere rather than being ID in the parent table and <parentTableName>ID in child tables.Tue, 08 Jun 2010 04:30:35 GMTRob Fiskhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxI agree with most of the article except for this statement:"The instant you start writing queries that join tables together ID, name and description are taken outside of their context and are therefore ambiguous and not descriptive of their purpose."Completely, totaly disagree. It is *not possible* to take these out of context as they must *always* be in the context of the entity. Suppose we have the following:[code="sql"]Select Id, Name, DescriptionFrom Table1 Join Table2 ....[/code]This query will not compile in any DBMS of which I'm aware. You must supply the table name/alias and therefore the context to understand their meaning. "CustomerName" on a table called "Customer" is redundant as we would reference the column as "Customer.CustomerName". It is far more logical to have "Customer.Name" The problem comes when developers choose poor aliases:[code="sql"]Select X.Id, Y.Name, Z.DescriptionFrom Order As X Join Customer As Y On ... Join Product As Z On...[/code]If instead, they used the table name the context would be obvious: [code="sql"]Select Order.Id, Customer.Name, Product.DescriptionFrom Order Join Customer On ... Join Product On...[/code] Furthermore, what happens when a given foreign key column has multiple meanings? For an employee we might have Id, ManagerId, HumanResourceRepId, UnionRepId. Is the OP really suggesting EmployeeId, ManagerEmployeeId, HumanResourceRepEmployeeId and UnionRepEmployeeId?! Again, this redundant. The columns should be named for what they represent to the entiy in which they are contained with the (IMO very safe) assumption that you know the context or entity to which they belong in order to know what they mean. Look at the counter to this, the presumption with having "ProductId", is that you might have an OrderId but no context. Does that represent a ProductId on an order? A ProductId in an assembly? The product itself? Without the context to which the column belongs, having the column by itself is useless.Mon, 07 Jun 2010 23:26:28 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxMy thanks to Paul for his help in resolving the SQL2005 conundrum.It was my bad. SQL2005 does reclaim space when the clustered index is rebuilt.SQL2000 doesn't and this I HAVE definitely checked (on multiple boxes).I still maintain that it is better to get the data types and sizes right very early on.On the Table.ID issue, what do you do if you have one:one relationships in your DB? How can you tell if two tables can relate together?Thu, 27 May 2010 10:12:15 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxCinderella data users!Oh how I laughed so long I cried.The actual crying is pretty much done with now. That was almost the entire company when I joined. Every department with their own collection of access front ends and spreadsheets, though admittedly the access had a sql server back end.Unfortunately access was the, cough, design, cough tool to create the DBs and the people doing it wouldn't know a naming convention or foreign key if it bit them on the arse.I still have the list of distinct column names for membership number across the tables in one of the DBsmemnoMembership Number:mem no:txtMemNocboMemnocboMemNomem #:Thu, 27 May 2010 09:47:52 GMTRob Fiskhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxDavid, assuming you meant Jeff Moden, not Jeff Modem? Although now I'll be hearing that old-school crackle and squeal of the dialup modem every post he makes...:-DThu, 27 May 2010 08:09:03 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxHehe sorry, I was going back-and-forth between work and writing so I wasn't focused. In all fairness, I would have made the same mistake regardless of which method I used because the only difference would have been "[Order].CustomerID" vs. "[Order].Customer_ID". Having written "Order.ID", I had "order ID" on my mind so I would have written [Order].Order_ID using your naming.I do see your point, however, with regards to being able to accidentally join the Customer table with the Order.ID instead of Order.CustomerID.Cheers,CyleThu, 27 May 2010 03:00:58 GMTsipe_16http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]sipe_16 (5/27/2010)[/b][hr]If you wanted to be super-clear you could skip the aliases and simply:[code="sql"]select [Order].ID, Customer.ID from dbo.[Order] inner join dbo.Customer on Customer.ID = [Order].ID[/code][/quote]Nope, doesn't make it easier for me, especially since the [Order].CustomerID was accidentally changed into [Order].IDI prefer <Tablename>.<Tablename>_ID to avoid errors like these. Clarity above brevity. But that's just what works best for me.Thu, 27 May 2010 02:49:38 GMTgserdijnhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxEverything I used to write that had to do with the database was in long procedures due to requirements from the client, but I suppose that since jumping on the "database is just a persistant storage" (please take that with a huge grain of salt) bandwagon I've started reducing the amount of SQL written and moving more into the DAL, so the "large blocks of SQL" have become much less of an issue (I feel that this is the way things are moving...). On the other hand though, aliases have always been very clear in the large procedures which I've inherited; It's actually the poor naming of variables and parameters used throughout that lead to tylenol abuse, hehe.I'm sure that my point of view simply stems from my programming background :-) It's always nice to hear what others are doing and why. IMO I don't think that there's a right or wrong way regarding this, or an easier or a more difficult way, because I've used both; As long as the entire database follows the same standard then everything works out (unfortunately, the databases I'm currently working with have hundreds of tables, and there's no standard whatsoever!... ugh!).Cheers,Cyle@ The "customer_id" example:I still don't see how the fowing scenario could be confusing...PK Table: CustomerPK: IDFK Table: OrderPK: IDFK: CustomerID[code="sql"]select ord.ID, cust.ID from dbo.[Order] ord inner join dbo.Customer cust on cust.ID = ord.CustomerID[/code]For me, if i were to write "cust.CustomerID" it feels to me like cust.CustomerID is referencing another table, whereas "cust.ID" feels more like i'm referencing the ID of the customer. If you wanted to be super-clear you could skip the aliases and simply:[code="sql"]select [Order].ID, Customer.ID from dbo.[Order] inner join dbo.Customer on Customer.ID = [Order].CustomerID[/code]Of course you'd still have to use aliases if you were referencing the same table twice...I think both approaches have their pro's and cons. Using TableNameID gives you consistency with that PK throughout the DB, while using ID gives a sense of origination when you're referencing it (at a glance you know if the referenced table has the PK or if it's an FK).Thu, 27 May 2010 02:31:36 GMTsipe_16http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxAny day I can open my email and see a new David Poole article, I know it's going to be a good day!Your comment made me think:[quote]Jeff Modem made the comment that people write time bomb code "because there's some sort of guarantee that there will never be more than some small number of rows in the table".A senior colleague opined that designing a system that isn't expected to grow is effectively designing a system that the business already regards as a failure before the first line of code is written![/quote]I was flipping through the news the other day and I saw a Congressman grilling a Wall Street trader. The Congressman asked the trader, "Do you know what IWBH/YWBH means?"After hedging and stalling, and a few reiterations of the question, the trader finally admitted it was shorthand for "I Won't Be Here/You Won't Be Here," a slogan a lot of traders said to each other when they were making instantly profitable deals with very bad repercussions for everyone else in the known universe.I think a lot of time bomb code is ordered by managers, spec'd by business analysts, designed by architects and built by developers who suffer from this same outlook on the future.Thanks for the great article David!MichaelWed, 26 May 2010 18:55:18 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]Stefan Krzywicki (5/26/2010)[/b][hr][quote][b]eric_russell 13013 (5/26/2010)[/b][hr]Perhaps my biggest data modeling pet peve is when date/time values are contained in a VarChar column. Not only does this consume 20+ bytes, compared to only 4 bytes for SmallDateTime, but often times the values are sporatically miscoded with invalid dates or multiple applications code the dates in multiple formats, resulting in data type mismatch errors and convoluted case expressions in the SQL statement to decode the data. The reason typically provided for this is that different applications need to display the date in different formats. Of course, this can be better achieved by storing dates as Date in the database using a formatting mask on the application form controls.[/quote]There is a valid reason to use varchar to store dates, though it doesn't come up often, especially in businesses in America. Before SQL Server 2008, the earliest date you could store in a date field was January 1, 1753. If you're creating a database that includes dates older than that, your only recourse was a varchar field. I've created databases for historical events and fictional events that couldn't use a date field for this very reason. And there are businesses in other parts of the world that have accounts that date back before 1753, so it could theoretically come up then too.With 2008, you can enter any date in AD through December 31, 9999. Even most SF won't give you a problem there. I suppose for BC you can add a flag to avoid needing to store it in Varchar.[/quote]Several years ago, I developed a table with over 500,000,000 rows and which received 100,000s of inserts per day. We were only concerned about the date the rows were inserted, so we stored the insert date as an integer offset from a specific date in the past. Let's assume that Jan 1, 2010 is the offset date, so a query that returns a count of rows inserted in the past 30 days would be something like this:select @report_date_offset = datediff(day,'2010-01-01',getutcdate())-30;select count(*) row_countfrom tran_masterwhere insert_date_offset >= @report_date_offset;I recall this specific case it was a 2 byte integer (SmallInt), allowing something like 90+ forward years of dates, and it only referred to forward dates. However, a 4 byte integer could point to practically any date forward or backward in time. To refer to dates prior to the offset date, you would use integers < 0.Of course, now we have the Date datatype in SQL Server 2008 which is 3 bytes and supports range from jan 1, 0001 - year 9999.Wed, 26 May 2010 14:38:00 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]eric_russell 13013 (5/26/2010)[/b][hr]Perhaps my biggest data modeling pet peve is when date/time values are contained in a VarChar column. Not only does this consume 20+ bytes, compared to only 4 bytes for SmallDateTime, but often times the values are sporatically miscoded with invalid dates or multiple applications code the dates in multiple formats, resulting in data type mismatch errors and convoluted case expressions in the SQL statement to decode the data. The reason typically provided for this is that different applications need to display the date in different formats. Of course, this can be better achieved by storing dates as Date in the database using a formatting mask on the application form controls.[/quote]There is a valid reason to use varchar to store dates, though it doesn't come up often, especially in businesses in America. Before SQL Server 2008, the earliest date you could store in a date field was January 1, 1753. If you're creating a database that includes dates older than that, your only recourse was a varchar field. I've created databases for historical events and fictional events that couldn't use a date field for this very reason. And there are businesses in other parts of the world that have accounts that date back before 1753, so it could theoretically come up then too.With 2008, you can enter any date in AD through December 31, 9999. Even most SF won't give you a problem there. I suppose for BC you can add a flag to avoid needing to store it in Varchar.Wed, 26 May 2010 14:03:47 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxOutstanding article. Thanks. Lots of good practical ideas, well justified by example and logic.I am going to file this one away in the Best Practices folder and refer back to it often.Wed, 26 May 2010 13:41:11 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxAgreed! :))Wed, 26 May 2010 12:13:52 GMTlastory2006http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxNice article. Thanks.Wed, 26 May 2010 11:56:41 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]lastory2006 (5/26/2010)[/b][hr] BTW - in the half-page/full-page example - just use varchar and be done with it, while at the same time taking up minimal storage space.[/quote]The point of the char was to demonstrate what happens when people are sloppy with inappropriate data types i.e. 2 bytes can make a lot of difference.Anyone who uses CHAR(4040) should be locked up until they can explain the plot of "Lost" to my mum.Wed, 26 May 2010 11:45:41 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxActually, the author's guess that inflation will not exceed 3 digits is rather similar to what the 20th century developers did by using 2 digits for year, guessing that their apps will not live to y2k. In the inflation scenario, what is ignored is the possibility of hyperinflation, a small chance, but a chance nonetheless. We wouldn't be the first, and history of just the past 100 years shows several precedents. Just an example of guessing about maximum data size in designing the data model. BTW - in the half-page/full-page example - just use varchar and be done with it, while at the same time taking up minimal storage space.Wed, 26 May 2010 11:23:18 GMTlastory2006http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxI use the same naming convention as Russell for ID fields, and for much the same reasons it sounds like. From a maintenance standpoint, it's much easier for the average human to understand that customer_id in the customer table and customer_id in the order table are refering the same value, and a related to each other. Especially if you're looking at the table, and not a query!My personal pet peeve was when a contractor converted one of our databases, and renamed every single primary key field in every table from my carefully named version to "id". So none of my older queries worked - I had to modify every single one of them. And the queries THEY wrote were "less than understandable", aliases or not.I'm a fan of naming conventions. Loved all the examples of time bombs; I've experience a few of them, although most of our databases aren't large enough to get some of the symptoms (no tera-bytes, here).Looking forward to David's next article!Wed, 26 May 2010 10:33:47 GMTStephanie J Brownhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxWe can debate about [customer_id], versus [CustomerNumber], versus [Cust_Num], but at a minimum the foreign key names should match the primary key names, and to do that, the primary key names must be something unqiue and preferably descriptive, not just [ID]. To make things simple, I just name the identifier column after the table name + _id. So the identifier for the [customer] table would be [customer_id], and for the [customer_contact] table, it would be [customer_contact_id].Wed, 26 May 2010 09:45:44 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx@sipe_16When you use an ORM tool it effectively hides the schema so you can get away with calling the fields almost anything.When you are reading down a large block of SQL then it is easy to get the wrong ID, name, description etc, particularly in the FROM clause.The fundamental point is that the name of something should be clear in its intent and meaning. Aliasing deals with it to some extent but underneath it all it becomes confusing as to whether ID means CustomerID, ProductID, OrderID, SalesmanID, RegionID....etcMind you, even ID is preferable to the DB I've inherrited that has a primary key called "NoUseWhatSoEver"Wed, 26 May 2010 09:25:39 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxExcellent series of articles. sums up a LOT of what I have seen and been yelling at people for years over...Wed, 26 May 2010 09:20:08 GMTPaul Muharsky-474732http://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxSee if executing a CHECKPOINT updates your allocation stats.Wed, 26 May 2010 09:18:02 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspx[quote][b]David.Poole (5/26/2010)[/b][hr]What am I doing wrong Paul? I don't seem to get any space back (in SQL2005) other than that I would get by defragging the index.[/quote]What are you using to measure the space you get back, David?Do you see any space being reclaimed? Does the large table shrink to the size of the small table?If you can give me enough detail so I can replicate what you are seeing, I'll happily investigate! :-)When you say 'defragging the index', do you mean you are running ALTER INDEX REORGANIZE / DBCC INDEXDEFRAG?The article only mentioned a full rebuild of the index, so I concentrated on that (the 'defrag' routines work a bit differently).Anyhow, if you have a few minutes to confirm the details, that'll help a lot.PaulWed, 26 May 2010 09:01:27 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxsometime last year in one of the linkedin groups i'm there was a huge religious battle between GUID PK supporters and the rest of us. after a while people got tired of it because the GUID side liked GUID's just becauseMS is guilty of some of this stuff as well. this year we set up perfmon to run and collect data from numerous servers and log to a database. the schema is horrendous and i had to do a lot of casting and converting to be able to read the dataWed, 26 May 2010 08:23:01 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxSometimes people make poor data type choices or implement redundant columns due to misguided notions about best practices. For example, I once was hired as a contractor to delevepe reports for a data warehousing project. The first thing that really jumped out at me was that every integer column in the fact tables was defined as BigInt.[sex] bigint not null,[marital_status] bigint not null,[center_id] bigint not null,...about 30 other bigint indicators or foreign key columns...Rather than 8 byte Big integers, for most of these columns, a single byte TinyInt or 2 byte SmallInt would have been more than enough. When I asked the DBA / data modeler why he had chosen BigInt for all these columns, his reply was that they were using the 64bit edition of SQL Server Enterprise and therefore 64 bit integers would perform better. He could offer no performance test results or even links to a single white paper to back that assertion. However, he did have bragging rights for owning the largest data warehouse in the agency's history. Another example is creating a clustered index on something like LastName, FirstName, PhoneNumber. This not only leads to page splits, fragmentation, and bloated indexes, but also excessive I/O and blocking. Other things I've seen are GUIDs used as unqiue surrogate key when a 4 byte integer would have been more appropriate or even a GUID column, which seems to serve no purpose, in addition to an integer surrogate key. Perhaps my biggest data modeling pet peve is when date/time values are contained in a VarChar column. Not only does this consume 20+ bytes, compared to only 4 bytes for SmallDateTime, but often times the values are sporatically miscoded with invalid dates or multiple applications code the dates in multiple formats, resulting in data type mismatch errors and convoluted case expressions in the SQL statement to decode the data. The reason typically provided for this is that different applications need to display the date in different formats. Of course, this can be better achieved by storing dates as Date in the database using a formatting mask on the application form controls.Wed, 26 May 2010 08:13:51 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic927945-60-1.aspxNice (lengthy) article with great examples!Wed, 26 May 2010 07:56:07 GMTKoen Verbeeck