3 ms·

Database IDs should be considered an implementation detail. It's better to assign a randomly-generated identifier for public-facing APIs for exactly this reason

Database IDs should be considered an implementation detail. It's better to assign a randomly-generated identifier for public-facing APIs for exactly this reason.

echelon 7y ago
Exposing auto incrementing IDs also lets your partners and competitors both see how big your tables are and make estimates at your growth rate. Always assign random identifiers.
- eb0la 7y ago
  Some accounting firms actually take this into account when you're sending bills. In my country bills must have an auto incremented bill number. You cannot issue bill #100 without having 99 bills before. I know one auditing firm that used those ids to spot fraud: some managers started their own firm and started giving contracts to that firm. The auditors spotted something was going on because the bills have a very low number, and they incremented by 1 or 2 every month - a sign I am your only customer.
  - jarofgreen 7y ago
    Which country is that?
    - nothrabannosir 7y ago
      It’s definitely true in Holland. The only resolution you’re allowed to shard by is yearly, so 2019-1, 2019-2,.. , 2020-1. Or just all out increment forever. I assume it makes it harder to hide invoices during an audit.
      - pawelk 7y ago
        How do companies with hundreds of sales points deal with that? Is a centralized invoicing system required by law? I find it hard to believe that multiple numbering sequences are not allowed.
        akvadrako 7y ago
        I'm pretty sure the grandparent is wrong. You are allowed to have multiple sequences, for example one per store. Maybe even one per client.
        nothrabannosir 7y ago
        > een opeenvolgend nummer - https://www.belastingdienst.nl/wps/wcm/connect/bldcontentnl/belastingdienst/zakelijk/btw/administratie_bijhouden/facturen_maken/factuureisen/ https://www.belastingdienst.nl/wps/wcm/connect/bldcontentnl/... Translation: "a sequential number." Exceptions are explicitly and exhaustively listed here: https://www.belastingdienst.nl/wps/wcm/connect/bldcontentnl/belastingdienst/zakelijk/btw/administratie_bijhouden/facturen_maken/factuureisen/aangepaste_regels_facturen/aangepaste_regels_voor_facturen https://www.belastingdienst.nl/wps/wcm/connect/bldcontentnl/... , e.g. the requirements for <€100 are relaxed and don't include the number. Note also that this is not for B2C receipts; this is for B2B invoices. I'll admit the Dutch IRS site is lacking in info. I remember this information from reading the IRS handbook (ugh) back when I was a sole trader there, and extensive conversations with my accountant on the matter. However, I would bet good money that a separate invoice category per client is not OK. Very interested to be proven wrong.
    - wodenokoto 7y ago
      Denmark as well. It is so audit can see that there is an item missing between item #54 and #56. It's not bullet proof, but it does help auditors.
  - pawelk 7y ago
    That's interesting. In Poland invoices also have to contain an auto-incrementing component (so you can't backdate something and slip it in, or post an invoice to a client but skip it when reporting taxes). However each company can design multiple "series" of invoices, for example per-client (e.g. 1/2019/MSFT, 1/2019/AAPL) or per type of services (1/2019/IT, 1/2019/Design) or office location (1/2019/CityA, 1/2019/CityB) or basically whatever. The year is optional, numbers could increment indefinitely, but it's convenient to reset the counter each year (or each month for large companies).
    - tecleandor 7y ago
      Same for Spain.
- travisjungroth 7y ago
  Or you just hit your auto-incrementer at an exponential rate. #growthhacking
- drewcoo 7y ago
  The way I heard it the "company" was Germany during WWII, the "tables" were tanks, and the allies could estimate the number they had produced because of incrementing ids.
  - toolslive 7y ago
    Yes, but amazingly, Hitler had countered this strategy when founding the NSDAP to make the party appear to be larger than it was. The membership numbering started from 501 iso 1. He was number 555.
    - eb0la 7y ago
      Remember web counters for visits? My company started them always at 1000 :-D
- niceworkbuddy 7y ago
  You are not necessarily right. It's because of limited knowledge of structure and architecture. Eg. developer giving someone `create table` statement (without table data). In MySQL engine this statement by default contains last auto incremented id. So someone executing that statement and then inserting record could end with id equals to 12345 even though it's very first record.
