April 7, 2016 at 9:36 am
Eric M Russell (4/7/2016)
.. The tables in a JD Edwards system were coded with a short alpha sequence and then a 4 or 5 digit number. Always fun to remember what "FS3401" means...
Ha! I'll bet you're talking about PeopleSoft.
Certainly started at JD Edwards (we were an ERP vendor), and we were sold to Peoplesoft. I think many of our employees had moved between the companies over the years.
April 7, 2016 at 9:41 am
TMiller 21949 (4/7/2016)
Not just Colorado.... Cape Coral, FL... although there are exceptions, streets that can get you from Point-A to Point-B (lots of streets cannot do that - the place is half streets, half canals) are named with words (Santa Barbara, Del Prado, Cape Coral Parkway - they thought long and hard on that one). However, a vast majority of streets are numbered, and each number may have 9 (12?) versions: 14th St, 14th Pl, 14th Ave, etc.To top it off, depending on where you are in the city, it might be NE 14th, SE 14th, NW 14th, or SW 14th.
There was a method to the madness, though. Each address included where on the city's grid that address was. 1704 SW 14th (whatever) placed that address on SW 14th, and the streets running perpendicular to SW 14th would be SW 17th (whatever). And you could tell if the street ran north/south or east/west by whether it was ST, PL, AVE, DR, etc. If you were new to the city, the reaction was always WTF?!? But once you learned the madness' method you could get from Point-A to Point-B, maybe even without a map.
We have lots of curved streets in developments, so there's not method that helps you understand where PL might be in relation to AVE or CIR.
April 7, 2016 at 9:41 am
Consistency is all otherwise it will be hell for all.
Trying to remember the CustomerNumber, AccNo, PersonId and any number of alternatives actually store the same thing until one day they don't:crazy:
April 7, 2016 at 10:02 am
TMiller 21949 (4/7/2016)
Not just Colorado.... Cape Coral, FL... although there are exceptions, streets that can get you from Point-A to Point-B (lots of streets cannot do that - the place is half streets, half canals) are named with words (Santa Barbara, Del Prado, Cape Coral Parkway - they thought long and hard on that one). However, a vast majority of streets are numbered, and each number may have 9 (12?) versions: 14th St, 14th Pl, 14th Ave, etc.
To top it off, depending on where you are in the city, it might be NE 14th, SE 14th, NW 14th, or SW 14th.
There was a method to the madness, though. Each address included where on the city's grid that address was. 1704 SW 14th (whatever) placed that address on SW 14th, and the streets running perpendicular to SW 14th would be SW 17th (whatever). And you could tell if the street ran north/south or east/west by whether it was ST, PL, AVE, DR, etc. If you were new to the city, the reaction was always WTF?!? But once you learned the madness' method you could get from Point-A to Point-B, maybe even without a map.
We have lots of curved streets in developments, so there's not method that helps you understand where PL might be in relation to AVE or CIR.
I've grown to like the Utah system, though it was strange to me at first, it just makes a lot of sense.
They have a Center Street and a Main Street that form a cross (like so... +). Then streets north of Center are named numerically as you move away from Center street, like so. 100 North, 200 North 300 North etc. South of it would be 100 South, 200 South etc.
Likewise streets east and west of Main would be 100 East, 200 east and 100 West, 200 West respectively.
So my old address 325 West 500 North would be 5 blocks north of Center and 3 1/4 blocks West of Main.
Always made it easy to know how much farther you had to go to get to your destination.
J DBA
April 7, 2016 at 10:19 am
J DBA (4/7/2016)
TMiller 21949 (4/7/2016)
Not just Colorado.... Cape Coral, FL... although there are exceptions, streets that can get you from Point-A to Point-B (lots of streets cannot do that - the place is half streets, half canals) are named with words (Santa Barbara, Del Prado, Cape Coral Parkway - they thought long and hard on that one). However, a vast majority of streets are numbered, and each number may have 9 (12?) versions: 14th St, 14th Pl, 14th Ave, etc.
To top it off, depending on where you are in the city, it might be NE 14th, SE 14th, NW 14th, or SW 14th.
There was a method to the madness, though. Each address included where on the city's grid that address was. 1704 SW 14th (whatever) placed that address on SW 14th, and the streets running perpendicular to SW 14th would be SW 17th (whatever). And you could tell if the street ran north/south or east/west by whether it was ST, PL, AVE, DR, etc. If you were new to the city, the reaction was always WTF?!? But once you learned the madness' method you could get from Point-A to Point-B, maybe even without a map.
We have lots of curved streets in developments, so there's not method that helps you understand where PL might be in relation to AVE or CIR.
I've grown to like the Utah system, though it was strange to me at first, it just makes a lot of sense.
They have a Center Street and a Main Street that form a cross (like so... +). Then streets north of Center are named numerically as you move away from Center street, like so. 100 North, 200 North 300 North etc. South of it would be 100 South, 200 South etc.
Likewise streets east and west of Main would be 100 East, 200 east and 100 West, 200 West respectively.
So my old address 325 West 500 North would be 5 blocks north of Center and 3 1/4 blocks West of Main.
Always made it easy to know how much farther you had to go to get to your destination.
J DBA
Chicago is similar in that every 8 blocks is a mile. So Halsted is 800 W, Ashland is 1600 W, and they are one mile apart. The same is true for South of Madison St (?). Roosevelt is 1200 S, and the distance between that and Garfield (5500 S) is 5 3/8 miles ( (55-12)/8 ). Now based on that last example, you can see where it gets weird. Main streets on the South side are 47th, 55th, 59, 75th, whereas E-W are 800, 1600, 3200, 4800, et cetera. Eight blocks works nicely since we tend to think in quarter, half and full miles. I assume it was pre-odometer, hence not matching up with base 10.
So, even when someone gets something right about streets, they still mess it up. 🙂
Dave
April 7, 2016 at 10:20 am
Iwas Bornready (4/7/2016)
OK clearly using a bunch of numbers makes little sense. But you can go overboard the other direction too. I think our column names are sometimes too long and become basically unreadable.Here is an example: USBankEPaymentSchedulePaymentMessageProductParameterID
Exactly. I am not great with schema, so feel free to correct me. Wouldn't it make more sense to have a USBank schema, a PaymentSchedule table, and then a shortened name for the payment message product parameter?
Dave
April 7, 2016 at 10:23 am
Right on, Steve! Try finding your way around Interlocken in Broomfield: Interlocken Dr, Interlocken Crescent, Interlocken Way...It's enough to make you drive your car into a building lobby! I used to think Colorado was the only place in the country where street names changed in the middle of an intersection - Cherry Street suddenly becomes Centennial Parkway - until I lived in Orange County for a while in the 90s; they do it in Southern California, too.
I've authored or co-authored numerous database standards documents for clients and employers over the years, most of which specified naming conventions - my Masters thesis was on the importance of standards and conventions in systems development. In my current role, I capitulated on one religious tenet to which I have always clung: That tables should always be named with the PLURAL of the entity they contain, because a table contains information about multiple instances of an entity, not a single instance - EMPLOYEES vs. EMPLOYEE, or PRODUCTS vs. PRODUCT, for instance. The reason I gave in on this rule for my current organization is that we are a large multi-national organization, with teams of developers scattered all over the world, many of whom are non-native English speakers. The plethora of rules and exceptions thereto in the English language for converting a singular noun to its plural can be quite confusing for non-native English speakers; so I accepted the argument that we should standardize on singular names for our database tables, just to keep it simple for our foreign-born counterparts.
The one naming convention that still grinds my gears is using a suffix of ID for a table's primary key column, and the identical column name as a foreign key in any associated tables - I prefer PK/FK for Primary Key and Foreign Key, respectively. That way, there's never any confusion as to whether the column in question is a primary key in THIS table, or a foreign key to some other table.
April 7, 2016 at 10:28 am
One thing I don't believe has been mentioned:
There are databases I have worked on where the vendor designed the tables so that a column in table B had a different name than the same column in the parent table. So for example, an employee number might be stored in EMPLOYEE in the EMPLOYEE table, but in a related table it might be stored in the EMP column, of sometimes fk_EMPLOYEE or something like that. I worked on one system where there was no correlation between tables at all, and I had to call the vendor to find out how to join tables. We essentially needed to obtain a list of employees that had met a requirement in the LMS system, and share the data with the Talent Management system. To do so, I believe I had to join around 8 tables, none of which contained anything that you could look at and see was the key to join to a parent table!
Worse yet, depending on how the data was entered, sometimes you couldn't join at all, because the system stored it one way when you took a course, but when the instructor entered the data, it didn't store things the same! ARGGGHHHHH!!!
Dave
April 7, 2016 at 10:31 am
MattGauch (4/7/2016)
Right on, Steve! Try finding your way around Interlocken in Broomfield: Interlocken Dr, Interlocken Crescent, Interlocken Way...It's enough to make you drive your car into a building lobby! I used to think Colorado was the only place in the country where street names changed in the middle of an intersection - Cherry Street suddenly becomes Centennial Parkway - until I lived in Orange County for a while in the 90s; they do it in Southern California, too.I've authored or co-authored numerous database standards documents for clients and employers over the years, most of which specified naming conventions - my Masters thesis was on the importance of standards and conventions in systems development. In my current role, I capitulated on one religious tenet to which I have always clung: That tables should always be named with the PLURAL of the entity they contain, because a table contains information about multiple instances of an entity, not a single instance - EMPLOYEES vs. EMPLOYEE, or PRODUCTS vs. PRODUCT, for instance. The reason I gave in on this rule for my current organization is that we are a large multi-national organization, with teams of developers scattered all over the world, many of whom are non-native English speakers. The plethora of rules and exceptions thereto in the English language for converting a singular noun to its plural can be quite confusing for non-native English speakers; so I accepted the argument that we should standardize on singular names for our database tables, just to keep it simple for our foreign-born counterparts.
The one naming convention that still grinds my gears is using a suffix of ID for a table's primary key column, and the identical column name as a foreign key in any associated tables - I prefer PK/FK for Primary Key and Foreign Key, respectively. That way, there's never any confusion as to whether the column in question is a primary key in THIS table, or a foreign key to some other table.
They do it in Northern California, as well.
I agree completely on the PK-FK naming -- we've adopted the same and it is hugely useful. The table names needing to be plural sounds needlessly pedantic, though, imo. Occam's razor suggests the simplest solution is the best.
April 7, 2016 at 10:56 am
Then there was a client of mine who won't be named who decided to store the data for all the Widgets in a copy of the Gadgets database.
Seriously, you had to know that the field for widgets.TrimColor was actually stored in Gadgets.MiscSize.
They laughed at me when i expressed how horrible this was to work with. The reasoning was 'we didn't have any time to create anything new so we cloned it and changed the labels in the software GUI'.
April 7, 2016 at 11:13 am
Many of those weird table names came from the 'olden' days. In ERP systems they used some sort of ISAM file system back then. When they moved to SQL they didn't make an effort to change the table names (previously file names) because of all the coding required. Sometimes they even support the ISAM and SQL backends at the same time. Some ERP systems eventually changed their file names (Sage MAS 100 for instance). I know for a couple of the ERP's they used code converters to change the code from x language to y language. Once they did that it's become pretty difficult to change other things (like table names) because of the poor code quality.
April 7, 2016 at 11:14 am
DavidL (4/7/2016)
Eric M Russell (4/7/2016)
.. The tables in a JD Edwards system were coded with a short alpha sequence and then a 4 or 5 digit number. Always fun to remember what "FS3401" means...
Ha! I'll bet you're talking about PeopleSoft.
Or about Microsoft Great Plains (Dynamics).....
Worked for a very short time, like 3 weeks, for a person who used the same naming conventions as Dynamics for the proprietary application designed for an insurance brokerage.
His logic was that Dynamics is from Microsoft, and so is SQL, so they must be 100% right.
That structure wasn't the only reason that I walked out the door after 3 weeks, but it was a big part of it.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 7, 2016 at 11:34 am
Michael L John (4/7/2016)
DavidL (4/7/2016)
Eric M Russell (4/7/2016)
.. The tables in a JD Edwards system were coded with a short alpha sequence and then a 4 or 5 digit number. Always fun to remember what "FS3401" means...
Ha! I'll bet you're talking about PeopleSoft.
Or about Microsoft Great Plains (Dynamics).....
Worked for a very short time, like 3 weeks, for a person who used the same naming conventions as Dynamics for the proprietary application designed for an insurance brokerage.
His logic was that Dynamics is from Microsoft, and so is SQL, so they must be 100% right.
That structure wasn't the only reason that I walked out the door after 3 weeks, but it was a big part of it.
Yes, blindly following another's lead does remove the burden of understanding, but it's rarely the right thing to do.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 7, 2016 at 11:49 am
There are many naming conventions that prescribe things like case, underscores, plural vs. singular, or hungarian notation, but here are some more general guidelines that should apply regardless.
A column should be named using a convention that's similar to what it would be called conversationally, which means use the commonly used subject name, not cryptic names based on short abbreviations and numbers. Considering that code today is written using drag/drop visual tools or code completion helpers, there is no reason to be sparse.
The column name should indicate both the subject (ie: Started) and the context (Time), so the user doesn't have to guess whether it contains a timestamp, code, description, etc.
The same column should be named identically across tables.
For example:
[ProductID], not [ID].
[JobStartedTime], not [Started] or [JobStarted].
[PrimaryDiagnosisCode], not [Diagnosis] or [ICD9].
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 7, 2016 at 12:16 pm
I recently switched to something I didn't like at first, but seems to work well for my SSIS packages.
DATABASE.DATASOURCE.ACTION.DATANAME
method running for my SSIS jobs. So you end up with.
DW.GA.Importing.Sales
as the package name to signify:
Target Database -> DW
Target Data Source -> Google Analytics
Target Action -> Importing Data
Target Data -> Sales Data
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply