Rule 1: Dr. House's rule of users: Everybody lies.
Rule 2: Constants aren't.
Rule 3: Even laws of nature aren't guaranteed to stay the same
Rule 4: EAV sucks, but sometimes it is the only solution
Rule 5: Rule 4 isn't acceptable

So, bottom line? Go meta. Instead of modelling say, an invoice or an employee look deeper. What is an employee? What assumptions are built into that word?

For example, employee assumes they are employed, and the unspoken assumption is by the user's company. Of course that leaves out consultants. And customer employees that may collocate on your site. That still need to use your system and be tracked...

And what about amount fields? US-centric companies generally only deal with dollars. But what happens if you have to deal with specific denominations? Coins vs bills vs gift cards vs counted cards vs postage stamps? Stamps are particularly egregious because their value varies from year to year so you have to store the per-stamp amount as well as the total value of all stamps for each transaction.

And there's an even more basic assumption with money. US currency has fixed ratios to other denominations, especially the penny.

However, this fixed relationship goes away when you add Euros and Yen and the hundreds of other currencies used in the world. Dealing with currency valuations between currencies is an entire industry in itself!

Then, of course, there's the problem of complexity and the headaches that brings. As in you don't want any.

The front-end developer's answer is "oh, the app can handle it. Don't worry about building the rules into the database, we don't need no stinkin' ACID compliance."

The DBA promptly explodes. 🙂

I once built a database using the assumption that 255 denominations would be plenty for a US-only database. After all, our company would never do business outside the US so surely 255 denominations was plenty? After all, there were only 16 denominations and that's counting stamps. (Oh, stamps...sigh).

Yeah, no. Somebody deep in the heart of one of our customer sales departments said "Let's get ATMs to dispense gift cards! We'll let the machine dispense a bunch of different company's gift cards in various fixed denominations!" And then somebody else said "Sure, but let's make sure we offer programmable amounts too!"

You can see where this is going...

Easy fix, right? Just change the denomination key to 2 bytes. In every single table that uses denominations. While in flight.

Not fun.

That lesson was painful. I had been assured by everyone years ago that we'd never have more than maybe 20 denominations. Hah!

Dr. House was right, right about everything. :laugh: