Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I was discussing with another colleague about what we should be used when an DB entity is referring to another. I don't think there is any good reason to break the practice of putting the Primary Key in the referring entity. However, one of my colleague says: "You should use a surrogate key in the entity, but it is better to put the human-readable natural key in the referring entity. As long it is unique, it is fine and it is easier when you are doing support or maintenance job"

I know it will works, but obviously it is not a good practice you are putting a non-PK unique column as "foreign key", just for gaining a bit of ease in writing SQL during support as we can have less table join.

Though I mentioned the his approach is conceptual incorrect, and causing problem too practically etc, he seems rather trade off correctness in data model in exchange of ease of ad-hoc support jobs. And he said: "I know it is not good practice, but good practice is not golden rule"

Honestly I feel frustrated when dealing with something like this. I know there are always case that we should break some rule or practice, but doubtless it is not such case now.

What will you when you are facing situation like this? Please assume yourself being a senior developer which is expected to contribute in misc development direction and convention.

share|improve this question

closed as off-topic by Ixrec, GlenH7, Scant Roger, gnat, Giorgio Dec 26 '15 at 12:20

  • This question does not appear to be about software development within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

So is this from the perspective of a senior developer suggesting a solution and a more junior not following the proposal. Or just two equal level colleagues disagreeing over best practice? – dreza Jun 19 '12 at 2:21
Your coworker isn't refusing, your coworker values maintainability (and the bugs that good maintainability prevents) over strict relations (and the data rot that tends to prevent). Personally, the second is better but it's not so cut-and-dried. You need to make sure that in this environment your way is really better. If it is, then argue its merit. – Telastyn Jun 19 '12 at 2:33
Paraphrasing George Orwell - "were equal but I am more equal the him". – mattnz Jun 19 '12 at 5:11
I am not sure what the problem is with your colleague's approach. As long as you have a unique natural key, and assuming you keep that indexed for performance reasons, what are the issues? You could even consider using that human-readable key as primary, so both of you are happy – Andrea Jun 19 '12 at 7:33
I'm voting to close this question as off-topic because it's not about programming, it's about interacting with people in the workplace. – Ixrec Dec 21 '15 at 15:25

I can see his side of the argument, and honesty, if more people threw out "good practice" and "Conceptual Correctness" in favor of maintainability, the world would be a better place.

What you need to decide is not who is right, but which approach provides the right balance. Being ideologically correct and hard to maintain and support is worse than being less than perfect and easy to support and maintain.

On that basis, review the approaches and decide is your approach going to provide a lower cost alternative (in the long term) to his - does his design fail if scaled or modified compared to yours. Does his design fall apart under certain circumstances where yours would survive. Argue you case on not what a book says is the best thing to do, but on what the business thinks is the best thing to do. Watch for "Technical debt" - often a designs such as this are really shortcuts that will cause problems sooner or later, but don't discard the idea unless you can identify a valid reason to.

share|improve this answer
best / good practice can be a four letter word, imo. – GlenH7 Jun 19 '12 at 14:32
If "conceptual correctness" would really stand here against "maintainability", I would agree, but to my experience using business data as keys is one of the worst mistakes one can make when trying to design a maintainable database schema. – Doc Brown Jun 19 '12 at 19:09
@DocBrown The "maintainability" he is concerning is only how easy it is to write SQLs during ad-hoc support work. It is not about maintainability of codebase or design etc. That's the reason why I am so against his suggestion. – Adrian Shum Jun 20 '12 at 3:07
@AdrianShum: so what's the primary role of your colleague - is he more a developer or more a "supporter" writing a lot of ad-hoc queries? For ad hoc-queries, a normalized data model is indeed sometimes worse to handle than a data model optimized for specific queries. The classical example for this are OLAP data models with star or snowflake schemas. You should clarify your teams priorities first, so that you follow the same goals. And clarify the difference between "ease of maintainance" and "ease of support", otherwise it gets a battle of words. – Doc Brown Jun 20 '12 at 6:41
@DocBrown What make me frustrated is precisely his role: he is supposed to be a senior developer. However, this company seems putting quite a lot of support job to developers, and because of lots of little flaws in the application, many developers here seems think that it is a norm to do many ad-hoc support queries for every kind of system – Adrian Shum Jun 20 '12 at 8:53

Speaking to the specific example, I believe there is a way you can both 'get your way'.

Suppose we have Cars, which each have a Style. Pseudoschema:

Id       | int
Name     | text

Id       | int
StyleId  | int, FK to Style(Id)

(please for the moment put aside religious debates about what PKs should be named and typed :))

Your colleague wishes to replace StyleId with a StyleName column. You do not.

However, simply by defining a view :

    Car.Id Id
    Style.Name StyleName
    Car INNER JOIN Style ON Car.StyleId = Style.Id

you both win: you get to keep the correct FK column, and he gets to see the data he wants.

share|improve this answer
Good point :D (Though I am expecting a discussion more on the soft skills in dealing with colleague) I will try to use this method to convince him ;) – Adrian Shum Jun 20 '12 at 3:09
+1. This answer demonstrates a very important soft skill: understanding the other person's problem. Your colleagues problem was "I need to be able to create debug queries quickly and easily and primary keys are hard to work with." Your problem was "database schema becomes fragile unless we use primary keys properly". State it like that, and you can see that one angle of attack is to make it easier to create debugging queries, without polluting the schema. – MarkJ Jun 25 '12 at 10:03

