3

I am in the process of designing a simple portfolio management system for financial advisors. The central table here is an 'tb_account' table that holds the various accounts an advisor manages. I create a random unique account number and store it in the 'accountno' field in the table tb_account. The field is also the primary key for the table. Now there are other tables where more information about the account (a.k.a. portfolio) is stored. All these tables are driven by 'accountno'.

The question I have is, should I store 'accountno' only in tb_account table and create a unique index on it and use the surrogate primary key from the table, an 'id', in all the other tables?

I have images of both the designs here. Please let me know which is the route I can go? The pros and cons of both will also help. I am leaning toward the latter, as I can completely hide the 'accountno' field from all the tables, except the main tb_account table. But, will such a design be a hindrance if and when I choose to go the 'micro-services' route wherein joins across tables are discouraged or may even be impossible.

The two designs, pictorially.

Improve this question
4
  • 2
    Where did you see that joins across tables are discouraged? That's literally the whole point of a relational database. Commented Oct 15, 2019 at 14:02
  • @ChrisMurray "when I choose to go the 'micro-services' route" ... I think the implication here is that these tables will be behind different microservices, hence, they should not or cannot be joined, because they'll actually managed by distinct applications. Commented Oct 15, 2019 at 14:56
  • That's right @svidgen. Assuming I go with the 2nd design, I may have to make a call to the 'account' service using the 'accountno' value, fetch the pk which is the 'id' and use this to fetch/update values in other services such as position, transaction, etc. Commented Oct 16, 2019 at 5:25
  • Is the account number a sensitive piece of information? If so you may want to minimize the number of places you store it and use the surrogate key instead. Back in the day, HR systems used to use social security number as the primary key... they were into a bit of a mess when the regulatory environment changed and they had to start encrypting it. Commented Oct 16, 2019 at 5:45

2 Answers 2

Reset to default
8

Use the second approach. The problem with using information like account numbers as your primary key is that they can in theory change (yes, even if the client says right now they won't ever change). Stick with the id field that uniquely identifies the database row.

Improve this answer
8
  • Yes, @GrandmasterB, I intend to use the second approach. Commented Oct 16, 2019 at 5:28
  • 2
    My rule of thumb is that a PK should never be seen by a user, so they can never ask for it to be changed. Commented Oct 16, 2019 at 7:27
  • Sorry, after fully waking up, I find this answer to be misleading. In many business settings, if you give a customer an account number, they should not own it. It's an auditing key. Whatever number the customer has on their N-year-old invoice should be in your database. And, it should be guaranteed to never change. Commented Oct 16, 2019 at 14:07
  • @svidgen What do you mean 'misleading'? Are you implying I'm being deceptive? Back here on Earth account numbers change. I've had many bank account numbers change after banks merged. What 'should' be is irrelevant, it happens. They don't make good PK's. Commented Oct 16, 2019 at 16:41
  • @GrandmasterB Nooo ... no no no. Sorry. Not deceptive. It addresses a technical problem and makes implications about the business reasons behind the technical judgement. But, I think you just "missed" some business concerns. ... The systems I've worked in treat account numbers, invoice numbers, etc. very very carefully. In cases where the systems do permit changes, very very strict logging/logging is required. ... And mechanisms need to be built into the customer and/or business-facing interfaces to ensure fidelity between the digital records and the paper records. Commented Oct 16, 2019 at 17:03
2

A good PK has three properties, at a minimum:

  • It's unique. (Sufficient "space" to generate new ID's.)
  • It's small. (Unlike a varchar for example.)
  • It's stable and independent. (It doesn't encode or derive from information about the record.)

There could be other requirements, depending on circumstances. For example, some database engines also benefit significantly from using ascending PK's, due to the fact that they order records in disk by PK (at least by default). Appending a record to the end of a table is a good deal faster than inserting at unpredictable locations.

But there are also business considerations here.

  • Yes: Anything a customer or stakeholder sees on the screen they'll believe is theirs.
  • But also: If account numbers are mutable, your auditing requirements can skyrocket.

These two things are potentially at odds with each other. (Though, probably not as often as you think.) And, you need to sort out which is more important to the business — how important auditability is and how you're going to perform an audit if an account numbers are mutable.

When a customer or fraudster calls with an account number, it should always refer to something in your database. When a customer calls with questions about a document (statement, invoice, etc.), the account number on that doc should always match an account in your database.

When there are rogue, unpaid orders on an account that has changed numbers, you should be able to tell the customer which account number was used to place the order. Etc.

And of course, when an actual auditor is digging through your records, they will not be happy if the paper records don't match the digital ones!

Now, you can certainly have separate PK's and account numbers — both unique fields. Maybe one is an ugly UUID and the other is a nice, short char. But, do not separate these fields under the assumption that it's a good idea to ever change either one. They should both be immutable. (The customer-facing account number even more-so than the PK, IMO...)

Improve this answer
1
  • Very valid comments, @svidgen. I guess I will go with the 2nd approach. The 'accountno' stays in just one table and is not visible in other areas of the database. The join or the multiple API calls may be a bit of an overhead, but I guess I can live with that. Commented Oct 16, 2019 at 5:35

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.