December 11, 2017 at 12:11 pm
Eric M Russell - Monday, December 11, 2017 11:57 AMWhen I'm wearing the DBA hat, I really don't comment much at all regarding the application developer's choice of naming convention, because ultimately they are the one's who have to code against it. I'd rather focus on normalization and optimization.
I would tend to agree with this, as long as the table name gives a general idea of what's in it i don't really care if it's called say SALES_ORDER or SALES_ORDERS.
December 11, 2017 at 12:15 pm
Phil Parkin - Monday, December 11, 2017 12:11 PMEric M Russell - Monday, December 11, 2017 11:57 AMWhen I'm wearing the DBA hat, I really don't comment much at all regarding the application developer's choice of naming convention, because ultimately they are the one's who have to code against it. I'd rather focus on normalization and optimization.You are, however, the one who gets to look at execution plans, traces, logs and all sorts of other SQL Server paraphernalia, none of which have aliasing!
Yeah, but we investigate those things in one off SQL code, and typing aliases is a fairly substantial burden.
412-977-3526 call/text
December 11, 2017 at 12:26 pm
robert.sterbal 56890 - Monday, December 11, 2017 12:15 PMPhil Parkin - Monday, December 11, 2017 12:11 PMEric M Russell - Monday, December 11, 2017 11:57 AMWhen I'm wearing the DBA hat, I really don't comment much at all regarding the application developer's choice of naming convention, because ultimately they are the one's who have to code against it. I'd rather focus on normalization and optimization.You are, however, the one who gets to look at execution plans, traces, logs and all sorts of other SQL Server paraphernalia, none of which have aliasing!
Yeah, but we investigate those things in one off SQL code, and typing aliases is a fairly substantial burden.
+1
December 11, 2017 at 12:27 pm
ZZartin - Monday, December 11, 2017 12:11 PMEric M Russell - Monday, December 11, 2017 11:57 AMWhen I'm wearing the DBA hat, I really don't comment much at all regarding the application developer's choice of naming convention, because ultimately they are the one's who have to code against it. I'd rather focus on normalization and optimization.I would tend to agree with this, as long as the table name gives a general idea of what's in it i don't really care if it's called say SALES_ORDER or SALES_ORDERS.
+1
December 11, 2017 at 12:37 pm
robert.sterbal 56890 - Monday, December 11, 2017 12:15 PMPhil Parkin - Monday, December 11, 2017 12:11 PMEric M Russell - Monday, December 11, 2017 11:57 AMWhen I'm wearing the DBA hat, I really don't comment much at all regarding the application developer's choice of naming convention, because ultimately they are the one's who have to code against it. I'd rather focus on normalization and optimization.You are, however, the one who gets to look at execution plans, traces, logs and all sorts of other SQL Server paraphernalia, none of which have aliasing!
Yeah, but we investigate those things in one off SQL code, and typing aliases is a fairly substantial burden.
You have highlighted my point: it's worth getting the name right in the first place and not having to rely on aliases to make things clear.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 11, 2017 at 12:50 pm
Phil Parkin - Monday, December 11, 2017 12:37 PMrobert.sterbal 56890 - Monday, December 11, 2017 12:15 PMPhil Parkin - Monday, December 11, 2017 12:11 PMEric M Russell - Monday, December 11, 2017 11:57 AMWhen I'm wearing the DBA hat, I really don't comment much at all regarding the application developer's choice of naming convention, because ultimately they are the one's who have to code against it. I'd rather focus on normalization and optimization.You are, however, the one who gets to look at execution plans, traces, logs and all sorts of other SQL Server paraphernalia, none of which have aliasing!
Yeah, but we investigate those things in one off SQL code, and typing aliases is a fairly substantial burden.
You have highlighted my point: it's worth getting the name right in the first place and not having to rely on aliases to make things clear.
It I'm looking at a table that is used in production, the name isn't want concerns me, it is having to type it over and over.
I'm in an atypical situation where I have to remote into servers that are owned by the customer so I don't have tools that really work consistently. Brevity matters.
412-977-3526 call/text
December 11, 2017 at 1:43 pm
I've used singular going on 27 years now, initially because that is what I first read in white papers, best practices and the first databases I inherited. And as I gained more experience it simply made sense to me from a modeling, consistency and brevity perspective all mentioned previously in this thread.
A pluralist commonly argues that a Customer table is a table of Customers, therefore it should be named Customers to reflect that collection. However, I look at it as a singular object with properties. And the notion of plurality can be countered when looking at the collection more colloquially. For example, we call it a Newspaper Stand, not a Newspapers Stand; Or Airplane Hangar, not an Airplanes Hangar; Or Book Store, not Books Store, etc.
The reserved word argument has merit against singularity, but I try to avoid reserved words at all costs and I treat it as a necessary trade-off to pluralizing every table with an S, ES or IES.
I also name my foreign keys with both the parent and child table names. A foreign key named fk_CustomerOrder is more natural and makes more sense than fk_CustomersOrders. But that may just be personal preference for me.
Regarding prefixes on objects, I don't prefix tables, but have no problem with tbl or t_ prefixes as long as all tables have them. I do put a v_ prefix for all views as I want any code to be distinguished between the two. I do this not just for my own benefit, but for the benefit of anyone taking over support of a database I've created. Anyone who has assumed development or maintenance of a legacy database with hundreds of tables and views knows the importance and relief when views are prefixed and identified as such.
December 11, 2017 at 2:56 pm
richard.julian - Monday, December 11, 2017 1:43 PMI've used singular going on 27 years now, initially because that is what I first read in white papers, best practices and the first databases I inherited. And as I gained more experience it simply made sense to me from a modeling, consistency and brevity perspective all mentioned previously in this thread.
A pluralist commonly argues that a Customer table is a table of Customers, therefore it should be named Customers to reflect that collection. However, I look at it as a singular object with properties. And the notion of plurality can be countered when looking at the collection more colloquially. For example, we call it a Newspaper Stand, not a Newspapers Stand; Or Airplane Hangar, not an Airplanes Hangar; Or Book Store, not Books Store, etc.
The reserved word argument has merit against singularity, but I try to avoid reserved words at all costs and I treat it as a necessary trade-off to pluralizing every table with an S, ES or IES.
I also name my foreign keys with both the parent and child table names. A foreign key named fk_CustomerOrder is more natural and makes more sense than fk_CustomersOrders. But that may just be personal preference for me.
Regarding prefixes on objects, I don't prefix tables, but have no problem with tbl or t_ prefixes as long as all tables have them. I do put a v_ prefix for all views as I want any code to be distinguished between the two. I do this not just for my own benefit, but for the benefit of anyone taking over support of a database I've created. Anyone who has assumed development or maintenance of a legacy database with hundreds of tables and views knows the importance and relief when views are prefixed and identified as such.
Richard, I totally agree with you as I've been creating my models like this for over 30 years and this method has worked quite well for me, those that I work with and those that followed me. I take it a point of pride when others have no trouble navigating the data model.
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
December 11, 2017 at 8:13 pm
SELECT customer.id, customer.name, customer.email
WHERE customer.name = 'Arthur Dent'
FROM Customers AS customer
That seems to me to be lots of extra keying, at which I'm not all that good. I still like
SELECT id,
name,
email
FROM Customer
WHERE name = 'Arthur Dent'
I pretty visual, and I don't want my code to look like I'm reading a book. With multi-table joins, I just used a single character alias, like c.id, c.name. In the past I've written stored procedure processes up to 40 or 50 pages, including building and accessing several temp tables and didn't seem to have a problem. Of course, we used comments throughout the code. Steps within the process were separated by explanatory blocks of comments. Worked pretty well for us.
I see that the web page has summarily removed leading blanks from my code, which in the original had all the column names indented 8 spaces for readability.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
December 11, 2017 at 8:20 pm
ZZartin - Monday, December 11, 2017 12:11 PMEric M Russell - Monday, December 11, 2017 11:57 AMWhen I'm wearing the DBA hat, I really don't comment much at all regarding the application developer's choice of naming convention, because ultimately they are the one's who have to code against it. I'd rather focus on normalization and optimization.I would tend to agree with this, as long as the table name gives a general idea of what's in it i don't really care if it's called say SALES_ORDER or SALES_ORDERS.
And when I was a DBA, we did the naming of all tables and columns as a part of the database design, and provided them to the developers. We, as DBAs, also wrote most of the stored procedure code, and had final review of all procedures before they were implemented.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
December 13, 2017 at 2:13 am
Phil Parkin - Monday, December 11, 2017 10:18 AMLynn Pettis - Monday, December 11, 2017 10:03 AMI still use two part names, habit. Plus, what happens when the one table query becomes a multi-table query? I'd rather have the aliases already in place.I do too.
But SQL Prompt fills them in automatically, if required, when moving from 1 table to 2 or more, so it's no extra effort.
You can set specific, shorter aliases with Prompt.
November 17, 2023 at 7:41 am
A table is not an object, it's a set. A set contains zero or more elements.
Set = plural
Element = singular
Once you start thinking of a table as singular, your mind shifts to RBAR instead of set thinking.
November 17, 2023 at 10:25 am
It depends on whether it's Monday morning or Friday afternoon.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
November 17, 2023 at 1:46 pm
Once you start thinking of a table as singular, your mind shifts to RBAR instead of set thinking.
Totally disagree.
November 17, 2023 at 2:08 pm
I can't believe you folks are stewing over this. Use freaking alias and move on.
Now for my next cup of java before I get up. It's past 9:00.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
Viewing 15 posts - 61 through 75 (of 81 total)
You must be logged in to reply to this topic. Login to reply