SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding a Database


Understanding a Database

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)

Group: Administrators
Points: 619994 Visits: 21261
Comments posted to this topic are about the item Understanding a Database

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Andy Robertson
Andy Robertson
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3679 Visits: 665
What you need is a guided tour by someone who knows the database and can explain the general principles and the strengths and weaknesses. if you're lucky there is someone who has been around forever who can spend some time with you explaining the schema.
I tend to set up diagrams for any new databases I create as I think they give the best overview of the structure and you can annotate these easily enough.
A good understanding of the business area helps you get an idea as to how the tables might be structured. Sometimes if you try and suss out the key areas that the database deals with and work out in your head how you would set up the database then often chunks of the real database fall into place.
An understanding of the history of the business, db, applications etc also helps.
robert.sterbal 56890
robert.sterbal 56890
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9354 Visits: 1691
I always do table counts. I compare the baseline count with subsequent table counts.

On big tables I'll do distinct counts per column.

External descriptions of what columns are being used for are quite helpful.
richardmgreen1
richardmgreen1
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8108 Visits: 1523
I fully agree that the vendor databases are usually less than perfect about naming conventions.

As I work in healthcare in the UK (not sure where other posters are from), we have a nationally provided dictionary of terms and descriptions and we map our fields to those.

It's not always perfect as the term for a field might not be perfectly clear in its content but 30 seconds on Google will usually give you the definition.

I also agree that diagrams are helpful, not only for explaining how tables link together, but we've also found that creating a diagram in SSMS helps when creating SSAS cubes.
We've found that, if you select the FACT table, then clicking the "include linked tables" (or whatever the field is called) also brings all the related DIM tables.
stevenb 14609
stevenb 14609
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 679
If you have the option of a test environment, or can put in test data, it's easier to figure out what goes where by putting dummy data in the front end and looking to see where your dummy values end up in the database.
I usually start from an entity approach. For example, your customer. Once you figure out what your primary key for your entity is, you can start looking to see where that ID shows up in other tables to figure out what the relationships are.
Lookup values are always a fun one. We have one system that decided cities should be a lookup. It makes sense for the application because it has to store extended information about the city for what it does, but it threw me just a bit when the front end says the customer address is like 123 main street, Springfield MA, and the table data says 123 main street, 54 MA.
I regularly use a script that searches all fields in all tables in the database for specific text values. Sometimes that's the only way to track down some of these lookup values. Especially when you have the fun of a master lookup table. So that's TableID, LookupID, LookupValue.
A lot of it is just knowing how databases are supposed to be designed, and then hoping that the designer of the system followed normal design patterns. Some of it is just luck as to how much hunting you're going to have to do.
roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8900 Visits: 2017
My first step is to use ModelRight's reverse engineering function to suck the database schema into a diagram. Then I spend a little time arranging the diagram to study the relationships between the tables.

Once I understand table categories (these are master tables, these are detail tables, these are code lookup tables, etc.) I start concentrating on understanding which table holds what. Alas I find third party vendors are seldom very good at relational database design.

One product didn't use clustered indexes. At all. Another decided that joins were for the weak. Smile

And yet others don't understand that tables should have descriptive names. Sigh.

Once I have all that, then I start tracking down what individual columns store. As I do so I add comments and note fields, see if there are any consistently named columns, and so forth. In effect the ERD becomes the data dictionary, with the added advantage that it's also a pretty picture.

My last step is to use SSMS to explore the stored procedures/functions and see if they're named well. Usually--no. (shaking head). Is that approach supposed to be security by obscurity? Or just being bloody minded to outsiders?
richardmgreen1
richardmgreen1
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8108 Visits: 1523

<snip>
And yet others don't understand that tables should have descriptive names. Sigh.
<snip>

I feel your pain.
As an example, here are a few of our tables in the vendor database:-
AbstractData
Abstractdata2
AbstractData3

Or, even better:-
SchUkReferral
SchUkReferralMore
SchUkReferralMoreTwo

Apparently, the top table in each of the above lists is from a different section of the application, with the others containing data that should have gone into the original table but they forgot(!).

It get's even better when the tables are not normalised (and even more fun when two data items that should be identical (like a date) ..... aren't!).

Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111692 Visits: 14932
Database modeling tools can be expensive, complicated, and despite all their bells and whistles, are ultimately rigid. There have been situations where I have questioned why a database was designed a certain way, things like inappropriate choice of datatypes or use of triggers, and the developer told me that's just how XYZ tool does it. These tools also require a 500mb download to be installed on your PC just to view and edit the project.

However, if what you're really wanting to do is create some basic entity relationship documentation for an existing database and then easily share that documentation with others, then there are some online tools that are either free or affordable. They are easy to use and have all the functionality you'd need for your purpose without all the fuss. Robert Sheldon wrote up a review on several of them a couple of years ago.
https://www.red-gate.com/simple-talk/sql/database-administration/five-online-database-modelling-services/


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111692 Visits: 14932
Documenting a legacy database is what I call "database cartography". Sometimes it's just built in a piecemeal fashion over time based on exploration and local folklore passed down from gray haired wise-men. There are plenty of areas on the map where I add the label "Here There Be Dragons". Unsure


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8900 Visits: 2017
Eric M Russell - Friday, November 30, 2018 7:27 AM
Database modeling tools can be expensive, complicated, and despite all their bells and whistles, are ultimately rigid. There have been situations where I have questioned why a database was designed a certain way, things like inappropriate choice of datatypes or use of triggers, and the developer told me that's just how XYZ tool does it. These tools also require a 500mb download to be installed on your PC just to view and edit the project.

However, if what you're really wanting to do is create some basic entity relationship documentation for an existing database and then easily share that documentation with others, then there are some online tools that are either free or affordable. They are easy to use and have all the functionality you'd need for your purpose without all the fuss. Robert Sheldon wrote up a review on several of them a couple of years ago.
https://www.red-gate.com/simple-talk/sql/database-administration/five-online-database-modelling-services/

This is true, of course. It took me forever to find an ERD tool that worked the way I wanted to, and it took a few weeks to really learn it.

HOWEVER.

ModelRight (the one I really like) is a bit pricey. For the SQL-Server only version it's about $600. Having said that ModelRight paid for itself very quickly. I'm a lone-wolf developer/DBA and using this program gave me a force-multiplier equal to hiring two junior DBAs.

It isn't just about drawing pretty pictures. ModelRight is a CADD tool for database design, not only does it allow you to create the diagram proper, but use that diagram to create the scripts that create the database, alter it, and maintain a detailed data dictionary during initial design. It lets you create user-defined data types (translated to native types in scripts) that automatically set constraints and defaults, set table constraints/indexes with naming conventions you can tailor, set up foreign constraints, and allow you to script it all with a single keystroke.

I operate on a severely constrained budget, but tools like this cost a pittance when compared to the salaries of two DBAs--and it's a sunk cost, not a recurring one.

I pick my tools carefully. Between ModelRight and Redgate's SQL Compare/SQL Data Compare it's like having a construction crew with heavy equipment building a canal vs me on my lonesome with a teaspoon...

The fact I can reverse-engineer third-party databases is just icing on the cake. Since, you know, I needed the tool for development anyway.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search