How do you keep track of the meaning of your SQL fields? [closed]

Question

The more SQL fields I add to my tables and the more primary/foreign keys I add the more I lose the overview for specific fields for certain scenarios like Get/Add/Delete/Update data.

I use SQL Management Studio (SQL Server) to design my database in the database diagram designer.

Maybe there is a better tool or good approach how to keep track of the meaning of all those fields?

Answer 1

How about a logical semantically correct naming system that avoids duplication, tautology and abbreviations?

That and a Glossary and a logical / physical ER diagram of the database, preferably hosted on a Wiki of some sort, is about all you can do.

Plus what Jason Holland says in the comment below! :-)

Answer 2

SQL Management Studio has the ability to add a Description for columns, but I've never found it useful, ever. I've also tried to get work to use docs, wikis and what-have-you's to document DB structure. After enough time, nobody bothers though. Release dates won't wait for anything.

A descriptive, consistent naming scheme won't fail you. Don't be afraid to use longer and descriptive column names if you feel the need, it's better to ensure future-you can make sense of the structure.

Currently I'm working a a 1600+ column DB that is a nightmare of inconsistent naming; so stick to your style!

A suggestion:

• name the primary key column to include it's purpose, so not "ID", but "ClientID". Makes reading queries easier.
• decide and stick to table names being singular or plural (i.e. tblClient vs tblClients)
  • My logical self prefers the singular
• prefer to group similar tables together, i.e. prefix lookup/static tables with tblLUT / lookup_
• the same goes for grouping Clients, Jobs, Assets, Cheezeburgers and similar
• use stored procedures and custom views, a lot of them, to retrieve your data
  • this normalizes your data access, providing a higher layer of data access

On a semi-related note, I recently found an alternative SQL server tool, with this nifty feature to generate code classes based on your table structures, taking a data reader in the constructor, makes for real handy work. It has made me realize that naming tables and fields without tbl and f prefixes is kind of nice for tools like these. (SQL Anywhere by Atlantis Interactive)

Answer 3

I would suggest to group your tables in logical domains and visualize them on a poster. You may created and prepare printable file them with the help of ER studio/SSMS.

Most importantly, naming of columns need to be closely related to domain to avoid naming further ambiguity in long run.

Answer 4

Since you're using MSSQL, if you're able to deploy a website then put together a little site that queries the system catalogs for the information you want to document. Connect to your database and go to Views -> System Views to see them.

I'd ignore the views in INFORMATION_SCHEMA because while they provide a more user-friendly format if you just want to look at them directly, they're lacking some useful information. The views in the "sys" schema contain everything.

The ones you'll most likely want to start looking at are:

sys.types
sys.objects
sys.tables
sys.columns
sys.indexes
sys.procedures
sys.views
sys.parameters
sys.schemas
sys.foreign_keys
sys.foreign_key_columns
sys.extended_properties

If you have any problems figuring out the meanings of columns in those views, they're all documented in Books Online / MSDN. Most of those should be self explanatory, except maybe sys.extended_properties. That's where Management Studio saves descriptions. If you add a description to a table and save it, then right-click the table in Object Explorer, and select Properties then Extended Properties you'll see the description. You can add an arbitrary amount of extended properties to things, and this can come in real handy for documentation depending on what you want to document and how detailed you want to get.

If you want to allow descriptions to be edited in the site itself, you can use the sys.sp_addextendedproperty, sys.sp_updateextendedproperty, and sys.sp_dropextendedproperty stored procedures.

Finally, if you want to document where a table / view / etc. is referenced throughout the database, you can use the sys.sp_depends stored procedure to get that information.

The nice thing about this approach is that your documentation will be exactly as up to date as the database itself. Looking at a table definition and add a new column to the table? Refresh the page and it's there. Although, if you want to have documentation that's just a snapshot, you can do that too. And you get something you can navigate through and in a format that avoids the clunkiness of trying to read description fields in SSMS.

Answer 5

The best naming in the world can't help you to know the details of why a field exists or what to look out for in the data. Check out Database Note Taker which is a free lighter weight tool enabling you to add an explanation to your database objects. Could be a short note, could be a longer description with example queries and using markdown syntax for formatting. It keeps your comments in a separate project file which is source control friendly.