If you have followed my blog at all, you are probably aware that I am very pro-standards. I usually don't really care what your standards look like as long as they are consistently applied.

I am currently working with a system called Banner. It's sort of an ERP system for universities. It handles students, financial aid, alumni, cash processing, fixed assets, EDI, etc. It is pretty much the standard for this kind of system and is widely used around the US. I have no idea if it has a global presence.

Anyway, because it is so widely used, you would think it would be architected better. I am not going to rant on the occasional lack of primary keys or on the fact that a relational database should probably consistently use foreign keys. I will save those rants for another day.

No, today I am going to rant on the mindlessly stupid naming standards that have been inflicted on its users. I'm going to stick with table naming for now but you can apply this to other objects also.

First off, I am going to use made up tables for my examples. These don't really exist but they do demonstrate the standard.

Let's say I have a table that holds student applications for admission to the university. For one, with a system this large, I would probably use schemas to segregate logical applications from each other. So, in this example, I would probably have a STUDENTS schema.

In the STUDENTS schema, I would then create a table something like ADMISSION_APPLICATIONS. That means I would end up with STUDENTS.ADMISSION_APPLICATIONS. Do you think you might know what this table is for at a quick glance? We could even do some abbreviations if we had a consistent set of accepted abbreviations, i.e. STUDENTS.ADM_APPS. I could live with that, although I prefer full words when we can fit them.

Now, let's name our table using Banner naming standards. First, all the banner tables are in the same schema – all some odd thousands of them. We won't be segregating by schema. Ok, I would prefer the segregation but I could live with including students in the name like STUDENT_ADMISSION_APPLICATIONS.

Is that how we do it? Oh no it is not. We use codes. Yes, codes. We limit the name to no more than 8 characters.. Yes, 8 characters. Most tables are only 7.

The first code is the system. In our example, the system is STUDENTS so the first letter is an S. The system can be 2 characters but I have only worked with 1 letter so far. Some other systems are Finance, Accounts Receivable, etc.

The second code is the module. In this case, it is admissions so now we have a table named SA. The code used in the second position changes meaning based on the first code. So, if we used SG, that would be Student General information but if we had TG, that would be Accounts Receivable General Accounts. FG is Finance General Ledger and AG is Alumni Pledge Payment. You need a freaking decoder ring.

The third code is the type of object that we are creating. In this case it is what we call a base table (as opposed to a rules table (which is a type of lookup table with no foreign keys)), so we will use the code B. Now we have SAB. Other object types can be F - Form (Banner uses Oracle forms and is being rewritten in Groovy), I – inquiry form, Q – query form, V – validation form OR a view depending (on what I have no idea).

Positions 4 through 7 (or 5 thru 8 if it was a 2 letter system), are for the name of the object. I guess we could use APPS and end up with SABAPPS.

If you are looking at a database and trying to make sense of it, which would you prefer? STUDENTS.ADMISSION_APPLICATIONS or SABAPPS?

But wait, there's more! Let's look at column naming. Every column in a table begins with the table name. Yep, every one of the columns in our new table start with SABAPPS.

Let's say we have a unique surrogate key as our primary key, highest grade attained, an application date and an application sequence (in case they apply more than once on the same day – this really can happen – boggle). In reality we would have many columns in a table like this but for ease of explanation I will stick to just these columns.

Now, we have a table named SABAPPS with the columns SABAPPS_ID, SABAPPS_EDUCATION_LEVEL, SABAPPS_APPLICATION_DATE and SABAPPS_APPLICATION_SEQ. Really?

Actually, they randomly use non-obvious abbreviations like LEVL for level. I'll stick with my names to avoiding giving me an eye twitch.

Ok, so let's say we have a child table (I'll call it SABMADUP). It has a relationship with SABAPPS and contains the SABAPPS_ID as a foreign key. What will we call this column? Will we call leave it as SABAPPS_ID so we know where it comes from? Do bears go doody in fancy lavatories? No, we will call it SABMADUP_ID. We rename the related columns from other tables to the name of the table containing the column.

So, looking at the description of tables, we can't even deduce parentage or relationships. With the dearth of defined foreign keys, reverse engineering the schema becomes a nightmare.

To be successful with a design like this, you need to spend time with it. A lot of time. Years. Which I guess would allow you to charge high consulting rates. Hmmmm. I could get used to this bill rate naming standard.

Lewis is an Oracle Ace Director, Oracle Certified Professional, published author, frequent conference speaker and Database Architect. Lewis's specialties revolve around databases, data warehousing, business intelligence and most anything having to do with databases. He has two decades of multi-vendor and open source database experience in a variety of industries and capacities and has worked with Oracle since 1993.

"It's a leftover from the database-agnostic period. PeopleSoft was the same way - simply horrible for any DBA to look upon!

They are slowly starting to use triggers, haha!

If they could eliminate blanks and use NULL instead : P "

Sorry! Something went wrong on our end. Please try again later.

Lewis Cunningham

September 04, 2012 12:25 PM

"1. Backward compatibility seems to have been taken too far. Plus, it was a bad design even in the 90s. ;-)

2. Yeah, I have access to the online version of that. The docs are actually decent but that doesn't make up for the awful database.

3. I know it has more than 1 schema but all the tables are in 1 schema right? All of the ones I've looked at were.

I didn't cover it in this post but the lack of data integrity creates some awful data issues. If foreign keys were fully utilized the naming probably wouldn't bother me as much (although it is still awful).
"

Sorry! Something went wrong on our end. Please try again later.

Judy Kay Craft

September 04, 2012 12:15 PM

"1. Parts of Banner, especially in the Student Module, are backwards compatible to the early 90's. Parts of it are still in Cobol.
2. There isn't a decoder ring, there is a decode bookshelf ! Just think
how big the ring would have to be!
Users guides, release notes, technical reference manuals are all in the ""Banner Bookshelf""3. Banner does have more than one schema, but many of the names are in the obscure 7 character style.
4. First & second characters W, X, Y, Z were originally reserved for custom tables. Then when developing web based self service modules, the development team used the W (both first & second position) to mean web.
5. And yes it takes years to learn Bannerese "

Sorry! Something went wrong on our end. Please try again later.

Lewis Cunningham

September 04, 2012 10:48 AM

Doesn't it? We're in the second decade of the 21st century and the standard sounds like it was written in the 80s. At least it's running on Oracle.

Sorry! Something went wrong on our end. Please try again later.

JJFlash

September 04, 2012 10:41 AM

Sounds like a system that started as a dBase application from back in the MS-DOS age.