How to understand a database that is already developed?

Question

My question may seem a little weird but I am sure most of you might have gone through this phase.

I am currently working on a database migration project (from FoxPro to SQL Server). The DB that is being migrated is vast and I am new on this project. Is there any easy way to understand such a database? Like how are the tables related and how it was modeled. There is no proper documentation available on this DB.

I think understanding how it is built makes it much easier to write new queries/stored procs. Just curious to know of any shortcut.

Thanks.

Answer 1

I'm actually hoping you don't find much for answers here as I've made my career based on coming in on these large undocumented data models and trying to figure them out. But for what it's worth:

• I don't like the automated data modeller / electronic modeller, though this might be personal opinion. My preference is to find a white board (or paper) and draw out your data model by hand. If you are a kinaesthetic learner (learn by hands on participation), I've found this to be the best way of familiarizing yourself with the new database...as nice as an automated system is to read the database, you won't learn what you will when you draw it by hand.

• There is a limited number of data modelling techniques, however they can be combined in a lot of ways. My guess with a larger database like you have here, you will have multiple programmers creating it, which means you'll likely see multiple techniques used in the same database. In the past I have found a system that had it's circuit information stored as a single table that self joined onto itself repeatedly to store the information for a data circuit while the customer information section was a very straight forward star design...2 very separate programming styles, likely two separate developers. I later ventured into the phone circuit section of the app, which I recognized immediately as the same style (likely same programmer) as the data circuit section was. Usually, developers will be assigned to a logical division that correlates to a section of your business...watch for patterns in similar sections.

• The physical database structure is only one section to understand...on the Left (prior to the database) is how the data is generated and loaded into your database (data warehouse?). Understanding what your data is and how it is created is the first step in knowing what you are looking for in the database after it's loaded.

• Opposite side of above, after the data is in the database...understanding how the data is consumed (used by your users) will help you understand what they have been getting out of it and what they need from it. Extra points if you can get your hands on scripting used to generate existing reports as the from statement will help you see how existing tables are used.

• Never forget to interview your users...especially if you can locate one that was around for the initial deploy of the system. If it's in-house designed, odds are it was these people that provided some of the initial requirements for the system and talking to them will give you an idea of what the people who designed the system first heard when they went requirement gathering. The logical division of your company (customer care vs operations vs billing vs etc...) is usually the same division your data model will follow.

• And lastly...Play! If a dev or QA environment is available, start writing queries and see what comes back...alter your statement and try again.

I think the biggest folly you will want to avoid is focussing solely on how the tables are arranged. Understand the data thats in it, how it is generated and how it is consumed. Understand your company, how it's arranged and how it functions. The manner in which it's stored (the data modelling) is secondary to this understanding.

Answer 2

If I jump into a massive SQL Server database with hundreds of tables and millions of records here are two queries I use to help make sense of it all, find the "main" tables, and then narrow down to specific tables and columns.

--Query to show list of tables ordered by number of records in each table
    SELECT
        t.NAME AS TableName,
        SUM(p.rows) AS [RowCount]
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.columns c on t.object_id = c.object_id
    WHERE   
        i.index_id <= 1
    GROUP BY 
        t.NAME, i.object_id, i.index_id, i.name 
    ORDER BY 
        SUM(p.rows) DESC



--Query to show any columns or table names like what I'm looking for
SELECT
    c.name, t.name
FROM sys.columns c
    INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%#ColumnName%' OR t.name LIKE '%#TableName%'

Answer 3

Hi the following script may help you . If you use this on any developed database , you will at least learn the relationship of the tables.

SELECT
fk.name 'FK Name',
tp.name 'Parent table',
cp.name, cp.column_id,
tr.name 'Refrenced table',
cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
 tp.name, cp.column_id

Answer 4

I recently went through the same process... The thing that I think helped me the most was creating my own database diagram. I used the free tool wwwsqldesigner. It seemed pretty easy, the only issue I had was that it wouldn't work in Chrome for some reason, but firefox worked fine. I just put in the tables that I use a lot, and I find that I frequently refer back to it when I need to do something new.

If you can use the automatically generated database diagrams, that would be a better way to go, but I wasn't personally able to get them to work (I'm using sql server).

Good luck!

Answer 5

You can use SQL Server Management Studio to create a entity-relation model, so you can easily see the relations between the db tables. Basically inside your db folder in the management studio there is a folder named "Database Diagrams". Just right click it, and select "New Database Diagram."

Answer 6

Will the old Foxpro server co-exist with the new sql server after the migration? If not, you should try to decipher the system in piecemeal. No need to waste time on any stagnant and unused code. Notes and diagrams to just get a general feel for flow of data and start digging!

Answer 7

There are two distinctly different types of Data objects in Foxpro.
You most likely are viewing FREE Data Tables (DBF, CDX, IDX, FPT files) which are not contained within a "Database".
Alternatively Foxpro may have a Database (a DBC file) which can contain Data tables and those can be 'held' in a Related condition.

However, Foxpro data tables are typically not related at all until needed by the application at which time they are dynamically related based on an Index Expression.

Indexes are built on the Data Tables using some Expression (example: Fld1 + Fld2 or Account_ID, etc.) that works to meet the needs of the application.
These Indexes may be 'activated' when needed for any given Data table which has had an Index created.

Related tables have a Parent / Child relationship where the Child table has activated an Index and the Parent relates to that Child by values from its own fields which match the Child Index Expression.

How you utilize the data after it is moved to a SQL Server depends on what you are using for your application.

If you are still using Foxpro for the application, you can query the SQL Server and get records back into a memory cursor (it works pretty much like a data table) and if you get back multiple SQL Server query cursors, you can then create an Index (using an Expression) and Relate the tables as needed.

If you are changing the application to some other language (such as maybe VB.net or C++) then you will have to create your own 'relations' via the SQL Query syntax itself.

Keep in mind that moving the Data tables is not too big a deal.
But changing the application language is a BIG Deal and even how you approach any individual task will have to be handled differently.
Think of it like translating a book from English to Chinese - EvERYTHING HAS TO CHANGE.

If you are uncertain and/or don't have a copy of Foxpro and the application's Source code, you had better consider finding a Consultant to either do the work for you or, at the very least, assist/advise you.

Also, keep in mind that if the project is BUSINESS CRITICAL, then you shouldn't try to skimp on the conversion costs.

Good Luck

Answer 8

You can use MySQL workbench to reverse engineer the whole database or multiple databases to visualize them for starters, later on, you have to follow the naive method of putting it on paper for better results.