So you've got two approaches (his, yours) and two outcomes (success, failure), giving you four possibilities:

  1. Your way, success. You convince your colleague to do things your way, and it works out pretty well. Good: you get your way, the system works the way you like, and you've shown to some small degree that you know how to do things "right". Also, your colleague may eventually come around to see things your way. Bad: your colleague may not come around, and he may remain unhappy especially if he's the one who has to deal more directly with this part of the system. Also, you've learned nothing.

  2. His way, success. You say: "okay, let's try it your way and see what happens." Things turn out pretty well -- the down side that you're worrying about really isn't so bad. Good: system works, your colleague is happy and you learn a new way to do things (and perhaps become less dogmatic). Bad: you don't get to take full credit for the design, and it may always bug you that things don't work "right."

  3. Your way, failure. Maybe not outright failure, but maintaining the database takes more time than it should. Good: could be a good learning experience for you. Bad: your colleague bristles every time the database needs to be modified, and he's much less likely to listen to you the next time you suggest "good practice."

  4. His way, failure. Again, not outright failure, but the scenario you envisioned comes true and your colleague's method causes more problems than it solves (or at least more problems than doing it your way). Good: you get to think (but probably not say) "I told you so", you have the upper hand in future decisions, and your colleague might learn something. Bad: the system doesn't work as well as you know it could, and as the guy who's supposed to know how to do this stuff you may have to take some of the blame.

So, consider the up and down sides of all these and decide whether this particular decision is worth fighting about. How hard will it be to fix things if the first approach you try doesn't work out as well as hoped? To what degree has your colleague dug in his heels at this point? Do you have the political capital to get your way, and is that how you want to spend it?

Of course, the best solution is to talk about the issue with an open mind and find a way to agree. You wrote in part:

I know it will works, but obviously it is not a good practice...Though I mentioned the his approach is conceptual incorrect...he seems rather trade off correctness in data model in exchange of ease of maintenance.

Your position seems to be that your colleague should accept your method because yours is "good practice" and his "obviously" isn't. That kind of thinking isn't going to help you win him over to your position. Instead, try laying out the benefits and drawbacks of both methods. Do your best to stand in his shoes and see things the way he does. Is the kind of correctness that you're talking about really going to make a noticeable difference? Is the increased ease of maintenance going to be noticeable in this situation?

If you have some past experience with similar decisions, it may help to talk about that. Similarly, ask about his experience with similar decisions. Remember that the goal is to build a system that works, not to win an argument, so avoid becoming personally attached to your recommendation.

share|improve this answer
Theoretically you are correct. However in practice, "success" and "failure" aren't always clear cut, especially if you have no quantitative method to measure results precisely. "Easy to maintain" or "bug prone" are pretty subjective terms, moreover these can only be demonstrated - if at all - over a long time period. I can very well envision situations where both opposing parties consider the outcome a success to them and failure to the other. I fully agree that it is best to compare the (perceived) benefits / drawbacks of each approach, rather than declaring the "obvious" solution. – Péter Török Jun 19 '12 at 8:38
I dislike boolean approaches to human issues. – Michael Durrant Jun 19 '12 at 12:39
@PéterTörök I agree that success and failure aren't always clear, and in fact usually aren't. I really just wanted to give the OP a framework for thinking about the problem, and it's easier to compare the possibilities at the extremes. The OP is going to have to consider how much this little db design detail really matters, how much he's willing to fight for his idea, and how much his colleague is attached to the alternate plan. – Caleb Jun 19 '12 at 21:52

In this particular case, you can perhaps convince him if you show him that indeed your suggestion is not only conceptual better, but also more maintainable.

putting a non-PK unique column as "foreign key"

introduces the data of that unique column in a second table, which means creating unnecessary redundancy, which is most times bad practice in relational databases (except for well-defined optimizing purposes). Whenever you have to change the data in that column later, you will probably run into trouble.

just for gaining a bit of ease in writing SQL during support as we can have less table join.

To me, that sounds very much like premature optimization. One of the rules I have learned over two decades of database design is "Avoid business data in keys whenever possible." That saves you lots of hassle in the long term.

Nevertheless, if your colleague is not open to listen to your arguments and still insists that his point of view is better, you probably need a third, independ person to make a final judgement (perhaps your boss?). You should be prepared for that with good, non-personal arguments.

share|improve this answer
In fact the 2 arguments you suggested is exactly what I have raised. Probably he is too get used to work with a not-properly-design data model and put too much attention too those ad-hoc support work. Anyway, Thanks so much for hardening my understand and your support ;) – Adrian Shum Jun 20 '12 at 8:47

I'm going to skip the technical issues of your question here as wiser and more practiced minds will probably serve you better there. However, in terms of practices as a more general issue I see a couple of sides to look at this from.

In the case that the practices are governed in some manner by the organization that you are working for, you're only choice is to have your colleague make a case to your manager to allow a change of practice either as a special case, or as a change as a general rule. If your colleague can make a good case and it is accepted after weighing the options for and against by your management, then the decision is out of your hands and you don't really need to care about how "technically correct" the issue may be.

If on the other hand the practice is more of a commonly accepted practice in industry, then the decisions comes down to who is ultimately responsible for maintenance of the code. If that is you and you had sought advice, then it's up to you to choose whether you will accept the advice or not, and whether you can listen with a wide-open mind (which I'll admit is hard to do when your experience might be screaming "NO" in your head). Your other choice if the decision doesn't ultimately rest with you is to calmly discuss the options with your manager, or optionally have an open discussion with your team as a means to pick the brains of a pool of experts.

The idea ultimately isn't to specifically validate your position or your colleagues, but to find the best solution for the situation at hand, regardless of the technical correctness of the solution you end up with.

A certain amount of 'friction' is normal in any job where things don't seem to sit well with your own knowledge or experience. How frustrated you end up feeling on the other hand is really up to you. You can choose to fight every issue, or to apply calm, impersonal, and collaborative compromise, and avoid letting differing opinions be a source of frustration for you in your workplace.

share|improve this answer

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