Encouraging the use of UUID as the primary key

[sergeyprokhorenko]

Change Proposal

Source (Select one.)

• [ ] IETF Published Draft
• [x] Work in Progress Draft

Change Reason (Select all that apply.)

• [ ] Typos and grammatical issues
• [ ] Bad Reference
• [ ] IETF Verbiage modification (MAY, MUST, SHOULD, SHOULD NOT, etc)
• [x] New Text for additional context
• [ ] Underlying XML Format Update
• [ ] ASCII diagram updates (artwork, code samples, etc.)

Draft Number, Full Section, Name

Section 6.9. DBMS and Database Considerations

Current Text:

DBMS vendors are encouraged to provide functionality to generate and store UUID formats defined by this specification for use as identifiers or left parts of identifiers such as, but not limited to, primary keys, surrogate keys for temporal databases, foreign keys included in polymorphic relationships, and keys for key-value pairs in JSON columns and key-value databases.

Proposed Text:

DBMS vendors are encouraged to provide functionality to generate and store UUID formats defined by this specification for use as identifiers or left parts of identifiers such as, but not limited to, primary keys, surrogate keys for temporal databases, foreign keys included in polymorphic relationships, and keys for key-value pairs in JSON columns and key-value databases.

All UUID columns, i.e. primary key columns containing the UUIDs, SHOULD be highlighted differently from the period start and period end columns and other primary key columns.

The database tables SHOULD be marked depending on the role and number of UUID columns:

• Tables of Entity type (other names: Anchor or Knot, Hub or Reference table, Dimension) contain one UUID column as full primary key but not as foreign key
• Tables of Attribute type (another name: Satellite) contain one UUID column as full or part of primary key and as foreign key
• Tables of Relationship type (other names: Tie, Link, Fact, Knotted attribute) contain several UUID columns as parts of primary key and as foreign keys in separate columns or in a column of compound type such as array, hstore or jsonb

Any other columns MAY be acceptable depending on the database modeling method such as Anchor modeling, Data vault modeling or Star schema.

The database tables MAY be marked unsafe if they contain no UUID column as primary key or as part of primary key. It means that database integrity and/or flexibility are not protected by proper keys.

[kyzer-davis]

The database tables SHOULD be marked unsafe if they do not contain UUID as primary key or as part of primary key.

I am not sure this is up to the spec to decide.

[sergeyprokhorenko]

You probably haven't seen chaos in databases with compound business keys. Often, incomplete compound keys are used for relationships between tables, hoping for luck. Such databases are definitely unsafe. UUIDs are designed to displace compound business keys. This is exactly the subject of this specification

[ben221199]

I don't see the need for it. By convention, everbody already uses id as column name and makes it the primary key. For id, somebody can decide to use UUIDs if they want, because it is in the name: UUID.

[broofa]

UUIDs are designed to displace compound business keys

UUIDs are designed to provide IDs that satisfy certain key criteria. The extent to which such criteria make them preferable over existing solutions is going to be application dependent, and highly subjective (and also changeable over time.)

We should limit opining on best practices and conventions to those issues that affect how well UUIDs meet the stated goals of the spec (uniqueness, monotonicity, db locality). Attempting to reach beyond that just compromises how seriously this spec will be taken.

[sergeyprokhorenko]

UUIDs are designed to displace compound business keys

UUIDs are designed to provide IDs that satisfy certain key criteria. To the extent such criteria make them preferable to existing solutions is going to be application dependent, and highly subjective (and also changeable over time.)

We should limit opining on best practices and conventions to those issues that affect how well UUIDs meet the stated goals of the spec (uniqueness, monotonicity, db locality). Attempting to reach beyond that just compromises how seriously this spec will be taken.

You arbitrarily put forward the list of goals and demand that other people be limited by them. It is necessary to rely not on non-existent best practices, but on the analysis of real problems that UUID solves.

[broofa]

Also, what does it mean to mark a database table as "unsafe"? Google doesn't have much to say on the subject, other than MSSQL's TRUSTWORTHY database attribute.

[sergeyprokhorenko]

Also, what does it mean to mark a database table as "unsafe"? Google doesn't have much to say on the subject, other than MSSQL's TRUSTWORTHY database attribute.

Unsafe mark of table means that database integrity and flexibility are not protected by proper keys. It is similar to package unsafe in Golang that violate type safety. On the contrary the MSSQL's TRUSTWORTHY database attribute is about security, not safety.

[sergeyprokhorenko]

The database tables SHOULD be marked unsafe if they do not contain UUID as primary key or as part of primary key.

I am not sure this is up to the spec to decide.

I changed SHOULD for MAY