January 27, 2015 at 7:16 am
andrew gothard (1/27/2015)
Eric M Russell (1/21/2015)
ProductID, ProductId, and product_id are all respectable options, so long as it's done consistently.However, I would take issue with ID by itself, on every table. Anytime I see that, I have low expectations for how the rest of the database is designed.
Even worse. GUID as a column on every table ...
Regardless of datatype, I don't want to see a column names having no contextual prefix, meaning just "ID" by itself, on any table, and much less on every table.
As for GUIDs, it's a natural fit for the XML document world, where data is exchanged globally, but there is no centralized global mechanism for obtaining unique and sequential identifiers. However, in the relational database world, I rarely see a strong and compelling argument for having primary or surrogate keys as type GUID. Even in an environment that uses federated databases or merge replication, simple integer based identity columns or sequences can be seeded within a different rage on each server.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 28, 2015 at 7:08 pm
Eric M Russell (1/27/2015)
andrew gothard (1/27/2015)
Eric M Russell (1/21/2015)
ProductID, ProductId, and product_id are all respectable options, so long as it's done consistently.However, I would take issue with ID by itself, on every table. Anytime I see that, I have low expectations for how the rest of the database is designed.
Even worse. GUID as a column on every table ...
Regardless of datatype, I don't want to see a column names having no contextual prefix, meaning just "ID" by itself, on any table, and much less on every table.
As for GUIDs, it's a natural fit for the XML document world, where data is exchanged globally, but there is no centralized global mechanism for obtaining unique and sequential identifiers. However, in the relational database world, I rarely see a strong and compelling argument for having primary or surrogate keys as type GUID. Even in an environment that uses federated databases or merge replication, simple integer based identity columns or sequences can be seeded within a different rage on each server.
Integer doesn't always work - 2**32 is quite a small number; but identity types can cover a much larger range, as they aren't restricted to integer, and the only imaginable justification for using a GUID is that (if you pick the right generation GUID spec) you may be globally, not just locally, unique.
I've known systems where the ID of an X was X_ID everywhere except the X table, where it was just ID. I really didn't see any problem with that, clearly ID as a column name in any join was going to need a prefix, but it didn't seem (to me anyway) to introduce any real problem (in fact it encouraged people to provide table alisases and use them in the select list, exactly the opposite of a problem). But anything I designed myself called it X_ID even in the X table, so maybe I agree with you even though a different approach is workable and in some ways useful.
Edit: I forgot to say: Anything that's a natural fit for the XML world must of course be hopelessly wrong for the relational world.
Tom
January 29, 2015 at 7:57 am
TomThomson (1/28/2015)
Eric M Russell (1/27/2015)
andrew gothard (1/27/2015)
Eric M Russell (1/21/2015)
ProductID, ProductId, and product_id are all respectable options, so long as it's done consistently.However, I would take issue with ID by itself, on every table. Anytime I see that, I have low expectations for how the rest of the database is designed.
Even worse. GUID as a column on every table ...
Regardless of datatype, I don't want to see a column names having no contextual prefix, meaning just "ID" by itself, on any table, and much less on every table.
As for GUIDs, it's a natural fit for the XML document world, where data is exchanged globally, but there is no centralized global mechanism for obtaining unique and sequential identifiers. However, in the relational database world, I rarely see a strong and compelling argument for having primary or surrogate keys as type GUID. Even in an environment that uses federated databases or merge replication, simple integer based identity columns or sequences can be seeded within a different rage on each server.
Integer doesn't always work - 2**32 is quite a small number; but identity types can cover a much larger range, as they aren't restricted to integer, and the only imaginable justification for using a GUID is that (if you pick the right generation GUID spec) you may be globally, not just locally, unique.
I've known systems where the ID of an X was X_ID everywhere except the X table, where it was just ID. I really didn't see any problem with that, clearly ID as a column name in any join was going to need a prefix, but it didn't seem (to me anyway) to introduce any real problem (in fact it encouraged people to provide table alisases and use them in the select list, exactly the opposite of a problem). But anything I designed myself called it X_ID even in the X table, so maybe I agree with you even though a different approach is workable and in some ways useful.
Edit: I forgot to say: Anything that's a natural fit for the XML world must of course be hopelessly wrong for the relational world.
Keep in mind, too, that a GUID is not absolutely guaranteed to be unique. Yes, there's an extraordinarily strong likelihood that it will be, but there can a single value overlap. An origin code and an identity provide an absolutely, 100% guaranteed unique identifier.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 29, 2015 at 8:13 am
ScottPletcher (1/29/2015)
TomThomson (1/28/2015)
Eric M Russell (1/27/2015)
andrew gothard (1/27/2015)
Eric M Russell (1/21/2015)
ProductID, ProductId, and product_id are all respectable options, so long as it's done consistently.However, I would take issue with ID by itself, on every table. Anytime I see that, I have low expectations for how the rest of the database is designed.
Even worse. GUID as a column on every table ...
Regardless of datatype, I don't want to see a column names having no contextual prefix, meaning just "ID" by itself, on any table, and much less on every table.
As for GUIDs, it's a natural fit for the XML document world, where data is exchanged globally, but there is no centralized global mechanism for obtaining unique and sequential identifiers. However, in the relational database world, I rarely see a strong and compelling argument for having primary or surrogate keys as type GUID. Even in an environment that uses federated databases or merge replication, simple integer based identity columns or sequences can be seeded within a different rage on each server.
Integer doesn't always work - 2**32 is quite a small number; but identity types can cover a much larger range, as they aren't restricted to integer, and the only imaginable justification for using a GUID is that (if you pick the right generation GUID spec) you may be globally, not just locally, unique.
I've known systems where the ID of an X was X_ID everywhere except the X table, where it was just ID. I really didn't see any problem with that, clearly ID as a column name in any join was going to need a prefix, but it didn't seem (to me anyway) to introduce any real problem (in fact it encouraged people to provide table alisases and use them in the select list, exactly the opposite of a problem). But anything I designed myself called it X_ID even in the X table, so maybe I agree with you even though a different approach is workable and in some ways useful.
Edit: I forgot to say: Anything that's a natural fit for the XML world must of course be hopelessly wrong for the relational world.
Keep in mind, too, that a GUID is not absolutely guaranteed to be unique. Yes, there's an extraordinarily strong likelihood that it will be, but there can a single value overlap. An origin code and an identity provide an absolutely, 100% guaranteed unique identifier.
Does anyone know the specification for how SQL Server's GUIDs are composed?
I thought that it contained the computer's MAC address (origin code) and timestamp (indentity), or perhaps that only applies to NEWSEQUENTIALID().
In the RDMS realm, it makes more sense for a number of reasons to use a business assigned origin code and timestamp combined to form a natural key.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 29, 2015 at 8:27 am
In the RDMS realm, it makes more sense for a number of reasons to use a business assigned origin code and timestamp combined to form a natural key.
[Emphasis mine]
However valid a process this might be to create a unique value, it's not a natural key. A natural key would be one that occurs with the item itself, a unique product code or something.
January 29, 2015 at 8:37 am
RonKyle (1/29/2015)
In the RDMS realm, it makes more sense for a number of reasons to use a business assigned origin code and timestamp combined to form a natural key.
[Emphasis mine]
However valid a process this might be to create a unique value, it's not a natural key. A natural key would be one that occurs with the item itself, a unique product code or something.
In a POS system, someting like a terminal_id (origination) + timestamp could be a natural key, assuming each terminal has a unique id and the terminal can only scan one item at a time. There would be a product_id too, but that would be more of an attribute of transaction, not necessarily part of the primary key. Of course someone in tech support could muck it up by installing a new POS terminal with the same ID as an existing one.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 29, 2015 at 9:34 am
In a POS system, someting like a terminal_id (origination) + timestamp could be a natural key
I don't want to get in a back and forth about what a natural key is or isn't. Here's one definition from Wikipedia:
In relational model database design, a natural key is a key that is formed of attributes that already exist in the real world.
Your addition of the timestamp seems to me to turn it into a surrogate key. I have nothing against surrogate keys. I use them in OLTP environments when a natural key isn't available, and almost exclusively in an OLAP environment (an absolute rule with dimension tables). Only that we should be precise in our terms to the extent that is realistic.
January 29, 2015 at 9:41 am
RonKyle (1/29/2015)
In a POS system, someting like a terminal_id (origination) + timestamp could be a natural key
I don't want to get in a back and forth about what a natural key is or isn't. Here's one definition from Wikipedia:
In relational model database design, a natural key is a key that is formed of attributes that already exist in the real world.
Your addition of the timestamp seems to me to turn it into a surrogate key. I have nothing against surrogate keys. I use them in OLTP environments when a natural key isn't available, and almost exclusively in an OLAP environment (an absolute rule with dimension tables). Only that we should be precise in our terms to the extent that is realistic.
Both attributes exist in the real world. Scanning an item on a POS terminal is an event that occurs in space (terminal_id) and time. Generally speaking, the way most POS hardware works, you can't scan multiple items on the same physical device at the same time, so that would be a natural key.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 29, 2015 at 9:56 am
Eric M Russell (1/27/2015)
Regardless of datatype, I don't want to see a column names having no contextual prefix, meaning just "ID" by itself, on any table, and much less on every table.
I worked with a third party product like this:
create table Stuff (id int, something varchar(), ...)
create table Schedule (id int, StuffID int, TimeID int, something varchar(), ...)
create table Time (id int, BuildingID int, something varchar(), ...)
create table Building (id int, StuffID int, something varchar(), ...)
January 29, 2015 at 9:59 am
Steve Jones - SSC Editor (1/29/2015)
Eric M Russell (1/27/2015)
Regardless of datatype, I don't want to see a column names having no contextual prefix, meaning just "ID" by itself, on any table, and much less on every table.I worked with a third party product like this:
create table Stuff (id int, something varchar(), ...)
create table Schedule (id int, StuffID int, TimeID int, something varchar(), ...)
create table Time (id int, BuildingID int, something varchar(), ...)
create table Building (id int, StuffID int, something varchar(), ...)
I too have worked on a system much like this and I found it extremely distasteful to work with.
_______________________________________________________________
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 29, 2015 at 9:59 am
Steve Jones - SSC Editor (1/29/2015)
Eric M Russell (1/27/2015)
Regardless of datatype, I don't want to see a column names having no contextual prefix, meaning just "ID" by itself, on any table, and much less on every table.I worked with a third party product like this:
create table Stuff (id int, something varchar(), ...)
create table Schedule (id int, StuffID int, TimeID int, something varchar(), ...)
create table Time (id int, BuildingID int, something varchar(), ...)
create table Building (id int, StuffID int, something varchar(), ...)
That was a common practice then. A simple "id" column was the id for the table in which it appeared; it was only prefixed in other tables.
The real problem here is not the naming, it's that some cluster every table "by default" on the identity -- that's a disaster. The naming is just a bit annoying to some.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 29, 2015 at 10:07 am
Phil Parkin (1/23/2015)
ScottPletcher (1/22/2015)
--But, ProductId seems right to me if you're going to camel case.
--
Camel case would be productId. ProductId is Pascal case 🙂
I believe either ProductId or productId are camel case, specifically "upper camel case" and "lower camel case". The default is, or at least was, considered to be upper. That may have shifted now, as lower camel case has caught on for whatever reason.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 4, 2015 at 2:08 am
I generally use ID if it represents a generated surrogate key, but if the key is a 'real' thing such as an employee ID then I would use employeeID.
February 4, 2015 at 7:27 am
richxs (2/4/2015)
I generally use ID if it represents a generated surrogate key, but if the key is a 'real' thing such as an employee ID then I would use employeeID.
So in one table the column name is ID but it changes it's name in another table? That is one of my biggest pet peeves in sql server. You have to stop and think about if the name is ID in this table or if it is another name for the same thing. I am a big fan of removing ambiguity whenever possible and a column named ID is very ambiguous.
_______________________________________________________________
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/
Viewing 14 posts - 91 through 103 (of 103 total)
You must be logged in to reply to this topic. Login to reply