Establishing SQL server tables Relationships without knowledge of DB and tables

Question

Establishing SQL server tables Relationships without knowledge of DB and tables

zocial411

Old Hand

Points: 338
More actions
January 22, 2020 at 12:04 am

#3717208

Hi
I'm Consultant as A DBA and a manager asked me to add or establish tables relationship and he does not know the business use of DB.
however he identified a business user who may answer my questions.
what kind questions may i ask the business user and is this problem solvable?
there are many tables in the databases and most of them don't have primary key and no pk, fk in this database- Non.
thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

Wow. You're in a tough spot. There is a very high likelihood that the business also doesn't know how these tables match. Personally, I'd suggest you bring some tooling into it. Here's a free Entity Relationship Diagram (ERD) tool that could help. The key is going to be a way to communicate with the business person that they'll understand. You can't sit there showing them T-SQL commands, table definitions and the like. You need to show them a picture and get them to start to make connections based on that. There's also a free tool built into SSMS, but be cautious with that. It creates extra tables and functions and stuff inside your database. Do not run it on production!

Strongest possible recommendation, as you work on this for them, create primary keys and foreign keys. It's a relational storage engine for crying out loud. It's how they're supposed to work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

IF there are any stored procedures, the joins in those store procedures can provide some important clues. I suspect that, because of the lack of keys, there probably aren't many of those to go by, though, because they probably thought they'd "do it all in the front end". You could also capture some of the queries in sessions to see what the code might contain for joins.

Although there are no keys, there may be some indexes and, especially the unique ones, they can also form important clues as to what some of the PKs could be.

One of the reasons for the total lack of keys is because someone probably thought that it would be easier to migrate the code if they ever needed to and that all referential integrity could be handled by the front end.

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

frederico_fonseca SSCoach Points: 16003 More actions · Answer 3

and second step - and sometimes the only one as what the "users" see does not necessarily reflect the underlying database - look at code that uses that db

SQL procs

Functions

View

and application code

to see how they go from one table to another - and this can prove quite hard and sometimes nearly impossible unless you implement Extended Events to capture all SQL executed against that db and then analyze it (nightmarish task from personal experience in some cases)

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 4

Honestly, sounds maybe like you just need to ask the business user to explain their business and what they are trying to accomplish, then once you understand that, model how you might understand it, and compare that to existing, see where the gaps are. That might point out efficiencies you can make, or highlight where gaps in your knowledge of the process are when you run across other tables that don't fit your understanding.

Pretend you're a physicist and you're trying to break the Standard Model lol

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

zocial411 Old Hand Points: 338 More actions · Answer 5

Done. thanks

This reply was modified 2 years, 10 months ago by zocial411.

zocial411 Old Hand Points: 338 More actions · Answer 6

Hi,

I'm very great full for your expertise advice. I just uploaded tables.

I'm in tough spot as Grant Mentioned. Client is saying/ thinking I should be able to create the PK/FK by just looking at the tables without prior knowledge. I never did that and I don't want to admit to them!

I appreciate any of your ideas. its challenging when you don't know the all the questions you need to ask to get the solution.

Thanks.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 7

You have a starting point. There are some primary keys. Heck there are two foreign keys that I saw. So, again, this is largely a paper process to start, spread those things out better, get the data types in the display. Then, using the PKs you do have, the names of those PKs, their data types, compare them to the other tables. Find places where there are matches on name and data type. Those are possible, emphasize that term, foreign key constraints. Draw lines, rearrange stuff, reprint as needed until you can ballpark something that covers as much of the database as you can based on the information you have. Take that to the client.

The core problem is, if there haven't been constraints, there could be anything in the data. You can't just apply keys. You have to know that they're right, first. Then, that the data will support it. You can swear to me up and down that this column is unique. If it's never had a unique constraint or primary key and there is duplicate data, it ain't unique.

So you can document, then you can test, then you can report. From there, probably, do data clean up, apply the PK and FK where you can, and then do it all again.

Also, fold in the suggestions from everyone else. They're not wrong.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

zocial411 Old Hand Points: 338 More actions · Answer 8

Hi Grant,

we have a plan here as you stated in your first paragraph from your last reply. 'Draw lines, rearrange stuff, reprint as needed and covers as much of the database as you can based on the information I have'. I will Take that to the client. ask more questions and ask if data can be cleaned. yes there are a lot duplicates in even columns that look like a good candidate for primary keys.

Thanks again and honor to get your advice internet is amazing thing.

zocial411 Old Hand Points: 338 More actions · Answer 9

agree with the colleague's suggestion to setup /implement Extended Events since there are no queries or stored procedures that can give me clues about how tables relate to each other/ join parent/child.

thanks.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 10

Sounds like you're on the path and headed in the right direction. Know that something like this is not simple. Happy if I've helped even a little.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning