Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

up vote 18 down vote accepted

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

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! :-)

share|improve this answer
3  
semantically correct naming system that avoids duplication, tautology and abbrevations THIS! Plus with SQL Server you can put descriptions of each field, which are tied to the table itself. –  Jason Holland Jul 11 '12 at 13:50
    
The Description field in management studio is more of a hack... Not usable in any productive way. –  Pascal Jul 11 '12 at 19:25
1  
The description field is an extended property which is stored within the database. This can be used in a productive way, as an example: A previous environment I worked in used extended properties in conjunction with a CodeSmith generator product that would generate all of our stored procedures for read, inserts and deletes. We also utilized extended properties to build our data dictionaries. –  Shawn Melton Aug 5 '12 at 4:22
add comment

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)

share|improve this answer
    
The thing about using stored procedures is highly controversial, so I would not recommend it without reservation. There are advantages, but also significant disadvantages to this approach. –  sleske Jul 11 '12 at 9:44
4  
And using tbl and f prefixes for tables/fields strikes me as rather absurd - what use can that possibly be? Or do you name all your classes starting with C and and all your variables with v? –  sleske Jul 11 '12 at 9:45
2  
For a discussion of possible problems with stored procedures, see e.g. programmers.stackexchange.com/questions/65742/… and programmers.stackexchange.com/questions/139319/… I'm not saying they are always bad, but there are problems involved. –  sleske Jul 11 '12 at 10:16
1  
Hungarian notation for database tables and columns?? No thanks. That would make a very funny code when you try to use an ORM tool, unless you want to manually rename ALL the table-class and field-property mappings. –  Konamiman Jul 11 '12 at 13:58
1  
At work we have to track the timestamp emails were sent. I have a few columns like so: MonthlySummaryEmailLastSent, WelcomeEmailLastSent etc. Make them as long as you need to get your point across. –  The Muffin Man Aug 5 '12 at 5:17
show 5 more comments

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.