- stickfigure 7y ago
  I just use a single sequence for the whole database. It's much less information leakage and you never have to worry about confusing thing 5 with otherthing 5 in a query.

Database IDs should be considered an implementation detail. It's better to assign a randomly-generated identifier for public-facing APIs for exactly this reason.

echelon 7y ago

Exposing auto incrementing IDs also lets your partners and competitors both see how big your tables are and make estimates at your growth rate. Always assign random identifiers.

eb0la 7y ago
Some accounting firms actually take this into account when you're sending bills. In my country bills must have an auto incremented bill number. You cannot issue bill #100 without having 99 bills before. I know one auditing firm that used those ids to spot fraud: some managers started their own firm and started giving contracts to that firm. The auditors spotted something was going on because the bills have a very low number, and they incremented by 1 or 2 every month - a sign I am your only customer.
- jarofgreen 7y ago
  Which country is that?
  - nothrabannosir 7y ago
    It’s definitely true in Holland. The only resolution you’re allowed to shard by is yearly, so 2019-1, 2019-2,.. , 2020-1. Or just all out increment forever. I assume it makes it harder to hide invoices during an audit.
    - pawelk 7y ago
      How do companies with hundreds of sales points deal with that? Is a centralized invoicing system required by law? I find it hard to believe that multiple numbering sequences are not allowed.
      - akvadrako 7y ago
        I'm pretty sure the grandparent is wrong. You are allowed to have multiple sequences, for example one per store. Maybe even one per client.
      - nothrabannosir 7y ago
        > een opeenvolgend nummer - https://www.belastingdienst.nl/wps/wcm/connect/bldcontentnl/belastingdienst/zakelijk/btw/administratie_bijhouden/facturen_maken/factuureisen/ https://www.belastingdienst.nl/wps/wcm/connect/bldcontentnl/... Translation: "a sequential number." Exceptions are explicitly and exhaustively listed here: https://www.belastingdienst.nl/wps/wcm/connect/bldcontentnl/belastingdienst/zakelijk/btw/administratie_bijhouden/facturen_maken/factuureisen/aangepaste_regels_facturen/aangepaste_regels_voor_facturen https://www.belastingdienst.nl/wps/wcm/connect/bldcontentnl/... , e.g. the requirements for <€100 are relaxed and don't include the number. Note also that this is not for B2C receipts; this is for B2B invoices. I'll admit the Dutch IRS site is lacking in info. I remember this information from reading the IRS handbook (ugh) back when I was a sole trader there, and extensive conversations with my accountant on the matter. However, I would bet good money that a separate invoice category per client is not OK. Very interested to be proven wrong.
  - wodenokoto 7y ago
    Denmark as well. It is so audit can see that there is an item missing between item #54 and #56. It's not bullet proof, but it does help auditors.
- pawelk 7y ago
  That's interesting. In Poland invoices also have to contain an auto-incrementing component (so you can't backdate something and slip it in, or post an invoice to a client but skip it when reporting taxes). However each company can design multiple "series" of invoices, for example per-client (e.g. 1/2019/MSFT, 1/2019/AAPL) or per type of services (1/2019/IT, 1/2019/Design) or office location (1/2019/CityA, 1/2019/CityB) or basically whatever. The year is optional, numbers could increment indefinitely, but it's convenient to reset the counter each year (or each month for large companies).
  - tecleandor 7y ago
    Same for Spain.
travisjungroth 7y ago
Or you just hit your auto-incrementer at an exponential rate. #growthhacking
drewcoo 7y ago
The way I heard it the "company" was Germany during WWII, the "tables" were tanks, and the allies could estimate the number they had produced because of incrementing ids.
- toolslive 7y ago
  Yes, but amazingly, Hitler had countered this strategy when founding the NSDAP to make the party appear to be larger than it was. The membership numbering started from 501 iso 1. He was number 555.
  - eb0la 7y ago
    Remember web counters for visits? My company started them always at 1000 :-D
niceworkbuddy 7y ago
You are not necessarily right. It's because of limited knowledge of structure and architecture. Eg. developer giving someone `create table` statement (without table data). In MySQL engine this statement by default contains last auto incremented id. So someone executing that statement and then inserting record could end with id equals to 12345 even though it's very first record.
stickfigure 7y ago
I just use a single sequence for the whole database. It's much less information leakage and you never have to worry about confusing thing 5 with otherthing 5 in a query.