How to study the layout of a database?
-
Hello friends,
Suppose you download a sample database, whether it be AdventureWorks, Northwind, or whatever other database. What steps do you take to thoroughly understand the layout of the database?
Do you use Excel or some software to note all the table names, columns, and keys? Do you use some tool to draw a Entity Relationship Diagram?
I ask this because if I were to do joins or subqueries, I need to know all the relevant tables, columns, and keys to write the query. What would you do to learn the database layout fully to perform such queries?
Thanks for all the advice!
-
The first step, you pray that whoever built it used somewhat intuitive names for tables columns and constraints.
- ZZartin - Tuesday, September 18, 2018 9:05 AM
Assuming there are some constraints. And that the tables are not all heaps.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Please. I am a noob but I really want to learn. Sorry if my question is funny, but I really would appreciate real advice. I am dreaming of landing a new career
- primitivefuture2006 - Tuesday, September 18, 2018 6:14 PM
Your question is not 'funny', don't worry! But it is difficult to answer, because the answer will vary from one database to the next.
A database can have a huge number of moving parts, and not all of them need to be contained within the database itself (SSIS packages, external Windows and Web apps, external scripts and other things can all affect the state of a database). Trying to get to grips with the entire scope of a database and its connected applications is likely to be a big task. I've seen applications where all of the data manipulation code was held outside of the database, in a desktop app, perhaps. Trying to understand 'the layout of the database' in such situations is difficult, because the code is largely hidden (unless you are also an Windows application developer, in which case you may be able to dig into the source code and work things out).
Having said that, if a database is serving a single application and has been built based on the principle of having as much code as possible within the database, the task gets easier. If the database objects also follow strict naming conventions and have been built by a diligent development team, you should be able to get somewhere.
One thing you can do is to create one or more database diagrams (use the 'Database Diagrams' node in SSMS). These will provide a graphical display of relationships between tables (assuming that those relationships have been created in T-SQL).
Beyond that, start looking at the design of some of the tables, views and stored procs to attempt to get a feel for what is going on and how things have been put together.
Don't be daunted if you feel like you are not getting anywhere fast. This is a task which even very experienced developers find difficult.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Start by understanding what business solution the database is providing. Are you looking at a way to track widgets being built? Healthcare? Financial transactions? Customer Service?
That will help you have some sense of how the data flows through the system. If you can have someone show you a front end, sit down with them for that. While they're showing you, write up statements you can reference later looking for referential integrity (customers have orders, create contact records, each contact is tracked but can be passed to multiple departments, etc.)
Then hope names make sense. Many times there are reference tables with the front end descriptions for things that are only referenced by key values in the tables. If you find one that you want to track, find every one of the columns named like that key, and/or find foreign key constraints.
Look at index usage, query store, any other tools within the database that are built to show you where the data is being used.
Views, stored procedures, reporting from the database are all good places to look to find out how it works.-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses - ZZartin - Tuesday, September 18, 2018 9:05 AM
And didn't try to punk you by using godawful Hungarian notation and all lowercase like that person thinks he's e.e. cummings or something.
-
I would say this question is kind of difficult not just because it varies from database to database and environment and environment but also from person to person and the particular goals that you set.
For example, depending whether you want to see a big picture of how data is organized within normalized or de-normalized tables or study what a particular t-sql stored proc or function does, or how a complex-looking view pulls data from many tables and other views, - you may want to see and ER diagram (ER = Entity Relationships). Of course if database is huge and has 500 or 2000 tables in it your ER diagram will be the size of a building and useless for your review :). I usually do ER-Diagramming of a particular subject area that I am interested in that involves a dozen or less of core tables and views containing the data that I need to learn and understand. You can do such diagramming using a variety of tools, including the one built-into SQL Server (not really good and flexible...) or something more sophisticated like ERwin or MS Visio.Database Documentation can be of paramount importance and use but it is usually a rare luxury in most IT shops.
Using an example of AdventureWorks database that you mentioned, here is what good documentation for it looks like (PDF file):
http://allstarsql.com/adventureworks2012.pdfIt never hurts to ask your DBA for such documentation :). At least you can ask. One shop out of 100 will have such documentation, you may just get lucky... 😎
I also do this for analyzing stored procedures or complex views: finding dependencies. I.e., objects that the stored proc depends on (uses in its code) and/or objects that use the object that I am analyzing. Since the built-in Sp_depends is not really dependable for such purposes, a third party tool may be recommended such as Redgate Dependency Tracker.
Some SQL Server undocumented stored procedures showing up and down level dependencies can be useful too:
-- Value 1053183 shows objects that the specified object is dependent on
EXEC sp_MSdependencies 'vw_MyComplexHeadacheView', null, 1053183-- Value 131527 shows objects that are dependent on the specified object
EXEC sp_MSdependencies 'vw_MyComplexHeadacheView', null, 1315327Likes to play Chess
-
No, it's a good question.
You could look after:
- If there are som diagrams under the 'Database Diagrams' node in SSMS
- If tables and columns have Extended Properties.
There are several programs that extract the documentation in the database(if there are some).
To get a simple list over the documentation of tables and columns:
select object_schema_name(ep.major_id) [Schema],
object_name(ep.major_id) TableName,
c.column_id ColumnId,
c.name ColumnName,
ep.value [Description]
from sys.extended_properties as ep
inner join sys.sysobjects as so on so.id = ep.major_id
left outer join sys.columns as c on c.object_id = ep.major_id and c.column_id = ep.minor_id
where so."type" = 'U' and so.name <> 'sysdiagrams' and ep.class_desc = 'object_or_column' and ep.name = 'MS_Description'
order by object_schema_name(ep.major_id), object_name(ep.major_id); -
Database Diagrams, but I find this quite clunky with a bigger DB.
Numerous bits of software out there to do the job for you. I've found myself using the latest build of SchemaSpy actually because its quite easy to read what is generated.. but If you want to give people access to the schema within an org, I've had great success with it - for multiple backends... You will need Java, and you will need the MS JDBC drivers....
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
- primitivefuture2006 - Tuesday, September 18, 2018 6:14 PM
IF there are views and stored procedures that come with the database, read those and map out how the tables are joined.
Also, if someone did a decent job on the database, check the Foreign Keys (pure gold if they exist) for relationships among the tables. Although a bit limited and does take a bit of getting used to, you expand a given database (in the Explorer window) and use "Database Diagrams" to help get pictures of tables and their relationships when FKs are present. If you're really lucky, you might find some that have already been drawn out by the people that created the database. It's not the best tool in the world but it's a start.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, November 13, 2018 7:24 PMprimitivefuture2006 - Tuesday, September 18, 2018 6:14 PM
IF there are views and stored procedures that come with the database, read those and map out how the tables are joined.
Also, if someone did a decent job on the database, check the Foreign Keys (pure gold if they exist) for relationships among the tables. Although a bit limited and does take a bit of getting used to, you expand a given database (in the Explorer window) and use "Database Diagrams" to help get pictures of tables and their relationships when FKs are present. If you're really lucky, you might find some that have already been drawn out by the people that created the database. It's not the best tool in the world but it's a start.
Thanks for the advice! I will explore Data Diagrams today. Are you working with massive databases professionally? I mean, are the databases you work with involve many tables (50+), some tables with hundreds of thousand to millions of rows? Thanks again
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply