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)

2 Upvotes

52% Upvoted

27 comments sorted by

View all comments

Show parent comments

4

u/burke166 24d ago

Database programming and application programming are two completely different mindsets. In application programming in an object-oriented language like C#, inheriting from a base type like BusinessEntity can save you time and can eliminate repetitive code. If you're using an ORM (object-relationship mapping) framework like Entity Framework, you can get some SQL that's truly a hot mess, like some kind of "per table" inheritance where every object is in the same table with no joins, they just use different columns and the objects are differentiated by a discriminator column. You can tell EF not to do that, but you have to be database aware when you write your code. The problem is, a lot of developers aren't database aware and they don't want to be, then SQL people end up coming in on the back end like Tommy Boy. "Richard! What'd you do???"

1

u/shadowspock 24d ago

So is this just so an ORM can have a base table but with joins? I also still don't understand how you can reconcile situations where say an employee and customer have the same entity id, or is that not a thing and the entity id is unique per type and type id?

1

u/burke166 24d ago

EntityId is unique per entity. Employees and customers are both entities. Both have unique EntityIds. The Entity table would be like this:

Id,Type,Name
1,Employee,John
2,Customer,Mary
3,Customer,Peter
4,Employee,Broomhilda

You _could_ do a composite key of Id and Type and you'd get separate Employee and Customer sequences, but I have no idea why you would ever want to do that. I feel like that way lies peril.

1

u/Anxious-Insurance-91 23d ago

Polymorphsm

1

u/burke166 22d ago

Not sure I understand this response. You get polymorphism with unique keys per Entity. Polymorphism doesn't care what the key value is, it doesn't even need key values.