asked
Migrating to SS - DB design names foreign keys different on each table. Opinions?
ME: I don’t see any of the Foreign Keys column names populated in the tables where they will be used. Will this issue be corrected when the ERD is created by SQL Server, i.e., cvtmID on Population table instead of popCvtMasterID, etc., etc.? All the foreign keys need to be fixed in this way for “automatic” joins. Consultant's Comments: 'we' discovered long ago that the risk of injecting database coding errors (ie stored procedures etc.) is increased when using the same field names across multiple tables. So, we have adopted a naming convention that eliminates the potential for error. We understand that it might break the ability to do “Automatic” joins but feel it is the proper trade off. We would prefer to leave the current naming convention.
My opinion is that all of this is an opinion. I hate to say it depends, but it's really a philosophical choice. If you work with EF, it will (by default) take the "Id" column and make that the PK. If you want some other nice name, like CustomerID, you need to define that. Guess what developers like? So they will have: Customer Table ---------------------- Id (PK) Name Order table -------------------- Id (PK) CustomerID -- Fk to
Customer.Id Qty SalesAssignments ------------------------- Id (PK) SalesPersonID (FK to
Salesperson.Id) CustomerID (FK to
Customer.Id) ... I've worked in a few systems like this, and they slightly drove me crazy. There are the same coding issues here, when I join multiple tables, I'll have multiple "Id" columns. It's no different than if I had CustomerID in two places (parent and child). However, here, I could make a bunch of errors and alias the wrong "Id" column to the wrong place. This is a poor practice, but it's just an opinion that this is poor. There are plenty of people that think this makes perfect sense. Coding errors can occur either way, so that's a silly argument.
18 People are following this question.
