January 23, 2015 at 9:23 am
ScottPletcher (1/23/2015)
Phil Parkin (1/23/2015)
Steve Jones - SSC Editor (1/23/2015)
I tend to use ID, but that's I think my OCD coming in. ID is an acronym. Product is a word.ProductId would look funny to me.
Acronyms are abbreviations which themselves are pronounced as words (Eg, NATO).
ID is an initialism for Identity document. It's not an acronym.
Not, it isn't. ProductId stands for product identifier. To what "document" are you referring? Most manufacturing shops use CAD rather than actual documents now anyway.
Edit:
Same with CustomerId. Are you trying to say that means "Customer Identity Document" also??
You are highlighting the inconsistency. Of course I know that ProductID is short for Product Identifier. But applying consistency rules, it should be ProductId.
As Koen has already mentioned, what about DriverID? That really could be a Driver Identity Document and ambiguity is a definite possibility.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 23, 2015 at 9:32 am
Phil Parkin (1/23/2015)
ScottPletcher (1/23/2015)
Phil Parkin (1/23/2015)
Steve Jones - SSC Editor (1/23/2015)
I tend to use ID, but that's I think my OCD coming in. ID is an acronym. Product is a word.ProductId would look funny to me.
Acronyms are abbreviations which themselves are pronounced as words (Eg, NATO).
ID is an initialism for Identity document. It's not an acronym.
Not, it isn't. ProductId stands for product identifier. To what "document" are you referring? Most manufacturing shops use CAD rather than actual documents now anyway.
Edit:
Same with CustomerId. Are you trying to say that means "Customer Identity Document" also??
You are highlighting the inconsistency. Of course I know that ProductID is short for Product Identifier. But applying consistency rules, it should be ProductId.
As Koen has already mentioned, what about DriverID? That really could be a Driver Identity Document and ambiguity is a definite possibility.
That's a bit of a stretch. *IF* it really does stand for "Identity *Document*", then, yes, it should be ID. That's again why I strongly prefer underscores, so the word breaks are clear and don't have to implied, esp. when such acronyms / abbrevs are used together. How would I name a NATO identity document?:
NATOID
But is that "NAT" "OID"? Etc.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 23, 2015 at 9:35 am
I think that ID as an acronym has a lot of meanings, including Identity Data or Identifying Data.
However, either way, I happily admit that I'm not necessarily consistent here. Orders and OrderDetails instead of OrdersDetails.
January 23, 2015 at 9:35 am
That's a bit of a stretch. *IF* it really does stand for "Identity *Document*", then, yes, it should be ID. That's again why I strongly prefer underscores, so the word breaks are clear and don't have to implied, esp. when such acronyms / abbrevs are used together. How would I name a NATO identity document?: NATOID But is that "NAT" "OID"? Etc.
This is the one exception that I have and use underscores. In this case, NATO_ID. But I don't run into this very often and am not going to change everything to handle this exception.
January 23, 2015 at 9:37 am
I suppose my usage of ID comes partly from C# programming, and it depends on the naming convention used in the database (which is almost never under my control so I will not express an opinion on that):
Camel case: productId
Pascal case: ProductId
Lowercase: product_id
Uppercase: PRODUCT_ID
English: Product ID
The last one is a pet peeve of mine. When writing English documentation (including code comments) and the code is not being quoted directly, there is only one correct casing: ID. For example, "This procedure takes a product ID and returns details about the product." (not a terribly useful code comment, repeating the obvious, but it serves as an illustration)
The argument here seems to be whether Pascal case of ID is Id or ID. Consider how you would write XML:
Camel case: sampleXml
Pascal case: SampleXml
Lowercase: sample_xml
Uppercase: SAMPLE_XML
English: Sample XML
This doesn't look right to me: sampleXML
XML is clearly an all-caps acronym, but it doesn't case that way in code.
January 23, 2015 at 9:43 am
Phil Parkin (1/23/2015)
ScottPletcher (1/23/2015)
Phil Parkin (1/23/2015)
Steve Jones - SSC Editor (1/23/2015)
I tend to use ID, but that's I think my OCD coming in. ID is an acronym. Product is a word.ProductId would look funny to me.
Acronyms are abbreviations which themselves are pronounced as words (Eg, NATO).
ID is an initialism for Identity document. It's not an acronym.
Not, it isn't. ProductId stands for product identifier. To what "document" are you referring? Most manufacturing shops use CAD rather than actual documents now anyway.
Edit:
Same with CustomerId. Are you trying to say that means "Customer Identity Document" also??
You are highlighting the inconsistency. Of course I know that ProductID is short for Product Identifier. But applying consistency rules, it should be ProductId.
As Koen has already mentioned, what about DriverID? That really could be a Driver Identity Document and ambiguity is a definite possibility.
That's my point, to remove the ambiguity. If you are consistent and use DriverId to represent Driver Identifier and DriverID to represent Driver Identity Document, there is no ambiguity, which is what you want when naming things.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 23, 2015 at 9:44 am
I'm stunned Celko hasn't weighed in and told us how to name things properly rather than relying on punched cards and tape naming!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 23, 2015 at 10:06 am
Now on to data types.
Currency: should it be decimal, numeric, money, int?
Date/Time: datetime, offset, varchar?
Names and descriptions: varchar or nvarchar?
Unique Identifiers: int, guid, timestamp, rowid, identity, sequence? :rolleyes:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 23, 2015 at 10:41 am
Eric M Russell (1/23/2015)
Currency: should it be decimal, numeric, money, int?
Definitely not int. That is unacceptable because you either have to divide by 100.0 all the time or your loose the fractional portion of the transaction. Decimal and numeric are the exact same datatype with two names and money is holdover from years gone by.
Date/Time: datetime, offset, varchar?
There is only one option here. Datetime. Varchar is strictly off limits as it is just completely wrong. This is like storing live chickens in an empty swimming pool. Sure it can be done but it the wrong tool for the job. Offset is sort of ok but I would prefer to store the datetime as server time and an additional column to hold the offset for the current user.
Names and descriptions: varchar or nvarchar?
There is no right or wrong answer here. It depends on the application and the audience as to what is appropriate.
Unique Identifiers: int, guid, timestamp, rowid, identity, sequence?
Well uniqueidentifier is a datatype so I assume you mean primary key.
int is fine. This could be either entered or an identity and can span more than just int.
guid = uniqueidentifier and is perfectly fine with the caveat that it must NOT be clustered index. There are times when it is required to use this as the primary key. If not needed I find them to be a total PITA to work with because debugging is so much more difficult when you have to copy and paste values instead of just typing them.
Datetime - absolutely not. This is a completely arbitray thing that makes no sense in any real situation. Would you create a foreign key based on a datetime value? It just doesn't work and it offers no clarity to what is being referenced.
Sequence - this is no different logically than an int or identity, the difference is in how it is generated.
rowid - this is from oracle and doesn't exist in sql server. The closest approximation here would be an identity.
There is my 2¢
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2015 at 11:08 am
ScottPletcher (1/23/2015)
Phil Parkin (1/23/2015)
Steve Jones - SSC Editor (1/23/2015)
I tend to use ID, but that's I think my OCD coming in. ID is an acronym. Product is a word.ProductId would look funny to me.
Acronyms are abbreviations which themselves are pronounced as words (Eg, NATO).
ID is an initialism for Identity document. It's not an acronym.
Not, it isn't. ProductId stands for product identifier. To what "document" are you referring? Most manufacturing shops use CAD rather than actual documents now anyway.
Edit:
Same with CustomerId. Are you trying to say that means "Customer Identity Document" also??
From what I can find (e.g. Mirriam Websters and abbreviations.com), the proper abbreviation for identify, identification, and identity is ID.
That said, I don't always use it that way and do revert to the Id form quite often.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 23, 2015 at 11:20 am
Sequence - this is no different logically than an int or identity, the difference is in how it is generated.
It may not be different logically, but if it's important to have unique identifiers throughout the database, or even among certain tables, it can be useful. I've never really had that situation, but I have it in mind if needed in the future.
January 23, 2015 at 11:52 am
RonKyle (1/23/2015)
Sequence - this is no different logically than an int or identity, the difference is in how it is generated.
It may not be different logically, but if it's important to have unique identifiers throughout the database, or even among certain tables, it can be useful. I've never really had that situation, but I have it in mind if needed in the future.
Exactly, that is what they are for but it is still just an int. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2015 at 12:46 pm
Sean Lange (1/23/2015)
Eric M Russell (1/23/2015)
Date/Time: datetime, offset, varchar?There is only one option here. Datetime.
I suppose if your dBMS is anccient enough that's excusable. But it probably isn't if Datetime2 is available.
Tom
January 23, 2015 at 12:50 pm
TomThomson (1/23/2015)
Sean Lange (1/23/2015)
Eric M Russell (1/23/2015)
Date/Time: datetime, offset, varchar?There is only one option here. Datetime.
I suppose if your dBMS is anccient enough that's excusable. But it probably isn't if Datetime2 is available.
An integer is also possible, and probably best in specific cases, such as joining to an existing column that also uses an integer and is already indexed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 23, 2015 at 1:16 pm
ScottPletcher (1/23/2015)
TomThomson (1/23/2015)
Sean Lange (1/23/2015)
Eric M Russell (1/23/2015)
Date/Time: datetime, offset, varchar?There is only one option here. Datetime.
I suppose if your dBMS is anccient enough that's excusable. But it probably isn't if Datetime2 is available.
An integer is also possible, and probably best in specific cases, such as joining to an existing column that also uses an integer and is already indexed.
I agree that VarChar based "datetime" columns are perhaps one of worst SQL anti-patterns. Even when formatted consistently, it suffers from storage and perforance inefficiencies. In a team environment where there are multiple ETL processes and applications inserting the table, somebody will eventually goof it up.
In addition to something like a DateKey smallint, there have been a few occasions where I've implemented an integer based time offset. I'm not talking about the new standardized datetimeoffset datatype. In this scenario I was using a regular smallint or tinyint column to indicate minutes or seconds elapsed since the ETL load started. So each record in transaction table had a LoadID (which could be used to join back to LoadStartTime), and each transactional record also had LoadOffsetSeconds. I did it that way to prevent having a full 8 byte datetime on each transactional record.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 61 through 75 (of 103 total)
You must be logged in to reply to this topic. Login to reply