Advanced SQL Server installation using PowerShe ll DSC (2023-11-22)

Having Data Modeling Standards

While working with a customer recently, they mentioned that they have certain standards for their objects. They require a PK, and it's the name of the table with _PK added. They also have some standards, like CustomerName vs. CustomerNames for various data items. In fact, they have enough that they built a tool to scan their database code to ensure that changes to the QA and UAT environments adhere to these modeling standards.

I wonder how many organizations have formal standards. While I've often tried to set some naming guidelines, I often haven't seen anything (or created anything) formal enough to build a tool around. I would like to, and I think it's a good idea, but it's often something that isn't handled in advance.

One thing I do think is important is to use singular naming. It's strange to me, as I've often thought of a table as a collection of Customers or Orders, but I get why singular makes more sense. I really started to embrace this more after a great design talk at one of the Denver Dev Days.

In the talk, the speaker talked about naming a table as an entity, but then including the entity name in columns. For something like a Customer table, we might have CustomerID, CustomerFirstName, CustomerLastName, CustomerStatus, CustomerPreferredContact, etc. There could be FKs in here, such as ContactID or AddressID, that linked to the entries in those tables, but in general, using a name like this was fairly unique for each column, and it helped anyone know where the data resided.

It was a lot of typing, and if you didn't have a tool like SQL Prompt, that might be really annoying, but I found myself seeing this as a very logical and easy-to-understand structure that even inexperienced developers might be able to follow and mimic. That doesn't mean there isn't a need to understand how we want to model structures like names, addresses, and more, but it did seem better than having FirstName in some fields and fname in others. CustomerFirstName or EmployeeFirstName makes a lot of sense. It also prevents ambiguous columns in queries.

I do think that good data modeling standards are helpful in organizations, especially as we allow more and more people to make database changes and our teams grow larger. I would like to know how many of you reading this feel. Do you think strong data modeling standards are a good idea?

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums