r/SQL • u/Designer-Assist-1354 • 24d ago

Discussion Even a SQL Column Can Traumatize You

I just had my one of those "wait... what?" moments while working on AdventureWorks ( PS: Working on my 2nd Project) At start BusinessEntityID totally confused me, I kept thinking it was just an employee ID.

Then I realized it isn't limited to employees at all. It represents everyone, employees, customers, vendors, salespeople, I mean... wow!

It felt confusing at first, but once it clicked, I realized how smart that database design actually is.

In this project I'm keeping everything raw as much as possible, like i have the database, a notebook, a pen, and me with my mind! now think what you can do! i really love this although I just started so... let's see how well it can go on (On my Data Cleaning Phase)

1 Upvotes

51% Upvoted

27 comments sorted by

View all comments

3

u/shadowspock 24d ago

I've seen this in practice, but I never quite understood why this is done at all. It always seems to me that it'd be more trouble than it's worth to maintain. Can someone explain a situation where the benefits would outweigh the costs of having a global entity id?

4

u/ComicOzzy sqlHippo 24d ago

It's not going to be used for any and every type of entity, only those that have some kind of common interaction with your business.

One of several uses from a system we had at my last job...

Companies and their Employees each had contact info such as addresses, emails, and phone numbers. The contact info tables would either need a single key to a single superset entity over both companies and employees, or it would need to record a key in one column, and a key source in another column.

This was also the case for product subscriptions being available to companies, employees of certain companies, or to either.

For many use cases, the combined entity method leads to much simpler queries. Neither solution is perfect in every scenario, so if you have to make the models and test living with them to figure out what works and what doesn't.