4 ms·

> when in every schema I have ever designed or seen, all rows get unique integer IDs anyway. This is artifact of 4 things: - Indexed Integer search are fast i

by mamcx 5y ago

> when in every schema I have ever designed or seen, all rows get unique integer IDs anyway. This is artifact of 4 things: - Indexed Integer search are fast in RDBMS - Having an non-bussines related value for identify a row could save you from making triggers for updated in other relations if it change. This is the part a lot of folks missed: If you already have a natural PK and it not change, is wasteful add ANOTHER (is another index btw) - Is enforced by ORMS that are badly designed. - Most people have not idea how model a database, and this one little thing is one of the easier "fix" you can think of! I have done a lot "non integer primary keys" before (and not GUIds!) and is useful that RDBMS are not like mongo with a fixed schema (yes! Mongo is a single fixed-schema for all your data!). For reporting, store aggregates, pre-compute values, etc is very valuable! For example, in accounting you do reports per day/mont/year. You then do granularity per day, and the another per month, per year, per semestre, etc. When the data volume is high, making tables "days, months, years" make a lot of sense. Cool guys call this a "time-series database". But the rdbms CAN model it! And can model - "document database" - "key-value database" - "olap database" - "columnar database" etc. (you go for specialized backend for performance but honestly? Most of the time is a mistake if them are not relational. "Relational" don't means "is backed by b-trees, is row oriented and use SQL")

whatshisface 5y ago
A time series database isn't keyed by day/month/year, it's typically keyed by monotonic timestamps.
- mamcx 5y ago
  Yes, that is even a more narrowing detail. (people that use a "time-series" rarely need that high of granularity!) But the question was about wondering "why exist the support for PK that are not integers/guis, like dates"?. To be able to model a lot of things for what in the past (before RDBMS) and today (for upstarts engines) you will be tempted to add another data store (that rarely is that necessary). I know by a fact that some times is because people not realize you DON'T need to constrain your db to things like "only integers pks!"! P.D: I work in the enterprise sector. Is fun when something is composed of a cluster of disparate things purely by misunderstanding you could have solved it with a simple db, well done. Contrary to belief, a lot of my customers, their largest databases are 1-10GB each. A rdbms can happily go much much larger than that, if you give them a little love...
jrumbut 5y ago
It's wonderful what you can gain in performance, correctness, and storage efficiency from finding the real primary key from the data model itself. Maybe not worth the intensive design work for many tables, and it can be a risk to get it wrong, but it's cool when it does work.
- brazzy 5y ago
  Yeah, I'm currently facing exactly that dilemma. I have to model a database of machine parts. There is a natural PK in the form of the supplier's parts number, but the legacy system (which uses that as PK) notes that it is specifically the parts number as used in the supplier's ERP system, but suppliers often use differently formatted parts numers in publications such as catalogs (adding or removing spaces, slashes or dashes). So the legacy system also has the "published part number" as one or more separate fields. This inconsistency makes me lean very strongly towards using a synthetic PK instead.
  - bob1029 5y ago
    We deal with a lot of crap where a certain business entity has several different biz keys that can reasonably be used to uniquely identify the thing. I would concur with adding a synthetic primary key, and maintaining all of the relevant business/domain keys on the same type. The advantage of this approach is that you can still keep 100% correct relations between your internal types, even if the domain keying scheme is fucked up or otherwise broken per your original assumptions. It's really easy to add a new column to refine the additional domain keys. Fixing a bad PK and all the things that talk to it is much harder.
  - CRConrad 5y ago
    > There is a natural PK in the form of the supplier's parts number, but ... Or even better: Then the supplier revamps their parts numbering scheme, and every product gets a new number.

by mamcx 5y ago

whatshisface 5y ago

A time series database isn't keyed by day/month/year, it's typically keyed by monotonic timestamps.

mamcx 5y ago
Yes, that is even a more narrowing detail. (people that use a "time-series" rarely need that high of granularity!) But the question was about wondering "why exist the support for PK that are not integers/guis, like dates"?. To be able to model a lot of things for what in the past (before RDBMS) and today (for upstarts engines) you will be tempted to add another data store (that rarely is that necessary). I know by a fact that some times is because people not realize you DON'T need to constrain your db to things like "only integers pks!"! P.D: I work in the enterprise sector. Is fun when something is composed of a cluster of disparate things purely by misunderstanding you could have solved it with a simple db, well done. Contrary to belief, a lot of my customers, their largest databases are 1-10GB each. A rdbms can happily go much much larger than that, if you give them a little love...

jrumbut 5y ago

It's wonderful what you can gain in performance, correctness, and storage efficiency from finding the real primary key from the data model itself. Maybe not worth the intensive design work for many tables, and it can be a risk to get it wrong, but it's cool when it does work.

brazzy 5y ago
Yeah, I'm currently facing exactly that dilemma. I have to model a database of machine parts. There is a natural PK in the form of the supplier's parts number, but the legacy system (which uses that as PK) notes that it is specifically the parts number as used in the supplier's ERP system, but suppliers often use differently formatted parts numers in publications such as catalogs (adding or removing spaces, slashes or dashes). So the legacy system also has the "published part number" as one or more separate fields. This inconsistency makes me lean very strongly towards using a synthetic PK instead.
- bob1029 5y ago
  We deal with a lot of crap where a certain business entity has several different biz keys that can reasonably be used to uniquely identify the thing. I would concur with adding a synthetic primary key, and maintaining all of the relevant business/domain keys on the same type. The advantage of this approach is that you can still keep 100% correct relations between your internal types, even if the domain keying scheme is fucked up or otherwise broken per your original assumptions. It's really easy to add a new column to refine the additional domain keys. Fixing a bad PK and all the things that talk to it is much harder.
- CRConrad 5y ago
  > There is a natural PK in the form of the supplier's parts number, but ... Or even better: Then the supplier revamps their parts numbering scheme, and every product gets a new number.