2

I have 3 years of SQL Server development work experience. Now I am moving to a new company (first jump). I heard in my new company they deal with too many tables.

What is the best way to understand table design in a database? How do you SQL gurus analyse the table design when you move to a new company?

Is ER diagram the best way? Please share your experience on this.

closed as primarily opinion-based by Brent Ozar, Marco, mustaccio, McNets, Evan Carroll Jun 2 '17 at 1:23

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Does the new company have any documentation regarding to the database design (ERD's, UML etc.) or are you coming in and supposed to document it? If no documentation exists I'd consider looking to see if you can generate any database diagrams in SSMS to see if the FK constraints give you some starting point. I've been here before where you come in blind and there are a few experts but no documentation and it's tough because you will find orphaned tables in the database etc. so you can't assume everything has a purpose. – Jeff A May 31 '17 at 15:22
  • @JeffA Looks like they don't have ERD's, UML etc. But FK's are there so I think ER diagram is the way to go.. – Marc May 31 '17 at 15:54
  • 1
    It can also be helpful to investigate who designed it; vendor, consultant, onsite developer, 'Receptionist that was really good at Excel'. I've seen the latter and at that point learning the design is irrelevant. It is more about figuring out how to find the data you need today and refactoring when you can. – Wes H May 31 '17 at 16:21
  • 1
    It's completely normal to arrive at a new engagement with a daabase that is a mess with no documentation. You need to ensure you set expectations with your new employer that you may not be able to deliver new functionality immediately, that there will be some ramp up time to learn the database. Personally, an ERD is a very important part, but you might also find there is a lot of data integrations going on, so also understanding data flow around systems is important – Nick.McDermaid Jun 1 '17 at 4:57
3

The database is an encoding of the business rules. Without understanding the rules there is no way of making sense of the database. Take it piece-by-piece, focusing on one area until you have a reasonable understanding of it.

Even if documentation exists I like to draw ER diagrams from scratch as it helps me remember the connections. It also allows me to compare how I would have done it to how it is currently implemented. The differences are an opportunity for further learning.

Reading the code will bring out further "virtual" FKs and alternate keys that are not declared explicitly.

  • Excellent point - autogenerating ERD's from FK's usually only tells half the story. – Nick.McDermaid Jun 1 '17 at 4:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.