Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joedoherty
Starting Member
1 Post |
 Posted - 2012-02-05 : 09:21:47
|
| Hi all,I've recently taken on a large database that has vurtually no relationships defined on the tables, although these do exist - they've just not been implemented at the database level. I plan to document this system by going through the stored procedures, views and functions and looking at what JOINs are used. That way I'm able to build a logical diagram.The next step would then be to implement this physically on the database, with all the issues (I imagine) that will bring.I hoping there's a guru (or saviour) out there that can let me know of one or two tricks to help streamline this process. Otherwise it's a pretty daunting task.God only knows what problems are lurking once this data starts to be 'integrified'!I'd apprecaite any help you guys can provide.Thanks,Joe |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-05 : 10:33:19
|
| as you told you can start with joins to see on what columns the tables are related and put the details across in an excel spreadsheet. Once this is done, you can generate create primary key,foreign key scripts from excel using formulas and copy and paste onto a sql window to create the script. You can make use of excel concatenate() function for generating the scripts------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-05 : 10:50:52
|
| While what Visakh suggests would take away a lot of the suffering involved, I would go about it the long and hard way if I have the time and resources. If I were faced with this, I would:1. Try to understand as much about the physical problem and the business rules as I possibly can. For example, talk to the business consumers and others with a stake to get their perspective.2. Using a database diagram sketch out the relationships that I think should be there.3. Look at the table names and column names, primary keys if any, candidate foreign keys etc. to confirm and/or correct my understanding of the business rules.4. Look at the stored procedures, functions, and views to reconfirm and/correct my understanding.5. Run some queries to verify the consistency of the data. For example, if I am planning to add a foreign key relationship, does the referenced table have all the values from the referencing table?6. Add the constraints, foreign keys etc. one at a time and do lot of testing. For example, if a foreign key constraint is added, can I guarantee that the referenced data will be inserted before the data in the referencing table is inserted?I know, I know, this is not what you are looking for, and may be all you need is what Visakh suggested, but look at the time you spend as an investment as well. Next time when someone asks you a question about the database, or you need to run a query to produce data under pressure, you would be the hero! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-06 : 03:35:17
|
I would put some triggers in place that LOG (to a table) when a violation of a proposed foreign-key occurs (you might be able to log enough information to determine the culprit - certainly which user and date-time, and armed with that and a trace from SQL Profiler you could probably see the actual SQL (and the "Name" of the application which sent the SQL), and ... maybe ... trace that back to the culprit.At the very least, over a period of time, you would discover which FKeys were safe to add, and which parts of the program need to be changed to prevent violations.I'm guessing, if the FKeys are weak, that you don't have a comprehensive fully automated regression test you can run? If you do then it might be enough to create the FKeys on the QA system, run the regression test suite, see what breaks, fix it and then Rinse & Repeat  An insert that currently succeeds, but which will break if an FKey is enforced, and (assuming ...) that INSERT is part of a multi-statement SQL code block that is NOT inside a TRANSACTION, could cause havoc if it fails ... probably best not to also discover that TRANSACTION wrappers are rare at your shop too  |
|
|
|
Swaynebell
Starting Member
8 Posts |
Posted - 2012-02-29 : 10:30:08
|
| Hi,I had to do something similar recently with a legacy database running on an OpenVMS server. Fortunately, I have a data modeling tool (ER Studio Data Architect by Embarcadero) that allows the user to infer Foreign Keys between tables either by column names (i.e. it looks for columns in different tables with the same name) or by indexes. In my case, I was lucky because the parent key/child key columns used the same name so the tool picked them up. So, it caught all of the PK/FK relationships and "drew them in".Note that I'm not advertising a particular software here, I just happen to use the Embarcadero tool. I'm sure that other modelling tools do the same thing. It saved me weeks or months of work. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2012-02-29 : 23:03:19
|
| Another thing that you need to verify is that the candidate foreign keys have exactly the same data types as the columns they reference.If that is not the case, you have a big job to bring them together.CODO ERGO SUM |
|
|
|
|
|
|
|
|
deriving table relationship from sprocs